I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using 'mysqldump' or any other DB backup tools?
I'll probably cron job the schedule so that it's done nightly and then backup the files with my backup system.
Thanks.
Best Practices to take MySQL server backup:
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.
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.
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/
Advantages of using MySQL Enterprise Backup:
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.
--single-transaction but don't forget to add --events --routines and I always use --triggers too, even though it's enabled by default, since it can be disabled in my.cnf. I would say it's pretty much always good to use these as standard practice whether you currently have those types of objects in your database or not.
– Michael - sqlbot
Sep 27 '13 at 20:39
