In this article, we will learn about innodb_force_recovery levels, what is their meaning and how it can be used to recover the entire database or subset of the data.
You are working as a MySQL DBA in a company and you received a notification of Power failure on a data center. Now, when you try to start the database and try to bring it online, one of your database does not start. We check the error logs and find out that it is full of checksum errors, page corruption, redo log failures. Unfortunetly the power failure intruptted the backup jobs as well.
In such scenarios, you can use the configuration parameter named innodb_force_recovery. It is a startup option which allows the MySQL InnoDB database engine to skip certain recovery steps which can bring the database online.
Note that, this setting does not fix the database corruption. It just enable the database so you can perform the data recovery.
Let us understand the innodb_force_recovery parameter, how it works, allowed value, and the benefits and drawbacks of each values.
What Is innodb_force_recovery?
It's a setting you add to your MySQL config file under [mysqld] and then restart. Like this:
[mysqld]
innodb_force_recovery=1
Each level builds on the one before. Level 4 does everything Level 1, 2, and 3 does, plus its own thing on top.
You always start at Level 1 and move up one at a time until MySQL starts. Don't jump straight to 6. That can actually make things worse.
Before We understandeach levels, lets us understand few things.
When your motive is to get the data out of the database, always start at the lowest level.
Do not jump at highest level. Levels 4 and above can cause more damage than it fix the database.
Always use the Level 6 is a last option.
Now, let us go through all of the levels.
Level 1 — Ignore Corrupt Pages
While starting, when MySQL Server encounters a corrupt page, it stops immediately. And usually you will see following in the error logs.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page [page number]
InnoDB: Page checksum mismatch
Now, when we start the MySQL server with the innodb_force_recovery level 1, it will skip the scanning the corrupt pages. It will allow MySQL Server start normally and bring the database online. This setting will solve the startup problem which occurs due to few corrupted pages, a secondary index or some checksum errors on disk.
The Risk of implementing the setting is Low.
Level 2 — Stop Background Threads
Sometimes, MySQL starts properly but crashes after few seconds. This issue usually occurs when a background thread like purge, cleanup, or maintenance threads reads corrupted data.
Level 2 stops these background threads. The MySQL will start the database but it does not run any threads in the background that might touch the broken pages.
The level 2 used when the corruption is in areas related to old row versions or transaction cleanup. Its still a pretty safe level hence risk is considered as Low.
Level 3 — Skip Transaction Rollback
After a crash, MySQL rolls back any transactions that are not committed yet. If the logs that are used to rollback process are corrupted, the MySQL will crash while doing the clean up. Level 3 skips the entire rollback process.
The problem is, rows from the incomplete transactions that never actually finished might still show up in your tables. For example, someone started inserting a row and the server crashed before they committed it. Normally that row would disappear on restart. But if you have set the Level 3, the row will be visible.
With that said, the data consistency is no longer guaranteed hence is risk level is moderate, but you can export / recover the data.
Level 4 — Skip Buffered Index Updates
MySQL InnoDB does not write changes to secondary indexes immediately. It keeps them in a buffer and applies them later in the background. This helps to keep performance of database server optimal.
If this buffer becomes corrupted, the server can fail during startup while trying to apply these pending changes. When you start the MySQL using level 4, the MySQL InnoDB ignores this buffer and pending changes will not apply. The secondary indexes are now incomplete. Some rows exist in the table but the index does not know about them. A query that uses such an index may return less records, and you will not get any error message about it.
Note that, after level 4, InnoDB starts in read-only mode. You cannot insert, update or delete anything. Oracle also states clearly that values of 4 and above can permanently damage the data files. Levels 1, 2 and 3 are considered relatively safe. Level 4 is where that guarantee ends.
If levels 1, 2 and 3 did not bring the server up and you have reached here, then your only goal is to export the data. Do not run the application against this server and do not treat it as a working database.
Level 5 — Pretend Undo Logs Don't Exist
Undo logs hold the old version of a row so that InnoDB can roll back a transaction that did not complete. During startup after a crash, InnoDB normally reads these logs to decide what has to be rolled back.
When we start MySQL using level 5, it skips the “reading undo log” process completely. Due to that, any transaction that not committed or incomplete, will be treated as committed.
Half-finished transactions become permanent. Rows that were never supposed to be saved will appear in your tables. A parent row may exist without its child rows, or the opposite. At this level, logical inconsistency is expected. So, try to export whatever data you can and verify it against your business rules or against your last known good backup. The risk level is high.
Level 6 — Skip Crash Recovery Entirely
Normally, after a crash, InnoDB uses the redo logs to replay the changes that were already written to the log but not yet written to the data files. This is how the pages are brought back to a consistent state. Level 6 skips this entire process and the database will running with the data pages exactly as they were at the moment of the crash.
You should use this option only when the redo logs are unreadable and the server refuses to start in any other way.
To export the data, keep your queries simple and try to avoid the tables that you already know are damaged. Take the dump immediately, verify it on a separate server. Do not attempt to continue running on this server. If you have reached Level 6 and the server still will not come up cleanly, you can use a tool like Stellar Repair for MySQL to recover the data. It offload a copy of the corrupted .ibd or .frm files and perform the repair which is useful because it is not touching the live instance.
The Recovery Strategy
Always use one step at a time. Start at Level 1. If the server does not come up than go to Level 2. You can also use SHOW ENGINE INNODB STATUS before you move up a level. It help us to point to the exact page or table causing the failure. If the server comes online, stop moving to next step. Export the dump or backup the database. Following command can be used:
mysqldump --all-databases > emergency_backup.sql
once you have backup or database dump available, create a new instance and restore the backup on it.
Summary
In this article we learned about the innodb_force_recovery parameter and how it is used to bring the server online and recover the database. innodb_force_recovery will not fix your database. It buys you time to get your data out before things get worse. Levels 1 to 3 are usually enough, and the damage stays limited. Levels 4 to 6 are risky and your data gets less reliable each time.
Top comments (0)