7.3 Example Backup and Recovery Strategy

Menu:
backup-policy:: Establishing a Backup Policy
recovery-from-backups:: Using Backups for Recovery
backup-strategy-summary:: Backup Strategy Summary

This section discusses a procedure for performing backups that enables you to recover data after several types of crashes:

Operating system crash
Power failure
File system crash
Hardware problem (hard drive, motherboard, and so forth)

The example commands do not include options such as '--user' and '--password' for the note 'mysqldump': mysqldump. and note 'mysql': mysql. client programs. You should include such options as necessary to enable client programs to connect to the MySQL server.

Assume that data is stored in the 'InnoDB' storage engine, which has support for transactions and automatic crash recovery. Assume also that the MySQL server is under load at the time of the crash. If it were not, no recovery would ever be needed.

For cases of operating system crashes or power failures, we can assume that MySQL's disk data is available after a restart. The 'InnoDB' data files might not contain consistent data due to the crash, but 'InnoDB' reads its logs and finds in them the list of pending committed and noncommitted transactions that have not been flushed to the data files. 'InnoDB' automatically rolls back those transactions that were not committed, and flushes to its data files those that were committed. Information about this recovery process is conveyed to the user through the MySQL error log. The following is an example log excerpt:

 InnoDB: Database was not shut down normally.
 InnoDB: Starting recovery from log files...
 InnoDB: Starting log scan based on checkpoint at
 InnoDB: log sequence number 0 13674004
 InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
 InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
 InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
 InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
 ...
 InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
 InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
 InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
 InnoDB: 1 uncommitted transaction(s) which must be rolled back
 InnoDB: Starting rollback of uncommitted transactions
 InnoDB: Rolling back trx no 16745
 InnoDB: Rolling back of trx no 16745 completed
 InnoDB: Rollback of uncommitted transactions completed
 InnoDB: Starting an apply batch of log records to the database...
 InnoDB: Apply batch completed
 InnoDB: Started
 mysqld: ready for connections

For the cases of file system crashes or hardware problems, we can assume that the MySQL disk data is not available after a restart. This means that MySQL fails to start successfully because some blocks of disk data are no longer readable. In this case, it is necessary to reformat the disk, install a new one, or otherwise correct the underlying problem. Then it is necessary to recover our MySQL data from backups, which means that backups must already have been made. To make sure that is the case, design and implement a backup policy.

File: manual.info.tmp, Node: backup-policy, Next: recovery-from-backups, Prev: backup-strategy-example, Up: backup-strategy-example

7.3.1 Establishing a Backup Policy

To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MySQL with several tools. For example, note MySQL Enterprise Backup: mysql-enterprise-backup. can perform a physical backup of an entire instance, with optimizations to minimize overhead and avoid disruption when backing up 'InnoDB' data files; note 'mysqldump': mysqldump. provides online logical backup. This discussion uses *note 'mysqldump': mysqldump.

Assume that we make a full backup of all our 'InnoDB' tables in all databases using the following command on Sunday at 1 p.m., when load is low:

 $> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

The resulting '.sql' file produced by note 'mysqldump': mysqldump. contains a set of SQL note 'INSERT': insert. statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using 'FLUSH TABLES WITH READ LOCK'). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the *note 'FLUSH': flush. statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

