There have been instances when we need to schedule the backup process of MySQL databases regularly. So I thought of writing batch file that can be added in the Windows scheduler. I used the ‘mysqldump’ utility that comes with the MySQL distributions. It dumps the database definition along with all the data, indexes, triggers and SPs in the form of .sql file. I’ve also made the batch file to compress the output .sql file.
The complete script can be configured by setting some variables in it. I’ll explain the configuration parameters below.
| SN | Parameter | Comments |
| 1 | backupdir | Tell where you want to store the final archive. If the folder doesn’t exist, Please create it. |
| 2 | workdir | Tell where your temporary .sql file will get generated. |
| 3 | mysqldir | Tell where the mysqldump exe is available. Generally it resides in the bin folder of the MySQL distribution. |
| 4 | logdir | Tell where you want to put the logfile. I’ve included a small logging mechanism with the script as well |
| 5 | zip_dir | Tell where your compressing utility is. It can be either Winzip or Winrar or gzip or any compression utility. |
| 6 | Servername, database, mysqluser, mysqlpassword | DB Specific settings. Configure it here. Please note this is available to anyone who has the access to this batch file. |
Once you have the configuration done. Open the Windows Scheduler (from control panel) add this batch file and schedule it with the interval you want. You are done. You have your backup process running. The same script can be modified slightly to handle multiple databases if you need. This script can be downloaded from here
Do share your comments.