Wondering how to do Innodb_force_recovery in MySQL? We can help you.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see procedure followed by our Support Techs in order to perform this task.

Innodb_force_recovery in MySQL

Today, let us discuss about the different sections that describe Innodb_force_recovery in MySQL by our Support Techs.

• Point-in-Time Recovery
• Recovery from Data Corruption or Disk Failure
• InnoDB Crash Recovery
• Tablespace Discovery During Crash Recovery

Point-in-Time Recovery

Firstly, to recover an InnoDB database to the present from the time at which the physical backup was made.

Then, you must run MySQL server with binary logging enable, even before taking the backup.

To achieve point-in-time recovery after restoring a backup, you can apply changes from the binary log that occur after the backup was made.

Recovery from Data Corruption or Disk Failure

If your database becomes corrupt or disk failure occurs, you must perform the recovery using a backup.

In the case of corruption, first find a backup that is not corrupt.

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 occur 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 okay.

It is best to try restarting the computer first.

Doing so may eliminate errors that appear as database page corruption.

If MySQL still has trouble starting because of InnoDB consistency problems.

InnoDB Crash Recovery

To recover from an unexpect 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 un-commit 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 369163704
InnoDB: Doing recovery: scanned up to log sequence number 374340608
InnoDB: Doing recovery: scanned up to log sequence number 379583488
InnoDB: Doing recovery: scanned up to log sequence number 384826368
InnoDB: Doing recovery: scanned up to log sequence number 390069248
InnoDB: Doing recovery: scanned up to log sequence number 395312128
InnoDB: Doing recovery: scanned up to log sequence number 400555008
InnoDB: Doing recovery: scanned up to log sequence number 405797888
InnoDB: Doing recovery: scanned up to log sequence number 411040768
InnoDB: Doing recovery: scanned up to log sequence number 414724794
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up in
total 518425 row operations to undo
InnoDB: Trx id counter is 1792
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: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 1511, 518425 rows to undo
...
InnoDB: Waiting for purge to start
InnoDB: 5.7.18 started; log sequence number 414724794
...
./mysqld: ready for connections.

InnoDB crash recovery consists of several steps:

1. Tablespace discovery

Firstly, tablespace discovery is the process that InnoDB uses to identify tablespaces that require redo log application.

See Tablespace Discovery During Crash Recovery.

2. Redo log application

Then, redo log application is perform during initialization, before accepting any connections.

If all changes are flush from the buffer pool to the tablespaces (ibdata* and *.ibd files) at the time of the shutdown or crash, redo log application is skip.

InnoDB also skips redo log application if redo log files are missing at startup.

Removing redo logs to speed up recovery is not recommend, even if some data loss is acceptable.

Removing redo logs should only consider after a clean shutdown, with innodb_fast_shutdown set to 0 or 1.

3. Roll back of incomplete transactions

Incomplete transactions are any transactions that were active at the time of unexpect exit or fast shutdown.

The time it takes to roll back an incomplete transaction can 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 roll back.

In extreme cases, when rolling back transactions is expect to take an exceptionally long time, it may faster to start InnoDB with an innodb_force_recovery setting of 3 or greater.

4. Change buffer merge

Next, 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.

5. Purge

Deleting delete-mark records that are no longer visible to active transactions.

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 commit or in XA

PREPARE state when the server exits.

The rollback is performe by a background thread, execute in parallel with transactions from new connections.

Until the rollback operation is complete, new connections may encounter locking conflicts with recover transactions.

In most situations, even if the MySQL server is kill unexpectedly in the middle of heavy activity, the recovery process happens automatically and no action is require of the DBA.

If a hardware failure or severe system error corrupt InnoDB data, MySQL might refuse to start.

Tablespace Discovery During Crash Recovery

If, during recovery, InnoDB encounters redo logs written since the last checkpoint, the redo logs must apply to affect tablespaces.

The process that identifies affect tablespaces during recovery is refer to as tablespace discovery.

Tablespace discovery is perform by scanning redo logs from the last checkpoint to the end of the log for MLOG_FILE_NAME records that are written when a tablespace page will modify.

An MLOG_FILE_NAME record contains the tablespace space ID and file name.

On startup, InnoDB opens the system tablespace and redo log.

If there are redo log records written since the last checkpoint, affect tablespace files are open base on MLOG_FILE_NAME records.

MLOG_FILE_NAME records are written for all persistent tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, and undo log tablespaces.

Redo-log-based discovery has the following characteristics:

1. Only tablespace *.ibd files modify since the last checkpoint are access.
2. Tablespace *.ibd files that are not attach to the InnoDB instance are ignore when redo logs will apply.
3. If MLOG_FILE_NAME records for the system tablespace do not match the server configuration affecting system tablespace data file names, recovery fails with an error before redo logs will apply.
4. If tablespace files reference in the scan portion of the log are missing, startup is refuse.
5. Redo logs for missing tablespace *.ibd files are only disregard if there is a file-delete redo log record (MLOG_FILE_DELETE) in the log.

[Need help with similar query? We’d be glad to assist you]

Conclusion

In short, today we discussed about the details of Innodb_force_recovery in MySQL.