Restore Your MySQL Database from a Backup Using PowerShell

Monday Jun 4th 2012 by Rob Gravelle

Rob Gravelle explores how to restore a MySQL database from backup using PowerShell commands.

In this follow-up to the Automate Database Backups with MySqlDump and PowerShell article, we'll be exploring the other side of the equation and learn how to restore a MySQL database from backup in the event of a rollback or disaster recovery. 

The Download Checklist

Before we go and restore any databases, let's take a moment and take inventory of what we should be restoring from.

Assuming that we did not perform any additional compression on the backup files using a utility like Winzip or Winrar we will have one or more .sql files.  Typically, each of these may represent anything from one table or view to all your databases.  It really depends on how you organize your backups. The backup article discussed the free and excellent mysqldump utility that comes packaged with MySQL.  While that's one way to create a .sql file, there are many other ways to accomplish the same thing, for instance, using the MySQL Workbench.  In any case, the .sql format is universal among relational databases and should contain the SQL required to rebuild everything from databases, tables, views, indexes to triggers, functions, and stored procedures, as well as statements to populate all of the data.

One point that I did not stress in the previous article is that when you backup a database, it does NOT include the user accounts. They are stored in the user table of the mysql database. That's just one of the reasons that it's a good idea to backup the mysql database along with your own.

Extracting .sql files from a compressed archive requires a more flexible approach because it depends largely on the tool used to perform the compression.

The Rebuild Process

There are many reasons for wanting to restore a database besides the obvious one of disaster recovery. For instance, you may want to temporarily revert some data tables to a previous localized state in order to perform time-specific QA tests off of them.

At its most basic, restoring a database requires you to log into MySQL using an admin account and specify the .sql file using the DOS Redirect input (<) character:

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

For instance:

mysql -u root –adm#1$$$ sonar < sonar.sql

With regards to using this command in an automated PowerShell script, be aware that it has to be translated for PowerShell because the '<' operator is reserved for future use.  Therefore, the entire command has to be enclosed in quotes and executed within a new &cmd shell. That runs the command as though you typed it directly at the DOS command prompt:

&cmd /c "mysql -u root -p[root_password] [database_name] < dumpfile.sql"

The /c flag terminates the shell process once the command has been executed.

That works great for one file, but, in some cases there may be hundreds of .sql files, depending on your exact backup process. It would be nice if there were a way to restore the entire database with one command.  Well, that's what PowerShell is all about!  Behold, the following line borrowed from UNIX where {username} is your username, {password} is your password, and {dbname} is the name of your database:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u {username} -p{password} {dbname}

Here's an equivalent command in PowerShell that will process the .sql files in alphabetical order:

PS C:\backups\> ls ./*.sql –name | mysql --batch -u {username} -p{password} {dbname}

With the --batch option, mysql does not use the history file. Batch mode also results in nontabular output format and escaping of special characters.

Here's the same command to process the .sql files by their last modified datetime:

PS C:\backups\> ls ./*.sql –name | Sort-Object LastWriteTime | mysql --batch -u {username} -p{password} {dbname}

Amazingly, the Sort-Object cmdlet will sort by the LastWriteTime even though ls only returns the name field!

Extracting Compressed Files

There's no reason that you can't extract compressed archives using PowerShell, but, as alluded to earlier, the exact command will vary depending on which tool you used to compress them in the first place. 

There are third-party cmdlets that can take care of compressing and extracting the .sql files, if you want to stay within PowerShell.

Otherwise, the key is to learn how to use the tool via the command line.  In PowerShell, external programs can be run using the ampersand symbol (&).  There is also an $env variable that contains all your PC's environment variables.  They can be referenced by following the $env variable by a colon (:) and the requested environment variable. For instance, $env:ProgramFiles returns the path to your local programs files folder, usually – but not necessarily – C:\Program Files\. 

Here's the command to extract the contents of a .zip file using its associated WinZip utility:

Winzip32 –e [options] filename[.zip] folder

In PowerShell, we could write it as follows:

& "$env:programfiles\winzip\winzip32.exe" –e C:\dbbackups.zip  C:\dbextracts

The "&" is a short-form alias of "&cmd" and thus launches a new shell just as we saw in the command to launch MySQL.

Here's the equivalent call to the unrar executable, which comes with WinRAR:

& "$env:ProgramFiles\WinRAR\unrar.exe" e "C:\dbbackups.rar"

That will extract the file to the current working directory.

Again there is the issue of multiple files, and again there is a simple solution:

foreach ($file in ls *.rar ) { & "$env:programfiles\WinRar\unrar.exe" e $file.name }

That will extract all the files in the current working directory.

A Cautionary Tale

In contrast to the database backups, the database restore process is not so much about the job automation per se, since you'd expect a human to be present to launch the process anyway.  Rather, it's more for having a process to restore the database(s) that's easy enough to run that a backup person can manage it with relative ease. 

Case in point, we once had a DBA who liked to go moose hunting every year during hunting season, whatever was going on around the office.  It just so happened that a major release had gone out the week before.  Wouldn't you know it, the database for a 24/7 mission critical system went down the day after he left.  The backup person was a programmer/analyst who possessed moderate knowledge of relational databases.  Nonetheless, the databases were corrupted and needed to be completely rebuilt, which he did, spending over twelve hours struggling through it.  The problem was that everything had to be put in place in a very specific order or errors would result.  Had someone written a PowerShell script to execute all of the steps in their logical order, many hours of arduous work could have been avoided. 

The moral of the story is this: it is all well and good to have an established system for backing up your database(s), but without the equivalent restore process, it's really only half a system.

See all articles by Rob Gravelle

Mobile Site | Full Site