Backup MySql database automatically


Recently, I had to work on a rather out of the scope issue, regarding the backup of mysql databases automatically. I mean manual backing of data or databases can be trouble some, and one might forget usually. So, why not write a script that can be scheduled to run automatically. This will ensure that the data is backed up regularly, and there is no loss of data at least for 1 day interval.


Step 1:

The first thing to do here is to create a database backup from the command prompt. To accomplish this you need to have MySql installed on your computer (the one where the backup needs to be done). The server to be backed up can be on any other computer on the network, doesnt matter. You can always change the host. So, if you have Mysql installed on your machine, you should be able to find a mysqldump.exe file located in the bin folder. Lets say I have installed it inside the C:\Program Files, then the location of the mysqldump.exe should be

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

This is the particular file required, that will connect to the mysql database server, and retrieve the dump file.

File Display

Step 2:

The next step is to actually write the dos command to perform the backup of the database. Let us open the command prompt. This can be done by Clicking on

Start > All Programs > Accessories > Command Prompt
Start > Run > type “cmd” > OK

Next, point your prompt to the location of the mysqldump.exe file using the following command

      cd C:\Program Files\MySQL\MySQL Server 5.5\bin

The syntax for the dos command can be seen as below

      mysqldump.exe –e –u[username] -p[password] -h[hostname] [database name] > C:\[filename].sql

For Example if my database name is “testdb”, username is “testuser”, password is “pass” and the hostname is “localhost”, then my dos command will look like

      mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

Once, we confirm that the above command works, we are ready create our batch file, which will be put up into the scheduler.

Ms Dos


Step 3:

Creating a Batch file. Open up notepad from

Start > All Programs > Accessories > Notepad
Start > run > type “notepad” > OK

Type the above code into the notepad as give below

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

Save the file as auto_backup.bat, where bat stands for batch. Place the batch file in a safe location, where it cannot be edited easily.

Another line that we need to add to the file is the renaming of the backup file according to the date. This will help keep the backup files according to the date. Add the following line to rename the backup file, assuming the file name is auto_backup.bat.

      ren C:\auto_backup.sql auto_backup%date:~4,2%-%date:~7,2%-%date:~10,4%.sql

The above command will rename the file as auto_backup_01_03_2012.sql

With the addition of the above code, you batch file will look like

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

ren C:\auto_backup.sql auto_backup%date:~4,2%-%date:~7,2%-%date:~10,4%.sql

Save the file after that.


Step 4:

Assigning the batch file to be run at a certain time, using the Task Scheduler.

In Windows 7, it is found inside Control Panel > Administrative Tools > Task Scheduler

In Windows XP, it is found inside Control Panel > Scheduled Tasks

Create a new task, and when it asks for a program to run, choose the batch file that we just created. (auto_backup.bat), select a suitable repeat interval, and the time to run the file.

With that, we are done.