7.3 Example Backup and Recovery Strategy

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
mysqldump and 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:

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.