IIS Aid

There is little doubt that MySQL is one of, if not the most popular open source database in the world. Take a look at most of the popular PHP applications and you'll find that MySQL more often than not heads up the supported databases list. No matter what you use MySQL for, it is likely that the data it stores is the most important component of your environment which means a suitable backup plan should be high on your agenda. There are two main methods for backing up your MySQL databases; An SQL dump or a raw file copy. As MySQL MyISAM databases are stored as files the raw file copy method is quicker to backup and restore, however unlike the SQL dump method the files are not directly editable in a text editor and InnoDB databases are not supported. In this guide we'll be looking at a batch script that manages the backup of all your databases end to end using the SQL dump method.

One thing I noticed about most of the available backup solutions for MySQL was that you had to explicitly tell the backup tool which MySQL databases to backup. This didn't really suit my situation as I was often adding and removing databases as were several people using my server who also had permissions to create and delete their own databases. The overhead of having to create or delete a backup job every time a database was added or removed would have been havoc to keep up with and cost a lot of time. What I needed was a solution that backed up each database separately into their own directories and also able to automatically detect when databases were added and removed and alter the backup job accordingly. Basically a one stop set and forget backup tool that once I had put in place I would never have to worry about MySQL backups again.

After some searching around the Internet I found a batch script that came very close to what I wanted, so I took it and modified it a little to suit my needs. What I ended up with was a script that;

• Backs up each database in a separate directory in a definable location, and creates the directory structure if it doesn't exist.
• Monitors the MySQL data directory so that new databases are included automatically in the backup job, and likewise databases that have been deleted are automatically excluded.
• Backs up each database using the SQL dump method and then compresses the output to a file using the database name as well as the current date and time as the file naming convention.
• Checks, analyzes, optimizes and repairs MyISAM databases that have changed since the last run
• Logs details of all backup jobs to a specified location
• Emails the details of what databases were backed up including the start and finish times of the script using definable email addresses for the from and to values as well as a definable SMTP server name.

Not as elegant or pretty as some of the backup solutions out there but the only one I've found for Windows that separates the database backups into individual directories and monitors the MySQL data directory for changes. To do all of this the batch script uses 4 executable files, which are;

• mysqldump - used for SQL dump of databases.
• mysqlcheck - used to analyze, optimize and repair MyISAM databases
• gzip - a command line based compression tool
• blat - a command line based email client

Mysqldump and mysqlcheck both come with MySQL server, however you will have to download gzip and blat which can be gotten from the links above. Once you have these and have downloaded the mysql_backup.bat batch file from the bottom of this article it is time to configure the options located at the top of the batch file. Using your favorite text editor open mysql_backup.bat and you should find the following variables;

• backupdir - Directory where all backups will be stored. Can be a network drive if you wish.
• mysqldir - The MySQL installation directory
• mysqldatadir - The directory that contains all your MySQL data, which by default is in a directory called "data" inside your MySQL server directory
• logdir - The directory where
• dbuser - User to connect to MySQL with. Note this user will require access to all databases being backed up.
• dbpass - Password for MySQL user.
• zip - Full path to the directory containing gzip.exe
• mailer - Full path to the directory containing blat.exe
• to - Email address to send the reports to
• from - Email address to send the reports from
• server - Email server used to send the reports

Simply replace the "ENTER_VALUE_HERE" string with the details from your server and save the file. Now it is time to give the script a test run, so open a command prompt window and navigate to the directory containing the backup script then enter mysql_backup.bat and press enter. Verify that the directory structure has been created in your backup directory, and that mysqldump is creating the SQL dump files. You should also check that the logs are being written and the report sent to the email address you specified.

Final step is to set mysql_backup.bat as a scheduled task so that it runs automatically at set intervals. How often you backup your databases is up to you, and really depends on how heavy their usage is. Personally I back up each day as typically gzip does a very good job of compression of the SQL files as they are text based. To set up your scheduled task go to a command prompt and type in the following command which will schedule MySQL backups every day at 11pm, making sure to alter the path to mysql_backup.bat to that of your system;

If everything has gone as planned then you should now have all your MySQL databases backed up at the intervals you set, regardless of how many databases you add or remove. If you have any comments, suggestions of problems using this script please feel free to comment of post in the forums.