Its pretty common to backup mysql with mysqldump utility but this backup acquires a global lock on all tables. Especially when your database is huge it takes few minutes to take a backup. When you only one database / shard in production running a dump will result in downtime. Another popular way to hot backup mysql is using LVM snapshot. mylvmbackup is a perl script that obtains a read lock on all tables and flushes all server caches to disk and creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. But FLUSH TABLES WITH READ LOCK may take a while which is varies on systems with long running queries. On EC2 this process becomes even messy but unmounting /mnt/ and then create a physical volume(pvcreate) and a few logical volumes(lvcreate).

Apart from these there is innodb hot backup, a commercial software to make hotbackups of both MyISAM and InnoDB. There is xtrabackup which is a open source implementation of the same from percona. The biggest advantage of xtrabackup is it does n’t lock your database during backup(except MyISAM tables).

Installation: Installing xtrabackup is kind of tricky. Installing from source requires to compile InnoDB. This process varies slightly from 5.0.x to 5.1.x. You can use one of the binaries from percona website. Since I was on CentOS I tried to use the rpm binaries from RedHat. rpm - i errored out with “requires mysql server”. Even though we have a compiled mysql rpm’s requires one installed from yum or something. I used the linux binary which worked for me. Copy the binaries in the bin directory to /usr/local/bin or some PATH.

Usage: Using xtrabackup is pretty easy.

innobackupex-1.5.1 /mnt/something/ --user=root --password=xxxxx

This will backup all databases to “something” directory under today timestamp. You might want to pass a list of databases to be backed up. There are a few options you can pass to innobackupex like datadir, default configuration, incremental backup etc. Check all the options here.

innobackupex-1.5.1 --defaults-file=/etc/my.cnf /mnt/something/ --user=root --password=xxxxx

Since I installed mysql from source I had pass my.cnf and make sure your my.cnf has datadir option defined in it.

Apart from backing up to a directory, gziping and copying the directory to another server you can stream the backup directly to another host there by making it easy to setup slaves. (But never the less streaming never worked for me.)

innobackupex-1.5.1: Created backup directory /root/src/xtrabackup-1.3/bin
101106 17:34:44  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/etc/my.cnf" --unbuffered --
101106 17:34:44  innobackupex-1.5.1: Connected to database with mysql child process (pid=16855)
101106 17:34:52  innobackupex-1.5.1: Connection to database server closed
101106 17:34:52  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/etc/my.cnf" --unbuffered --
101106 17:34:52  innobackupex-1.5.1: Connected to database with mysql child process (pid=16865)
101106 17:34:56  innobackupex-1.5.1: Connection to database server closed

If you had any success with streaming backups please post a comment.

After making a database backup you need to make sure you backup the InnoDB logs for proper functioning of the database. You can apply-logs using the same innobackupex along with a special flag.

innobackupex-1.5.1 --defaults-file=/etc/my.cnf --user=root --password=xxx --apply-log /mnt/something/2010-12-08_18-01-12/

This will apply all the innodb logs to the backup. This operation will complete quickly basing on your innodb log size. After the logs are applied your backup is ready. All you need to do is copy the timestamped folder into the destination machine. You can use scp / rsync to copy the directory over. If you are on EC2, you can attach and mount a EBS volume and copy the backup into the volume and unmount and mount it back on the destination server. After the transfer is complete there is only last step to copy back the new data into the new database.

innobackupex-1.5.1 --defaults-file=/etc/my.cnf --user=root --password=xxx --copy-back /mnt/something/2010-12-08_05-21-36/

This will copy the new data into the database.

You might see a error that cannot copy mysql. This is because its trying to copy mysql database to the new server. You might want to mv the mysql in /var/lib/mysql to mysql.bak and retry the operation which will succeed.

Soon after the copy-back make sure you make to make mysql user owner of the /var/lib/mysql since you might have ran these operations as root / some other user.