How to source a mysqldump file with syntax statements

MySQL provides an external program called mysqldump that writes a file full of MySQL syntax statements that will recreate a database’s tables and their contents. Back in the Apple II days this kind of file was called an exec file. Windows calls them batch files and Linux calls them shell scripts, of which one common format is the bash file. You can create the same thing with HeidiSQL by right-clicking on the database you want to save or duplicate and selecting Export database as SQL.

The Open-Meta.org project has reached a point where it has become painful to start over and rebuild the database from scratch after it has been burned down to get rid of errors. So, why not, I sez, use a file like this to rebuild the entire project’s database from a strategically-saved copy?

The issue, it turns out, is that there isn’t a statement in MySQL syntax for reading a file like this back in. There’s a source command for the mysql command-line tool that will do it. In HeidiSQL you can select the database name followed by File:Run SQL file... (making sure to change the Encoding dropdown – under the filename – to UTF-8).

But neither of these options really work for an automatic database rebuild. Eventually it came to me, however, that I could read the file into R then send it line-by-line to MySQL through the typical command process. In the end it wasn’t quite that easy, but here’s some R code that works for this.

filename <- "dumpfile.sql"
if(file.exists(filename)) {
   sql <- stri_read_lines(filename)
   cmd <- ""
   for(line in sql) {
      if(line!="" && str_sub(line,1,3)!="-- ") {
         cmd <- paste0(cmd, line)
         if(str_sub(cmd,-1,-1)==";") {
            r <- dbExecute(dbLink, cmd)
            cmd <- ""
         }
      }
   }
   return("Initialization of database is complete...")
}

The slight difficulties come because some lines in the dump file are blank, some are comments, and some commands are written as multiple lines that have to be combined into one line for dbExecute. The code above simply reads the file into a character vector called sql with one line of the file in each cell of the vector.

It then loops through the vector, skipping lines that are blank or comments and combining multi-line commands into one line with paste0. The dbLink variable is a connection,  created with the pool package, to our MySQL instance. We determine when we’ve reached the end of a command by checking for a “;” in the last position of the line, which MySQL expects at the end of every command.

I spent an inordinate amount of time doing Google searches looking for how to source a file with MySQL statement syntax instead of with a mysql command-line command. If there’s another solution like this out there I never found it.