Schedule Regular MySQL Backups for wampserver (windows)

Occasionally we are forced to use Windows to host our applications under wamp platforms. but scheduling backups in Windows under a wamp platform like xampp or wampserver is completely unknown to us.

Dumping a datestamp file.

First, you’ll need The path to your mysql /bin/ directory (e.g., C:mysql).
We want to dump the file to a datestamped file. There are several ways to do this, but the easiest way is through a batch file. The command we’ll be using is:

c:<path-to-mysql>binmysqldump -u[user] -p[password] --result-file="c:<path>backup.%DATE:~0,3%.sql" [database]

Just fill in the username, password, and database with your MySQL information.

Which date format to use?

You’ve dumped all of your MySQL data in [database] into a file named backup.Mon.sql (assuming it’s Monday). The %DATE:~0,3% string basically runs the DOS “date” command and parses out the first three characters.

If you’d rather see YYYYMMDD format, use

%DATE:~10,4%%DATE:~7,2%%DATE:~4,2%.

The advantage (or disadvantage, depending on your needs) of using the week day is that you will eventually have only seven backup files… you can go back, at most, seven days. Eventually each day will be overwritten. Using YYYYMMDD will allow you to create an unlimited amount of backup files, and go back and recover to any date you’d like.

Take this command, and create a backup file (e.g., mysql-backup.bat). It can look like this…

@echo off
echo Running dump...
c:<path-to-mysql>binmysqldump -u[user] -p[password] --result-file="c:<path>backup.%DATE:~0,3%.sql" [database]
echo Done!

Now you can run mysql-backup.bat from a command prompt (hit Start->Run and type ‘cmd’). It it works, you should see a success message and a file should be generated in the directory you specified. If not, make sure you’ve got the correct username, password, and database. Then try it again.

Automating mysql backups process

Linux has cron, Windows has at. The at command schedules commands to run as often as you’d like. In this case, we’re going to run the backup one day a week at 2 AM.

Open up the command prompt and type the following:

at 02:00 /every:M,T,W,Th,F,S,Su c:<path>mysql-backup.bat

Once you run this, it should return “Added a new job with job ID = X”

Type at to make sure that it's in the schedule.

If you’ve set everything up correctly you should now be running nightly MySQL backups.

In order to transfer your backups on a distant server nightly via FTP protocol, you can follow the steps of my next post here.
Cheers