Sunday, August 16, 2009

Best MySQL backup strategy

There are several ways to take backups (some good, some bad, and some will depend on your situation). Here's the thought process I use for choosing a backup strategy.

  1. mysqldump

  2. If your data set is small (I realize "small" is a relative term.. to qualify it, let's say <10GB), then mysqldump will probably work great. It's easy, it's online and it's very flexible. Just a few things mysqldump can do:
    • backup everything or just certain databases or tables
    • backup only the DDL
    • optimize the dump for a faster restore
    • make the resultant sql file more compatible with other RDBMSes
      and many more things.

    However, the most important options are related to the consistency of your backup. My favorite options are:
    • --single-transaction : this option gives a consistent backup, if (and only if) the tables are using InnoDB storage engine. If you have any non-read-only MyISAM tables, then don't use this option when backing them up.
    • --master-data=2 : this option will make sure your dump is consistent (by doing a lock-all-tables unless you've added the option --single-transaction). The --master-data option also records the binary log position in the resulting dump file (=2 causes this line to be a comment in the dump file)


    Final note about mysqldump: keep in mind that the restore time may be significantly longer than the backup time. It will depend on several factors, for example, how many indexes you have.

  3. LVM snapshot

  4. For those that have larger datasets, a physical backup is the way to go. While you could take a cold backup (i.e., shutdown the MySQL service, copy the data directory, restart the service), many people do not want downtime. My favorite solution is snapshots. This can be hot (for InnoDB) or require a brief lock (for MyISAM). Don't forget to include all your data (include the ib_logfiles). Lenz provides a nice utility to help with this: http://www.lenzg.net/mylvmbackup/

  5. Other

  6. So let's say you have more than 10GB of data and you don't have the ability to use LVM. There are many other strategies that have their pros and cons. I'll quickly list some:

    • Cold backup
    • mysqlhotcopy (This applies only to MyISAM tables. It should not use the word "hot" since it is actually a "warm" backup)
    • Use replication and take cold backups on the slave
    • InnoDB HotBackup commercial product
    • Zmanda recover manager (This isn't a backup strategy per se, but it's a management console for backup and recovery)


  7. Binary log

  8. Regardless of which backup strategy you choose, it's important to synchronize the binary logs with your backup. This can be done by:

    • Restarting MySQL which starts a new binary log file
    • Issue FLUSH LOGS
    • Capture the binary log coordinates during the backup (e.g., SHOW MASTER STATUS)


    If you choose the mysqldump approach, then simply include --master-data=2 option. If you choose the LVM snapshot, including a flush logs would do nicely. You don't need to keep your binary logs forever. Consider using the --expire-logs-days option or manually archiving them.

12 comments:

  1. I wouldn't consider a Snapshot as a full backup, its good to restore the whole lot pretty fast, but won't protect you from data corruption (which can occur days, weeks or months before you need the backup), and you can't restore just one table, or only a few rows, if needed.

    ReplyDelete
  2. Sarah,

    Do not forget Xtrabackup - Open source alternative to commercial Innodb Hot Backup.

    ReplyDelete
  3. broddlit: Good point, however, you can restore the backup on another system (letting InnoDB do any necessary crash recovery) and then check tables for corruption

    PeterZ: thanks!

    ReplyDelete
  4. and mydumper, so you can dumps per table and do parallel import.

    ReplyDelete
  5. Maatkit's mk-parallel-dump is another good option if you can take the downtime hit. (I don't think it uses single-transaction due to the way it works. so it would have to lock all tables.) If you have a slave for backups, this is usually a pretty quick way to dump the database.

    ReplyDelete
  6. Hi Sarah, thanks for the plug about mylvmbackup! It may be worth pointing out that there are other snapshotting technologies available, e.g. ZFS on Solaris/OpenSolaris. And many SAN vendors support file system snapshots as well...

    ReplyDelete
  7. Sarah,
    Under what circumstance do you recommend a DB restore from idata file and/or the .frm files?
    I've seen people use this instead of just recreating the DB from mysql dump.

    -sv

    ReplyDelete
  8. With an LVM snapshot, you say it's important to issue a "FLUSH LOGS" statement, but why? Looking at the source code of mylvmbackup it doesn't look like he issues FLUSH LOGS. FLUSH LOGS is also not mentioned in the section on LVM backups on the mysql site http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

    ReplyDelete
  9. I've seen that others mentioned some tools like Xtrabackup and mydumper. If you don't have many databases you also could try MySQLBackupFTP (http://mysqlbackupftp.com).This tool is not free but it has a free version that allows to schedule up to two databases and send the compressed backup to a remote FTP server.
    An advantage of this tool is that it connects to MySQL through phpMyAdmin so you won't need privileges on the server.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Hi Sarah, thanks for the amazing article, you are good. As to MySQL backup I would advise Handy Backup http://www.handybackup.net/mysql-backup.shtml . I like it`s good for both of Windows and Linux MySQL, also there are hot & cold backup technologies.

    ReplyDelete