How To Back Up MySQL Databases Without Interrupting MySQL

Version 1.0
Author: Falko Timme

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: https://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.

 

Share this page:

10 Comment(s)

Add comment

Please register in our forum first to comment.

Comments

By:

There's also a script mysqlhotcopy that is part of the MySQL distribution (at least on Debian) which can backup a live server with cp or scp.

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html.

By:

Here's a script that can transfer all mysql databases to another server.

By: Anonymous

works great with mysql 5.0. Thanks.

By: Rudolf Pietersma

I get this error when running the script:

[email protected]://usr/local/ispconfig/interface/web/themes/default/images# /usr/local/sbin/mysqlbackup.sh
Slave stopped
/usr/bin/mysqladmin: Error starting slave: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

By: Anonymous

when i start the task this problem is occured

/usr/local/sbin/Mysqlbackup.sh: line 4: /home/Mysqlbackup/backup-/bin/date +%Y%m%d-%H.sql: No such file or directory

how can i fix this problem?

thnx

By: Anonymous

It appears that you did not use back-tics when specifying datum:

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

By: Vlatko Šurlan

The script that I am sharing backs up the files along with MySQL data but does it live and directly into an ssh connection so it might be of interest to many here: Live files and MySQL data backup.

By: Anonymous

Hi, If your tables are in innodb you can use the --single-transaction option. You can do your dump with your slave active with this. --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables.I use kls backup for backing up my system.

By: User

I added this line after I started the slave back up. Saves space in the future if you have several DBs or they are large.

/bin/gzip /home/sqlbackup/backup-${datum}.sql

Hi Falko.

I need small help on how to take backups for master/slave replication databases.please let me know  using manaul Mysqldump .please guide me as my client wants to apply patch on the application end.just want to be on the safer side to restore the databases.we are having full VM backups .Do you want to us to take VMbackup before the process of both master/slave so that we can restore.

 

thanks

Goutham