Backup and Recovery

MySQL

–If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data.

–If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master.

–Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 13.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 13.5.5.2, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

— MySQL supports incremental backups: You need to start the server with the –log-bin option to enable binary logging; see Section 5.11.4, “The Binary Log”. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump –flush-logs, or mysqlhotcopy –flushlog. See Section 8.10, “mysqldump — A Database Backup Program”, and Section 8.11, “mysqlhotcopy — A Database Backup Program”.

–If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the –slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.