This section discusses a procedure for performing backups that enables you to recover data after several types of crashes:
Operating system crash
File system crash
Hardware problem (hard drive, motherboard, and so forth)
The example commands do not include options such as
--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
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.
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 atInnoDB: log sequence number 0 13674004InnoDB: Doing recovery: scanned up to log sequence number 0 13739520InnoDB: Doing recovery: scanned up to log sequence number 0 13805056InnoDB: Doing recovery: scanned up to log sequence number 0 13870592InnoDB: Doing recovery: scanned up to log sequence number 0 13936128...InnoDB: Doing recovery: scanned up to log sequence number 0 20555264InnoDB: Doing recovery: scanned up to log sequence number 0 20620800InnoDB: Doing recovery: scanned up to log sequence number 0 20664692InnoDB: 1 uncommitted transaction(s) which must be rolled backInnoDB: Starting rollback of uncommitted transactionsInnoDB: Rolling back trx no 16745InnoDB: Rolling back of trx no 16745 completedInnoDB: Rollback of uncommitted transactions completedInnoDB: Starting an apply batch of log records to the database...InnoDB: Apply batch completedInnoDB: Startedmysqld: 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.