MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section describes InnoDB recovery. Topics
include:
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. You can use the Tablespace Monitor to check the
integrity of the file space management inside the tablespace
files.
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 14.21.2, “Forcing InnoDB Recovery” for steps to start the
instance in recovery mode, which permits you to dump the data.
To recover from an unexpected MySQL server exit, 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: Log scan progressed past the checkpoint lsn 430875675 InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 436118528 InnoDB: Doing recovery: scanned up to log sequence number 441361408 InnoDB: Doing recovery: scanned up to log sequence number 446604288 InnoDB: Doing recovery: scanned up to log sequence number 451847168 InnoDB: Doing recovery: scanned up to log sequence number 457090048 InnoDB: Doing recovery: scanned up to log sequence number 462332928 InnoDB: Doing recovery: scanned up to log sequence number 467575808 InnoDB: Doing recovery: scanned up to log sequence number 472818688 InnoDB: Doing recovery: scanned up to log sequence number 478061568 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 483304448 InnoDB: Doing recovery: scanned up to log sequence number 488547328 InnoDB: Doing recovery: scanned up to log sequence number 493790208 InnoDB: Doing recovery: scanned up to log sequence number 496426509 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1441473 row operations to undo InnoDB: Trx id counter is 2304 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed ... InnoDB: Waiting for purge to start Starting in background the rollback of uncommitted transactions InnoDB: Rolling back trx with id 2022, 1441473 rows to undo ... InnoDB: 5.6.36 started; log sequence number 496426509 ... ./mysqld: ready for connections.
The InnoDB
crash recovery
process consists of several steps:
Redo log application
Redo log application is the first step and is performed
during initialization, before accepting any connections. If
all changes are flushed from the
buffer pool to the
tablespaces
(ibdata* and *.ibd
files) at the time of the shutdown or crash, redo log
application is skipped. InnoDB also skips
redo log application if redo log files are missing at
startup.
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
innodb_fast_shutdown set to
0 or 1.
Roll back of incomplete transactions
Incomplete transactions are any transactions that were active at the time of unexpected exit 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 InnoDB with an
innodb_force_recovery
setting of 3 or greater. See
Section 14.21.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, 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 exited. 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 14.21.2, “Forcing InnoDB Recovery”.
For information about the binary log and
InnoDB crash recovery, see
Section 5.4.4, “The Binary Log”.