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

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.


2. LVM snapshot

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/


3. Other

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)
  



4. Binary log

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.