Hello,
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.
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
or
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.
Creating a Batch file. Open up notepad from
Start > All Programs > Accessories > Notepad
Or
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.