This section describes
InnoDB recovery. Topics
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
TABLE naturally cannot detect every possible kind of
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
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.
automatically checks the logs and performs a roll-forward of the
database to the present.
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....
consists of several steps:
Tablespace discovery is the process that
InnoDBuses to identify tablespaces that require redo log application. See Tablespace Discovery During Crash Recovery.
Redo log application
Redo log application is performed during initialization, before accepting any connections. If all changes are flushed from the buffer pool to the tablespaces (
*.ibdfiles) at the time of the shutdown or crash, redo log application is skipped.
InnoDBalso skips redo log application if redo log files are missing at startup.
The current maximum auto-increment counter value is written to the redo log each time the value changes, which makes it crash-safe. During recovery,
InnoDBscans the redo log to collect counter value changes and applies the changes to the in-memory table object.
For more information about how
InnoDBhandles auto-increment values, see Section 18.104.22.168, “AUTO_INCREMENT Handling in InnoDB”, and InnoDB AUTO_INCREMENT Counter Initialization.
When encountering index tree corruption,
InnoDBwrites a corruption flag to the redo log, which makes the corruption flag crash-safe.
InnoDBalso writes in-memory corruption flag data to an engine-private system table on each checkpoint. During recovery,
InnoDBreads corruption flags from both locations and merges results before marking in-memory table and index objects as corrupt.
Removing redo logs to speed up recovery is not recommended, even if some data loss is acceptable. Removing redo logs should only be considered after a clean shutdown, with
Incomplete transactions are any transactions that were active at the time of crash or fast shutdown. The time it takes to roll back an incomplete transaction can be three or four times the amount of time a transaction is active before it is interrupted, depending on server load.
You cannot cancel transactions that are being rolled back. In extreme cases, when rolling back transactions is expected to take an exceptionally long time, it may be faster to start
3or greater. See Section 15.20.2, “Forcing InnoDB Recovery”.
Change buffer merge
Applying changes from the change buffer (part of the system tablespace) to leaf pages of secondary indexes, as the index pages are read to the buffer pool.
Deleting delete-marked records that are no longer visible to active transactions.
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,
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
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 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 is not
configured, the default location is the MySQL data directory
There are two tablespace map files
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
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
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.)
MySQL option file:
specified at startup, the
process prints messages similar to the following, reporting the
directories that were scanned and the number of tablespace files
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)