## Backing up MySQL automatically using a batch file

Categories:

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;

at 23:00 /every:M,T,W,Th,F,S,Su x:\path\to\mysql_backup.bat

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.

Average rating
AttachmentSize
MySQL_Backup.bat3.17 KB

### error running batch file "mysql_backup"

Hi, firstly thank you for this batch file...

im getting error below though...not sure if you've seen this error before, but if you could assist with this that would be great.

Rgds
H

C:\mysql>MySQL_Backup.bat
Volume in drive C has no label.
Volume Serial Number is CC1F-8285

Directory of C:\mysql\backup

13/02/2008 04:53 PM

.
13/02/2008 04:53 PM ..
13/02/2008 04:58 PM bin
13/02/2008 05:02 PM mysql
13/02/2008 05:02 PM test
13/02/2008 05:02 PM test1
0 File(s) 0 bytes
8 Dir(s) 51,971,596,288 bytes free
Directory mysql Exists
Backing up database mysql_02-13-2008_17-13-41.sql.gz
\gzip.exe: No such file or directory
mysqldump: Got errno 22 on write
Done...
\gzip.exe: No such file or directory
mysqldump: Got errno 22 on write
Done...
\gzip.exe: No such file or directory
mysqldump: Got errno 22 on write
Done...
Directory test Exists
Backing up database test_02-13-2008_17-13-41.sql.gz
\gzip.exe: No such file or directory
mysqldump: Got errno 22 on write
Done...
Directory test1 Exists
Backing up database test1_02-13-2008_17-13-41.sql.gz
\gzip.exe: No such file or directory
mysqldump: Got errno 22 on write
Done...

Have you downloaded the gzip compression tool as instructed in the article? If so, double check that you have set the correct path for the %zip% variable in the top of the batch file.
----------------
Dominic Ryan
3 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster

### why not just install mysql

why not just install mysql administrator from the mysql site and set it to do automatic backups?

Using MySQL administrator is fine for basic backups, however using batch file backups gives you more options. For instance you can backup all databases automatically instead of having to explicitly set which ones are backed up as with MySQL administrator. You can also do things like separate backups into individual directories, backup several times a day if need be, compress backup files, email backup verifications, optimise, analyse and repair tables, and basically anything that can be done via the command line.
----------------
Dominic Ryan
3 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster

### how to send email fetching data from database.

hi Mr. Ryan
i hav to send email (reminder mail) to customers whoose validity is about to Expires
i hav to fetch end date, name of customer , Email- Id, from data base.
using above information i hav to send reminder to customer that "U r Validity is about to Expires"
Can u help me out
hope u will help me

### This can be done, however it

This can be done, however it would be somewhat more involved as it requiresfetching and manipulation of data. To be honest this kind of functionality should be a part of your account management software rather than patched together from manually created scripts which ultimately will be a nightmare to maintain. If you haven't yet I'd be taking a look at some of the CRM products out there, such as SugarCRM.
----------------
Dominic Ryan
3 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster

### transfer data from one tbl to the other using batch file

Hi Ryan,

can I make a batch file that will query the table to count group by ID, and position and put the output in another table??

the reason why i need this file is that the table is growing too big to execute even the simplest query..so i need to group the data total it, n put the total in another table..so i don't have to count the total from the first table..i can just extract it from the new table -containing the total-

### Send the db's?

Is their a way to make it instead of sending the log to send the db backups themselves?

### Sure, although I'd only

Sure, although I'd only recommend doing that if the databases you are backing up are fairly small. The scripting you'd need to do what you want would actually be a lot less complex than the batch file attached to this article, however I think you'd probably be able to get all the functionality you want from PHPMyBackupPro. Worth a look at least, but of you really want this done via batch file I'll see if I can come up with something next week.
----------------
Dominic Ryan
4 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster

### no php

I dont like the phpmybackup becuase the way the server is setup their is no privte www, so if i put it their everyone can acess it. I tried todo the attachment command on blat and just select the lattest file from the db name i wanted. However at around 6mbs compressed it timesout on mail.

What about ftping to another server of mine?