As a database administrator, we should be prepared to a situation where your production database server refuse to start. The error log is full of red warnings, and the client start asking when the database will be back. The first thing most of us try to start the server using innodb_force_recovery. A lot of time,database server starts and we can extract the data from the database. But sometimes it does not work, and the DBA is left with fewer options.
This article is about that second situation. What do you do when innodb_force_recovery does not bring your database back?
In our previous article InnoDB innodb_force_recovery Levels 1–6: What Each Level Actually Does, we learned about the innodb_force_recovery setting and what it does. The innodb_force_recovery is a setting in MySQL that tells the InnoDB engine to skip certain checks and recovery steps during startup, so that a damaged database can still come online. It has six levels, from 1 to 6, and each level skips more things than the one before it.
When we use level 1, the server start even if it finds corrupted pages.
When we use Level 2, the server skips the master thread, which normally does background cleanup.
When we use Level 3, the server skips transaction rollback.
When we use Level 4, the server skips insert buffer merging.
When we use Level 5, the server will start without using Undo logs.
When we use Level 6, the server will not perform the log recovery.
The idea of using this parameter is at each level, MySQL does less validation, so it has a better chances of starting. But the problem is higher the level, the more likely get a database with missing or inconsistent data.
Now, let us understand why the MySQL cannot start even after using the innodb_force_recovery settings.
Why It Sometimes Fails Anyway
Even after trying all six levels, some DBAs find that the server still will not start, or it starts but crashes again a few seconds later. This happens for a few common reasons.
The corruption is at the file system or storage level, not just inside InnoDB.
If the underlying disk, RAID array, or storage volume has bad sectors, or if there was a partial write due to power failure, the actual .ibd files may be physically damaged. innodb_force_recovery can only work around the InnoDB engine related problems or InnoDB engine itself understands. It cannot fix a file that has pages that were never fully written to disk.
The redo log itself is unreadable.
Some of the recovery levels assume that MySQL can at least read the redo log to figure out what happened before the crash. If the redo log files are missing, truncated, or corrupted, the level 6 may not help, because the startup sequence still touches other structures that depend on log sequence numbers.
The data dictionary is broken.
InnoDB keeps its own internal dictionary of tables, indexes, and their structure. If this dictionary is damaged, MySQL may not even understand what tables exist, regardless of which recovery level you use. This is a different kind of problem than a broken table, and innodb_force_recovery is not designed to repair it.
Now, let us understand the step-by-step actions that we can take to bring the server online.
What are next steps
If innodb_force_recovery at level 6 still does not give you a working, readable database, here is a practical order of steps to follow.
Step 1: Take full backup of the database and its current state.
Before trying anything else, make a full copy of the data directory, including all .ibd, .frm, and log files, exactly as they are. Do this before any further attempts, because some recovery actions can overwrite information that a specialized tool might still be able to read.
Step 2: Check for an available and usable backup
Check if there is a recent physical or logical backup, along with binary logs, that can be restored and rolled forward. In many organizations, this is faster and safer than trying to repair a badly damaged data file, even if it means losing a small window of recent transactions.
Step 3: Try file-level and page-level inspection tools
Tools such as the InnoDB page inspection utilities, or innochecksum, can tell you which pages are actually damaged and which ones are still intact. This helps you understand the real scope of the damage before deciding on the next step.
Step 4: Consider a dedicated MySQL recovery tool
When none of the native recovery options work, a specialized MySQL repair tool like Stellar Repair for MySQL read directly from the damaged .ibd files and rebuild readable data. Stellar Repair for MySQL is built specifically for this kind of situation, where InnoDB's own recovery mechanism has already failed and we need to recover as much data as possible from the files directly. These tools work outside of the MySQL server process, which is helpful when the server itself is offline.
Step 5: Rebuild in a clean environment
Once you recover whatever data you can, whether through backups, page-level tools, or a repair utility, do not try to reuse the damaged data directory. Set up a fresh MySQL instance and import the recovered data into it. This avoids carrying forward any hidden corruption.
Step 6: Document what happened
Write down the sequence of events: what triggered the crash, which recovery levels were tried, what worked and what did not, and how the data was finally recovered. If next time something similar happens, this document becomes valuable.
Summary
The innodb_force_recovery is a genuinely useful tool, and it solves the majority of MySQL crash situations but it has limitations. It is important to understand its limitations early rather than spending hours performing the recovery steps which might not work in our scenario.
In this article, we learned about the limitations of the innodb_force_recovery parameter and what should we do in case we are unable to bring the database online using the level 6 of innodb_force_recovery parameter.
Top comments (0)