15.17.2 InnoDB Recovery

To recover an InnoDB database to the present from the time at which the physical backup was made, you must run MySQL server with binary logging enabled, even before taking the backup. To achieve point-in-time recovery after restoring a backup, you can apply changes from the binary log that occurred after the backup was made. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

If your database becomes corrupted or disk failure occurs, you must perform the recovery using a backup. In the case of corruption, first find a backup that is not corrupted. After restoring the base backup, do a point-in-time recovery from the binary log files using mysqlbinlog and mysql to restore the changes that occurred after the backup was made.

In some cases of database corruption, it is enough to dump, drop, and re-create one or a few corrupt tables. You can use the CHECK TABLE statement to check whether a table is corrupt, although CHECK TABLE naturally cannot detect every possible kind of corruption.

In some cases, apparent database page corruption is actually due to the operating system corrupting its own file cache, and the data on disk may be okay. It is best to try restarting the computer first. Doing so may eliminate errors that appeared to be database page corruption. If MySQL still has trouble starting because of InnoDB consistency problems, see Section 15.20.2, “Forcing InnoDB Recovery” for steps to start the instance in recovery mode, which permits you to dump the data.

To recover from a MySQL server crash, the only requirement is to restart the MySQL server. InnoDB automatically checks the logs and performs a roll-forward of the database to the present. InnoDB automatically rolls back uncommitted transactions that were present at the time of the crash. During recovery, mysqld displays output similar to this:

InnoDB: The log sequence number 664050266 in the system tablespace does not match 
the log sequence number 685111586 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Using 'tablespaces.open.2' max LSN: 664075228
InnoDB: Doing recovery: scanned up to log sequence number 690354176
InnoDB: Doing recovery: scanned up to log sequence number 695597056
InnoDB: Doing recovery: scanned up to log sequence number 700839936
InnoDB: Doing recovery: scanned up to log sequence number 706082816
InnoDB: Doing recovery: scanned up to log sequence number 711325696
InnoDB: Doing recovery: scanned up to log sequence number 713458156
InnoDB: Applying a batch of 1467 redo log records ...
InnoDB: 10%
InnoDB: 20%
InnoDB: 30%
InnoDB: 40%
InnoDB: 50%
InnoDB: 60%
InnoDB: 70%
InnoDB: 80%
InnoDB: 90%
InnoDB: 100%
InnoDB: Apply batch completed!
InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 561887 row 
operations to undo
InnoDB: Trx id counter is 4096
...
InnoDB: 8.0.1 started; log sequence number 713458156
InnoDB: Waiting for purge to start
InnoDB: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 3596, 561887 rows to undo
...
./mysqld: ready for connections....

InnoDB crash recovery consists of several steps:

The steps that follow redo log application do not depend on the redo log (other than for logging the writes) and are performed in parallel with normal processing. Of these, only rollback of incomplete transactions is special to crash recovery. The insert buffer merge and the purge are performed during normal processing.

After redo log application, InnoDB attempts to accept connections as early as possible, to reduce downtime. As part of crash recovery, InnoDB rolls back transactions that were not committed or in XA PREPARE state when the server crashed. The rollback is performed by a background thread, executed in parallel with transactions from new connections. Until the rollback operation is completed, new connections may encounter locking conflicts with recovered transactions.

In most situations, even if the MySQL server was killed unexpectedly in the middle of heavy activity, the recovery process happens automatically and no action is required of the DBA. If a hardware failure or severe system error corrupted InnoDB data, MySQL might refuse to start. In this case, see Section 15.20.2, “Forcing InnoDB Recovery”.

For information about the binary log and InnoDB crash recovery, see Section 5.4.4, “The Binary Log”.

If, during recovery, InnoDB encounters redo logs written since the last checkpoint, the redo logs must be applied to affected tablespaces. The process that identifies affected tablespaces during recovery is referred to as tablespace discovery.

Tablespace discovery is performed using tablespace map files that map tablespace IDs to tablespace file names. Tablespace map files are stored in the innodb_data_home_dir directory. If innodb_data_home_dir is not configured, the default location is the MySQL data directory (datadir).

There are two tablespace map files (tablespaces.open.1 and tablespaces.open.2) that are written to in circular fashion. Tablespace map files are only used during recovery. The files are ignored during normal startup.

In case of lost or corrupted tablespace map files, see Lost or Corrupted Tablespace Map Files.

If tablespace map files are lost or corrupted, the innodb_scan_directories option can be used to specify tablespace file directories at startup. This option causes InnoDB to read the first page of each tablespace file in the specified directories and recreate tablespace map files so that the recovery process can apply redo logs.

This option can also be used to specify the directory path of a missing tablespace file. For example, if recovery reports an error due to a missing tablespace file, you can configure innodb_scan_directories to search for the tablespace file in a specific directory.

innodb_scan_directories may be specified as an option in a startup command or in a MySQL option file. Quotes are used around the argument value because otherwise a semicolon (;) is interpreted as a special character by some command interpreters. (Unix shells treat it as a command terminator, for example.)

Startup command:

mysqld --innodb-scan-directories="directory_path_1;directory_path_2"

MySQL option file:

[mysqld]
innodb_scan_directories="directory_path_1;directory_path_2"

When innodb_scan_directories is specified at startup, the InnoDB startup process prints messages similar to the following, reporting the directories that were scanned and the number of tablespace files found:

InnoDB: Directories to scan 'directory_path_1;directory_path_2'
InnoDB: Scanning 'directory_path_1'
InnoDB: Scanning 'directory_path_2'
InnoDB: Found 10 '.ibd' file(s)