It was assumed earlier that the tables to back up are 'InnoDB' tables, so '--single-transaction' uses a consistent read and guarantees that data seen by note 'mysqldump': mysqldump. does not change. (Changes made by other clients to 'InnoDB' tables are not seen by the note 'mysqldump': mysqldump. process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the 'MyISAM' tables in the 'mysql' database, there must be no administrative changes to MySQL accounts during the backup.

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the '--log-bin' option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MySQL server that was started with the '--log-bin' option and that has been running for some days, we find these MySQL binary log files:

 -rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
 -rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
 -rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
 -rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
 -rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
 -rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
 -rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Each time it restarts, the MySQL server creates a new binary log file using the next number in the sequence. While the server is running, you can also tell it to close the current binary log file and begin a new one manually by issuing a 'FLUSH LOGS' SQL statement or with a note 'mysqladmin flush-logs': mysqladmin. command. note 'mysqldump': mysqldump. also has an option to flush the logs. The '.index' file in the data directory contains the list of all MySQL binary logs in the directory.

The MySQL binary logs are important for recovery because they form the set of incremental backups. If you make sure to flush the logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup. Let's modify the previous *note 'mysqldump': mysqldump. command a bit so that it flushes the MySQL binary logs at the moment of the full backup, and so that the dump file contains the name of the new current binary log:

 $> mysqldump --single-transaction --flush-logs --master-data=2 \
          --all-databases > backup_sunday_1_PM.sql

After executing this command, the data directory contains a new binary log file, 'gbichot2-bin.000007', because the '--flush-logs' option causes the server to flush its logs. The '--master-data' option causes *note 'mysqldump': mysqldump. to write binary log information to its output, so the resulting '.sql' dump file includes these lines:

 -- Position to start replication or point-in-time recovery from
 -- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Because the *note 'mysqldump': mysqldump. command made a full backup, those lines mean two things:

The dump file contains all changes made before any changes written to the 'gbichot2-bin.000007' binary log file or higher.
All data changes logged after the backup are not present in the dump file, but are present in the 'gbichot2-bin.000007' binary log file or higher.

On Monday at 1 p.m., we can create an incremental backup by flushing the logs to begin a new binary log file. For example, executing a note 'mysqladmin flush-logs': mysqladmin. command creates 'gbichot2-bin.000008'. All changes between the Sunday 1 p.m. full backup and Monday 1 p.m. are in the 'gbichot2-bin.000007' file. This incremental backup is important, so it is a good idea to copy it to a safe place. (For example, back it up on tape or DVD, or copy it to another machine.) On Tuesday at 1 p.m., execute another note 'mysqladmin flush-logs': mysqladmin. command. All changes between Monday 1 p.m. and Tuesday 1 p.m. are in the 'gbichot2-bin.000008' file (which also should be copied somewhere safe).

The MySQL binary logs take up disk space. To free up space, purge them from time to time. One way to do this is by deleting the binary logs that are no longer needed, such as when we make a full backup:

 $> mysqldump --single-transaction --flush-logs --master-data=2 \
          --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Note:

Deleting the MySQL binary logs with note 'mysqldump --delete-master-logs': mysqldump. can be dangerous if your server is a replication source server, because replica servers might not yet fully have processed the contents of the binary log. The description for the note 'PURGE BINARY LOGS': purge-binary-logs. statement explains what should be verified before deleting the MySQL binary logs. See *note purge-binary-logs::.

File: manual.info.tmp, Node: recovery-from-backups, Next: backup-strategy-summary, Prev: backup-policy, Up: backup-strategy-example

7.3.2 Using Backups for Recovery

Now, suppose that we have a catastrophic unexpected exit on Wednesday at 8 a.m. that requires recovery from backups. To recover, first we restore the last full backup we have (the one from Sunday 1 p.m.). The full backup file is just a set of SQL statements, so restoring it is very easy:

 $> mysql < backup_sunday_1_PM.sql

At this point, the data is restored to its state as of Sunday 1 p.m.. To restore the changes made since then, we must use the incremental backups; that is, the 'gbichot2-bin.000007' and 'gbichot2-bin.000008' binary log files. Fetch the files if necessary from where they were backed up, and then process their contents like this:

 $> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

We now have recovered the data to its state as of Tuesday 1 p.m., but still are missing the changes from that date to the date of the crash. To not lose them, we would have needed to have the MySQL server store its MySQL binary logs into a safe location (RAID disks, SAN, ...) different from the place where it stores its data files, so that these logs were not on the destroyed disk. (That is, we can start the server with a '--log-bin' option that specifies a location on a different physical device from the one on which the data directory resides. That way, the logs are safe even if the device containing the directory is lost.) If we had done this, we would have the 'gbichot2-bin.000009' file (and any subsequent files) at hand, and we could apply them using note 'mysqlbinlog': mysqlbinlog. and note 'mysql': mysql. to restore the most recent data changes with no loss up to the moment of the crash:

 $> mysqlbinlog gbichot2-bin.000009 ... | mysql

For more information about using note 'mysqlbinlog': mysqlbinlog. to process binary log files, see note point-in-time-recovery::.

File: manual.info.tmp, Node: backup-strategy-summary, Prev: recovery-from-backups, Up: backup-strategy-example

7.3.3 Backup Strategy Summary

In case of an operating system crash or power failure, 'InnoDB' itself does all the job of recovering data. But to make sure that you can sleep well, observe the following guidelines:

Always run the MySQL server with the '--log-bin' option, or even '--log-bin=LOG_NAME', where the log file name is located on some safe media different from the drive on which the data directory is located. If you have such safe media, this technique can also be good for disk load balancing (which results in a performance improvement).
Make periodic full backups, using the note 'mysqldump': mysqldump. command shown earlier in note backup-policy::, that makes an online, nonblocking backup.
Make periodic incremental backups by flushing the logs with 'FLUSH LOGS' or *note 'mysqladmin flush-logs': mysqladmin.

File: manual.info.tmp, Node: using-mysqldump, Next: point-in-time-recovery, Prev: backup-strategy-example, Up: backup-and-recovery