How to Setup Backup for your MySQL Databases on Windows Server (using PowerShell and Task Scheduler)

Given that I am now hosting most of my websites under WordPress, I was finding it hard to keep up with my disaster recovery strategy. I tried to use various plugins that are available for WordPress, but this was like Spock wishing himself “Live long and prosper”.

Those plugins created backups alright, but they backed up within the WordPress website folder, which was of no implicit use (without having to write additional scripts to copy them out of there) because if the entire folder or drive was lost, there was no use of the backups taken.

Enter PowerShell and Task Scheduler.

PowerShell

I am using PowerShell simply because I have a list of databases to backup and I want to name the backup files according to the time stamp of the backup. I could probably do this using traditional DOS batch commands, or using custom programs in .NET/C#, etc., but that was either too complicated or overkill. PowerShell fits that need perfectly and my code is all done in about 8 lines of code in all. First, let’s take a look at the code:

$DateString = (Get-Date -Format "yyyyMMdd")
$Databases = @("wp_database_1", "wp_database_2", "wp_database_3")

foreach ($databaseName in $Databases)
{
    $BackupCommand = '"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe" 
                     --defaults-file=Z:\Backups\Database\MySQL\credentials.ini --host=localhost 
                     --protocol=tcp --port=1234 --default-character-set=utf8 --add-drop-database 
                     --add-drop-table --result-file="Z:\Backups\Database\MySQL\' + 
                     $databaseName + '\bak_' + $DateString + '.sql" --skip-triggers "' + 
                     $databaseName + '"'

    Invoke-Expression "& $BackupCommand"
}

Now let's take it apart:

My backup file naming scheme is simple. I want a file name that starts off as “bak_” and is suffixed by the current date in yyyyMMdd (“20150201” for Feb 1, 2015), with a “.sql” extension. For this, I need to get the date in the yyyyMMdd format, and I do this using the Get-Date -Format “yyyyMMdd” command, setting that output to the $DateString variable.

I have a fixed set of databases to backup, and I don’t have too many (if you do, you can find a different way to get the names into the script). So I can store the list of names into an array $Databases. Of course, I don’t call my databases as “wp_database_1“, those names are for purposes of this very public blog.

Next, I loop through the array of database names in a simple foreach loop, and create a command line command for the backup operation. We need to do this, rather than directly fire off the command, because we have several quoted values like the path to the sqldump program itself. The entire command is composed into the $BackupCommand string variable.

WARNING: I have added –add-drop-database and –add-drop-table commands to the mysqldump. This is because I intend the backups to always be a FULL backup that I can restore onto a fresh install of mysql.

NOTE: Since the scripts I am using are backing up WordPress databases, I am not using the –add-routines flag and I have the –skip-triggers (since there are no routines or triggers in these DBs). You will need to manage these appropriately in your script.

You can look up the details of the various parameters I am using by running:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --help > %USERPROFILE%\Desktop\MySQLDump.txt

and then examining the contents of the “MySQLDump.txt” that will get generated on your desktop.

The command now stored in the $BackupCommand variable is invoked (run) using the Invoke-Expression PowerShell command. The “&” is needed there because we are using quoted identifiers to run the sqldump command.

We need one more file before the script above will run. Yes, that is the file referenced as “Z:\Backups\Database\MySQL\credentials.ini”. You can create this file anywhere that will be accessible to your PowerShell script, I chose to keep both files in the same folder for convenience sake. Create the file with the following contents (only):

[mysqldump]
user=<USERID>
password=<PASSWORD>

Note that you CANNOT use your “root” credentials here. Now you are all set.

Do not forget to look carefully at all the elements of the script that are provided in bold lettering and replace them with the actual values at your end.

Save the PowerShell file somewhere (by the name “MySqlBackup.ps1”). You can run and test this script from a PowerShell shell by simply running:

PS> .\MySqlBackup.ps1

Once you have fixed any syntax / other errors or added other code to your liking, you are ready to configure Task Scheduler.

Task Scheduler

The idea is to create a task that runs every day at a time of your choice with suitable retries in case of failure. This task would run even without a user being logged on, and would run our PowerShell script. To walk you through the entire wizard to create the task is pretty voluminous. So, download this file (it is a “.xml” file, so save it to a local folder) and proceed as below:

1. Open the file you downloaded above in Notepad.
2. Scroll right down to the end and find the <WorkingDirectory> element.
3. Edit the value (removing the square brackets) to point it to the location where you will have your backup PowerShell script. Take care to include only the directory name and not the .ps1 file name in this path!

   Example: <WorkingDirectory>Z:\Backups\Database\MySQL</WorkingDirectory>

4. If you named this script something else, then be sure to provide that name for the <Arguments> element just above the <WorkingDirectory>.

NOTE: The task will be created with the same name as the XML file. So be sure to name the XML file something you want to see in the Task Scheduler’s task list!

Save the XML file and open up Task Scheduler (from Administrative Tools). Click on “Task Scheduler Library” on the left side tree, and from the Actions pane on the right, click “Import Task…“. Select our XML file. Go through each step of the wizard and ensure that there are no errors, or change values to suit your particular needs. When you are satisfied, click OK.

The task is configured to allow you to run it adhoc. This is useful for both testing and creating adhoc backups if a try fails or before a rebuild. So, right click on the newly created task (it will be created with the same name as the XML file name) and select “Run”. Watch the “History” tab (you may need to select “Enable All Task History” from the Actions pane if History is shown as “disabled”) for errors and fix any errors that you may see.

Now your system is configured to take automatic daily backups of your MySQL Databases! Enjoy!