MySQL InnoDB won't start after crash — fix the recovery loop

If you're reading this, your MySQL server probably crashed hard and now InnoDB won't start. The logs show something about a page with a log sequence number in the future. It's a nasty situation — I've been there. Let's get it fixed.

First, try the real fix

Stop MySQL if it's running. Then edit your MySQL config file (my.cnf on Linux, my.ini on Windows) and add this line under the [mysqld] section:

innodb_force_recovery = 1

Restart MySQL. If it starts, run a full mysqldump of your databases immediately. Then drop the innodb_force_recovery line, re-create the database from the dump, and you're done.

If it still doesn't start after setting recovery to 1, try innodb_force_recovery = 2, then 3, 4, 5, 6 in order. Stop at the lowest value that lets MySQL start. Each level removes more safety checks:

• 1 — skips checking corrupt pages but still tries to roll back transactions. Good for most cases.
• 2 — also blocks rollback of uncommitted transactions. Faster start but may leave data inconsistent.
• 3 — skips undo log operations too. You'll lose any uncommitted changes.
• 4 — ignores data dictionary corruption. Might let you start when tables are missing.
• 5 — skips all undo logs completely. Data might be a mess after this.
• 6 — bypasses redo log entirely. Last resort — your table data may be garbled.

Once you can connect, export everything with mysqldump, rebuild the database from scratch, and re-import. Never run production with innodb_force_recovery on — it's a one-time export tool, not a permanent setting.

Why this works

Here's what's actually happening. When MySQL crashes, InnoDB's crash recovery tries to replay the redo log to bring the database to a consistent state. The redo log contains "log sequence numbers" (LSNs) that mark each change. After a crash, InnoDB reads the last known good LSN from the checkpoint, then replays all newer entries.

The problem: if a log entry got half-written when the crash happened, or the page data on disk is newer than the log (that "future" LSN error), recovery can't figure out what's real. It loops trying to reconcile impossible states. innodb_force_recovery tells InnoDB to skip the checks it can't pass. It's like telling the database "just start up, I'll clean the mess myself."

The reason step 3 works is that level 3 skips both rollback and undo processing. Most corruption lives in the undo logs — transactions that were in progress when the server died. By skipping them, you get to the point where you can dump the surviving data.

When the fix above doesn't work

Sometimes even innodb_force_recovery=6 doesn't start MySQL. That usually means the InnoDB system tablespace (ibdata1) is trashed — not just a corrupted page or log. Here are two real-world scenarios and what to do:

Scenario 1: ibdata1 is corrupt, but you have backups of individual databases

If you have .ibd files for your InnoDB tables but the system tablespace is gone, you can't just drop them in. The fix is to create a fresh MySQL install with a new ibdata1, then use ALTER TABLE ... IMPORT TABLESPACE for each table. You need the .ibd file AND the .cfg file (or .frm if using old MySQL). Here's the procedure:

-- On the new MySQL instance:
CREATE DATABASE mydb;
CREATE TABLE mytable (... same schema as original ...);
ALTER TABLE mydb.mytable DISCARD TABLESPACE;
-- Copy the old .ibd file to the database directory
ALTER TABLE mydb.mytable IMPORT TABLESPACE;

This only works if the corruption is in ibdata1, not in the table's own .ibd file. Check MySQL error log for which file it blames.

Scenario 2: No backups, ibdata1 is corrupted beyond recovery

This is the worst case. Your only option is to recover what you can from the raw .ibd files using a tool like undrop-for-innodb or recover-innodb. Both scan the binary data for row fragments. Expect to recover maybe 60-80% of your data, with some rows missing or garbled. I've used recover-innodb with some success — it's ugly but it's all you've got.

Preventing this from happening again

Three things make the biggest difference:

1. Set innodb_flush_log_at_trx_commit = 1. Yes, it slows writes. But it guarantees the redo log is flushed to disk after every transaction. The default on some systems is 0 or 2, which sacrifices durability for speed. If you crash with those settings, corruption is practically guaranteed.
2. Use a UPS and a filesystem that handles crashes well. XFS and ext4 with ordered data mode are solid. Avoid ZFS with compression on old kernels — I've seen it corrupt MySQL redo logs under load.
3. Set innodb_buffer_pool_size to no more than 70% of available RAM. If the OS runs out of memory and kills MySQL, it's the same as a hard crash. Stay below the line.

Also, take regular mysqldump backups — not just file-level snapshots. A file-level backup of a crashed database gives you a crashed copy. Dumps give you clean SQL.

That's it. Recovery mode 1 or 2 will get most people out of this. If it doesn't, the export-and-rebuild approach is your only clean option. Don't waste time trying to repair InnoDB files in place — it rarely works and you'll make things worse.