Ghetto MySQL Innobackup with rsync

I was reading an interesting samba mailing list comment about using rsync on live MySQL databases.

The author said this:

“Assuming a short break in accessibility is tolerable, I’d

1. run rsync to the backup
2. stop the server
3. run rsync to the backup (should be much much faster now)
4. restart the server.”

Combining rsync and mysqlhotcopy we can get a little fancier:

Ghetto Innobackup-style backup with rsync

1. STOP SLAVE; FLUSH TABLES
2. run rsync to the backup
3. FLUSH TABLES WITH READ LOCK; SHOW SLAVE STATUS;SHOW MASTER STATUS
4. run rsync to the backup (should be much much faster now)
5. UNLOCK TABLES
6. START SLAVE

Note that the read lock and unlock must be done while on the same database connection, and innodb continues to update indexes even when read-locked.

Also, record the master and slave status values. They may be very useful later if you want to apply binlogs to the backup, or initialize a slave.

This technique would be very suitable for non-critical snapshots like QA copies and on quiet databases.

It may be suitable for busy databases if other methods aren’t working out, for instance you don’t have LVM snapshots setup and innobackup is locking your MyISAM tables too long.

rsync -a is also useful for backing up master binlogs every 5 minutes on a live site. Normally you’re better off setting up a slave just running the slave IO thread, though.

Many databases have features to allow “log shipping.” With MySQL, similar functionality is accomplished by doing FLUSH LOGS and rsync, or using replication (there is a command to not execute the replication stream, just save it to disk.)

FLUSH NO_WRITE_TO_BINLOG LOGS
FLUSH TABLES WITH READ LOCK