How To Back Up MySQL Databases Without Interrupting MySQL

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Thu, 2007-05-10 16:36. :: Backup | High-Availability | MySQL

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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Thu, 2011-03-31 08:07.
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.
Submitted by Vlatko Å urlan (not registered) on Tue, 2010-05-04 08:20.
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.
Submitted by Anonymous (not registered) on Thu, 2010-02-04 22:36.

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

Submitted by Anonymous (not registered) on Sun, 2010-08-01 04:38.

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

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

Submitted by Anonymous (not registered) on Sun, 2009-11-15 13:54.
works great with mysql 5.0. Thanks.
Submitted by Rudolf Pietersma (not registered) on Fri, 2009-09-04 22:49.

I get this error when running the script:
root@web01://usr/local/ispconfig/interface/web/themes/default/images# /usr/local/sbin/mysqlbackup.shSlave stopped/usr/bin/mysqladmin: Error starting slave: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Submitted by kvz (registered user) on Fri, 2007-11-30 10:21.
Here's a script that can transfer all mysql databases to another server.
Submitted by andmalc (registered user) on Tue, 2007-05-15 13:50.

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.