Scheduling periodic MySQL backup in Windows

13 01 2011

When you are working on a project that involves a database and which is also very prone to crashes :D. Its time to check out a backup method to have safe copies of the database.

The easiest method we find for taking a MySQL backup is to use its own inbuilt “mysqldump”. Even though this provides with a backup, it needs all the parameters to work. In-order to make a automatic backup scheduler with the mysqldump would take two basic steps. One would be to make a script, which can take a backup of the database and the second would be to schedule it so that it works uninterrupted at the server.

So the script first. For this we use the windows batch script. We want to take a backup everyday of the month, and the backup can be overwritten at the end of month with the next month backup. and also we would be using another script which takes a monthly backup on the start of every month. that way we have backup for a year.

First step in creating the script is to locate the folder containing the mysqldump. It would be usually something like “c:\xampp\mysql\bin”. Next is to set up a folder where the backup would be stored. We create two set of folders for daily and monthly backup separate.

Now for the script, we use the following script for daily backup

@echo off
echo Database backup running..

c:\<path-to-mysql>\bin\mysqldump -u[user] -p[password] –result-file=”c:\<path>\backup\daily_%DATE:~4,2%.sql” [database]

echo Done!

and the following script for monthly backup

@echo off
echo Database backup running..

c:\<path-to-mysql>\bin\mysqldump -u[user] -p[password] –result-file=”c:\<path>\backup\monthly_%DATE:~7,2%.sql” [database]

echo Done!

The syntax is self explanatory.  This would provides with backup which have the files name as the database name with a extension of the data of the month or the month detail. So if the database is user it would provide with a backup of name User_<date> and User_<month> as the backup. Save the two scripts as two separate files. Now for the scheduling part

This we can do using the windows scheduling command at , So for our scheduling, we are scheduling the daily backup at 2:00 am daily and the monthly backup at 3:00 am, just to make sure no deadlock occurs.

Type the following command in the command prompt

at 02:00 /every:1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31 c:\<path>\<daily_backup_batchfile>.bat

at 03:00 /every:1 c:\<path>\<monthly_backup_batchfile>.bat

And that all. The backup has been scheduled successfully.