Backing Up MySQL Databases on a Windows Server

I’ve been managing Microsoft Windows based Plesk servers for many years now and one thing that has always been a problem has been backing up MySQL databases, in particular, databases that use InnoDB tables.

Disclaimer: Backups are a critical part of any infrastructure and as such, you are responsible for your own testing.  My advice and script below are provided purely as-is with no guaranteed of success, data integrity or eternal youth.

I’ve spent many hours researching ways to do this before finding my solution (below), with all roads seeming to lead to one of these unsatisfactory solutions:

Percona Xtrabackup

Percona Xtrabackup is a backup utility for MySQL that – by all accounts – is pretty solid and reliable.  Unfortunately, it’s also Linux-only.  They did dabble in porting it over to Windows and there is still an ‘Alpha’ release of version 1.6 (available here) but its old and unsupported which is never a good start for a backup solution. They are currently on version 2.2.9 which gives you an idea of just how far in the past the Windows version is

LogicNow MaxBackup

As a LogicNow partner, I was so relieved when I discovered their backup software supported MySQL – finally someone had taken the headache away from me!

Unfortunately, we discovered when a customer suffered a data loss and needed their database restoring, MaxBackup only supports restoring an entire server, not individual databases.

Vembu StoreGrid

For a while we were a Vembu partner (before they pulled the rug out from under the partners then sqldumped on them from a great height).

Ironically, while a far inferior solution, StoreGrid actually backed up and restored MySQL databases very well – its just a shame they worthless as a company and willing to make bad business calls, leaving thousands of customers stranded in an attempt to force a new product/service on them.

The Backup Solution

So while doing all this research, I kept reading about a command-line utility called mysqldump.  I generally avoided it as I wanted something with a GUI that could handle the backup and the restore.  In the end, when it turned out this was never going to happen, I turned my attentions upon mysqldump and started putting together a simple batch file, for which I had a few requirements:

• It had to backup all the databases (so new ones were automatically included)
• It had to create a folder for each database
• It had store multiple copies of the database (named with the date)
• It had to purge older backups to save disk space

My final batch script looks like this:

SET MySQLDataFolder="C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\data"
SET MySQLBackupFolder="F:\BACKUPS\mySQL"

for /f %%x in ('wmic path win32_utctime get /format:list ^| findstr "="') do set %%x
SET today=%Year%-%Month%-%Day%

for /d %%i in (%MySQLDataFolder%\*) do "C:\Program Files (x86)\Parallels\Plesk\MySQL\bin\mysqldump.exe" -uUSERNAME -hLOCALHOST -pMYPASSWORD --single-transaction -c -n %%~ni | "c:\Program Files\7-Zip\7z.exe" a -tgzip -si"%%~ni.sql" "%MySQLBackupFolder%\mySQL\%%~ni\%%~ni(%today%).sql.gzip"

forfiles -p "F:\BACKUPS\mySQL" -s -m *.* /D -15 /C "cmd /c del @path"

I have deliberately left in all of the Plesk references as in a standard Windows Plesk installation, these paths will likely be the same.  Effectively it breaks down like this:

MySQLDataFolder is the folder where your MySQL databases are stored.

MySQLBackupFolder is the destination backup folder.

The next two lines get a UTC format date (YYYYMMDD) and set it to the variable today

We then loop through all of the databases in the specified MySQL data folder and run MySQLDump against them, with the –single-transaction parameter to ensure InnoDB integrity.  Z-Zip is used to ZIP the resulting sql file and store it in a subfolder of the backup destination, based on the name of the database – the ZIP file is named after the database and the backup date.

Finally, I used forfiles to purge all backup files older than 15 days.

The Restore Solution

No backup is of any use whatsoever without the ability to restore it.  Fortunately mysqldump generates an SQL script that can be executed against your database in a variety of ways.  I tested the backups generated by my script above by installing XAMPP on my PC and using the built in ‘PhpMyAdmin’ to simply import the SQL scripts to a database.

Worked like a charm and I was able to see MyISAM and InnoDB tables, all with their data safely intact.