Best practices for backing up a MySQL DB

Best Practices to take MySQL server backup:

MySQL Replication

Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server. Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master without any downtime.

Using MySQL Dump

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.

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/

Using MySQL Enterprise Backup

Advantages of using MySQL Enterprise Backup:

• "Hot" Backups of InnoDB tables takes place entirely online, without blocking Backup only particular tables or tablespaces
• Only backup the data that changed since a prior backup
• Compressed Backup - Saves storage upto 90% and many more..

Reference: http://www.mysql.com/products/enterprise/backup/features.html http://www.mysql.com/products/enterprise/backup.html

I would recommend setting up a dedicated replica to use for backup. This will let you perform any backup tasks without impacting the primary. As this does add complexity to your architecture, you'll want to monitor the replication lag to ensure everything is working.

As for the actual process, you have a couple options without any third-party tools. Snapshots can be taken using the mysqldump command (assuming you're using InnoDB): mysqldump --all-databases --single-transaction > all_databases.sql. Depending on the data size, it may be preferable to shut down MySQL and backup the data files directly. When the replica is restarted it will replay all the events the primary received in the duration it was down. If you're using MySQL Enterprise the mysqlbackup utility does this.

Incremental backups can be taken by enabling the binary log on the replica. Obviously, this only records events that mutate data so you'll need to combine this with the snapshots above.