Automating db backup using mysqldump on Windows 2003

Today, I found a real gem of a post. I have been trying to work this process out for a few days. I tried unsuccessfully to use PHP to build my .sql backup file a couple different ways. Each would create the .sql file, but both would cause problems to the moodle app. when I imported them (restored). I was able to use phpMyAdmin directly to create a .sql file using the Export feature. Very simple this way, lots of good documentation and examples. The problem I was having was how did I automate that?

I do not think phpMyAdmin or mySQLWorkbench or mySQL administrator have the tools to automate or schedule the backup of the DB.

When I was looking today, I found the MySQLWorkbench documentation talking about using the mysqldump.exe utility to handle the creation of the .sql file. As a matter of fact, that is what phpMyAdmin is doing, its just providing a GUI to the mysqldump.exe utility.

To my point.

I was still looking with an eye toward scheduling the task. When I googled the correct question "schedule mysql db backup windows 2003", that lead me to this most excellent post.

In a nutshell, the author is using DOS to call the mysqldump.exe program and gives a great example of how to do that, while passing in required parameters, like this:

@echo off
echo Running dump...
c:\\bin\mysqldump -u[user] -p[password] --result-file="c:\\backup.%DATE:~0,3%.sql" [database]
echo Done!

I added those lines to a new file and saved as backupDB.bat. Remember the [ brackets] are placeholders and are not actually used when building the syntax. My syntax looked like this:

C:\wamp\bin\mysql\mysql5.5.20\bin\mysqldump -u username -ppasswordused --result-file="c:\automatedDBBackups\backup.%DATE:~0,3%.sql" dbname

I opened a command window and ran the backupDB file. It took me a couple tries to get the syntax correct, specifically NOT placing a space between the -p passwordused.

When it worked, it created c:\automatedDBBackups\backup.Fri.sql

I tested the restore of the file and saw NO PROBLEMS in my application. I did not expect I would since I used the source utility to generate, not a PHP script.

If that was not enough, the author also demoed how to use a Windows command called at to automate the task of calling it. Wow.

I had planned on using a Scheduled task to call the backup.bat file, but this at function is too sweet. I added a line from the command console like this:

at 04:00 /every:M,T,W,Tu,F,S,Su c:\backupDB

and pressed enter and Windows said "Added a new job with job ID = 1"

Amazing.

Moral of the story?

Keep looking for the best solution and you will find it.