XtraDB/InnoDB Recovery Modes

You are viewing an old version of this article. View the current version here.

The XtraDB/InnoDB recovery mode is a mode used for recovering from emergency situations. The innodb_force_recovery server system variable sets the recovery mode. A mode of 0 is normal use, while the higher the mode, the more stringent the restrictions. Higher modes incorporate all limitations of the lower modes.

The recovery mode should never be set to a value other than zero except in an emergency situation.

Generally, it is best to start with a recovery mode of 1, and increase in single increments if needs be. With a recovery mode < 4, only corrupted pages should be lost. With 4, secondary indexes could be corrupted. With 5, results could be inconsistent and secondary indexes could be corrupted (even if they were not with 4). A value of 6 leaves pages in an obsolete state, which might cause more corruption.

To recover the tables, you can execute SELECTs to dump data, and DROP TABLE to remove corrupted tables.

The following modes are available:

Recovery Modes

0The default mode while XtraDB/InnoDB is running normally. It is the only mode permitting changes to the data.
1(SRV_FORCE_IGNORE_CORRUPT) allows the the server to keep running even if corrupt pages are detected. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.
2(SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge.
3(SRV_FORCE_NO_TRX_UNDO) does not roll back transactions after the crash recovery.
4(SRV_FORCE_NO_IBUF_MERGE) does not calculate tables statistics and prevents insert buffer merges.
5(SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the undo logs when starting.
6(SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.

Note also that XtraDB by default will crash the server when it detects corrupted data in a single-table tablespace. This behaviour can be changed - see the innodb_corrupt_table_action system variable.

Fixing Things

Try to set innodb_force_recovery to 1 and start mariadb. If that fails, try a value of "2". If a value of 2 works, then there is a chance the only corruption you have experienced is within the innodb "undo logs". If that gets mariadb started, you should be able to dump your database with mysqldump. You can verify any other issues with any tables by running "mysqlcheck --all-databases".

If you were able to successfully dump your databases, or had previously known good backups, drop your database(s) from my mariadb command line like "DROP yourdatabase". Stop mariadb. Go to /var/lib/mysql (or where ever your mysql data directory is located) and "rm -i ib*". Start mariadb, create the database(s) you dropped (CREATE yourdatabase), and then import your most recent dumps: "mysql < mydatabasedump.sql"


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.