How To Back Up MySQL Databases Without Interrupting MySQL

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 04/23/2007

This article describes how you can back up MySQL databases without interrupting the MySQL service. Normally, when you want to create a MySQL backup, you either have to stop MySQL or issue a read lock on your MySQL tables in order to get a correct backup; if you don't do it this way, you can end up with an inconsistent backup. To get consistent backups without interrupting MySQL, I use a little trick: I replicate my MySQL database to a second MySQL server, and on the second MySQL server I use a cron job that creates regular backups of the replicated database.

This document comes without warranty of any kind! I want to say that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

Preliminary Note

To follow this tutorial, you need a second MySQL server (the slave), and you have to set up MySQL replication from your first MySQL server (the system from where you want to take backups, the master) to the slave, e.g. as described in this tutorial: http://www.howtoforge.com/mysql_database_replication. Setting up MySQL replication is beyond the scope of this document.

The whole setup that I describe here has to be done on the slave MySQL server!

I have tested this on a Debian system; this should work on other distributions as well, but it's possible that some paths differ (in the script /usr/local/sbin/mysqlbackup.sh).

Doing Automated Backups Of The Replicated Databases On The Slave

After you have set up a working MySQL replication from the master to the slave, I assume that you want to do automatic backups of the slave database to the directory /home/sqlbackup. First, we must create that directory:

mkdir /home/sqlbackup

Next we create the shell script /usr/local/sbin/mysqlbackup.sh that stops the slave, makes an SQL dump of the whole MySQL database in /home/sqlbackup (the file name of the SQL dump will look like this: backup-20070423-18.sql; this is a dump taken on April 23, 2007, at 18.00h), restarts the slave afterwards (the slave will then catch up on everything that has happened on the master in the meantime so that no data is lost), and deletes all SQL dumps in /home/sqlbackup that are older than two days:

vi /usr/local/sbin/mysqlbackup.sh

#!/bin/sh

datum=`/bin/date +%Y%m%d-%H`

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave

/usr/bin/mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables \
      --all-databases > /home/sqlbackup/backup-${datum}.sql

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword start-slave

for file in "$( /usr/bin/find /home/sqlbackup -type f -mtime +2 )"
do
  /bin/rm -f $file
done

exit 0

(Please make sure that you replace yourrootsqlpassword with the password of the root MySQL user on the slave!)

Now we must make the script executable:

chmod 755 /usr/local/sbin/mysqlbackup.sh

Of course, we don't want to run the /usr/local/sbin/mysqlbackup.sh manually; instead, we create a cron job that runs the script automatically every three hours:

crontab -e

0 */3 * * * /usr/local/sbin/mysqlbackup.sh &> /dev/null

Of course, you are free to modify the cron job to run as often as you need it.

That's it, using this method you can now back up your MySQL database without interrupting the MySQL service on the master server.

Links

• MySQL: http://www.mysql.com