MySQL Database Crashed? Complete InnoDB Recovery Tutorial

MySQL Database Crashed? Complete InnoDB Recovery Tutorial

Two weeks ago, an online shop in Causeway Bay called: their website wouldn't load, admin panel unreachable, customers couldn't order. Their outsourced IT said MySQL wouldn't start — error log read "InnoDB: Database page corruption on disk." The 40GB database was the shop's lifeline — member data, orders, inventory, everything. The owner's voice shook: "Today's the Double-11 warmup. Losing the shop means closing down." Here's what causes MySQL InnoDB crashes, how to rescue with innodb_force_recovery, and how to recover .frm/.ibd files.

What Is InnoDB? Why Does It Crash?

MySQL has several storage engines; InnoDB is the default (MyISAM is legacy, rarely used now). InnoDB features transactions, crash recovery, and foreign keys. But it has weaknesses:

• Sudden power loss: Most common. InnoDB relies on redo logs (ib_logfile) for crash recovery; normal restarts auto-replay the log. But if a data page was half-written during outage (torn page), next boot reports page corruption.
• Disk bad sectors: ibdata1 or .ibd files span many sectors; a bad sector on a page corrupts it. MySQL reports "Database page corruption" or "Checksum mismatch."
• Failed MySQL upgrades: Major upgrades (e.g., 5.7 to 8.0) going wrong leave the data dictionary inconsistent. 8.0's dictionary architecture differs greatly from 5.7 — messy to fix.
• Disk full: When the disk fills, InnoDB can't write redo logs or data pages and crashes directly. Freeing space afterward may not help — some pages are already half-written.
• Accidental deletion: Someone rm -rf's the wrong folder or hits DROP DATABASE by mistake. Not corruption but logical deletion — needs different recovery methods.

Step 1: Read the Error Log, Don't Guess

When MySQL crashes, don't reboot first — read the error log (usually /var/log/mysql/error.log or the .err file in datadir). Common errors:

• "InnoDB: Database page corruption on disk": Damaged data pages; note the page numbers (e.g., page 12345) — useful later.
• "InnoDB: Failed to read page": Unreadable — possibly disk issues; check S.M.A.R.T. first.
• "[ERROR] InnoDB: Log scan aborted": Redo log damaged; crash recovery impossible. Use innodb_force_recovery to skip.
• "Table 'xxx' doesn't exist in engine": .frm (structure) and .ibd (data) mismatch — usually upgrade or migration issues.

innodb_force_recovery: Try Levels 1 Through 6

MySQL's built-in lifesaver — add under [mysqld] in my.cnf:

innodb_force_recovery = 1

Start at 1, work up gradually — never jump straight to 6. What each level does:

1. Level 1: Skips parts of crash recovery checks. Gentlest — if damage is minor, this alone may boot.
2. Level 2: No background purge, read-only. Use when the purge thread crashed.
3. Level 3: No transaction rollback. Uncommitted transactions stay, but at least it boots.
4. Level 4: No insert buffer merge. Use when the insert buffer is damaged.
5. Level 5: No undo log rollback, read-only. The database is read-only at this point.
6. Level 6: Ultimate mode — no redo log, reads data pages directly. Skips even crash recovery; highest boot chance, but data may be inconsistent.

Correct usage: after setting, restart MySQL. If it boots, immediately dump with mysqldump:

mysqldump -u root -p --all-databases --single-transaction > /backup/full_dump.sql

Then build a fresh MySQL and restore the dump. Don't keep using the damaged datadir — it's untrustworthy.

Note: with innodb_force_recovery > 0, MySQL blocks writes (INSERT/UPDATE/DELETE error out). This is protection — don't run in this mode long-term.

The Causeway Bay Case: Level 4 Saved the Shop

The 40GB database had several page corruptions in the error log. Our process:

1. Full datadir backup: 40GB, ~25 minutes. All subsequent work on the backup.
2. Graduated force_recovery: Levels 1, 2, 3 all failed at the same point. Level 4 booted — insert buffer damage confirmed.
3. mysqldump export: Used --single-transaction + --skip-lock-tables, database by database. Two tables errored on some rows; skipped those first, then retried row by row.
4. Fresh machine restore: Built a new MySQL 8.0 on our test box, restored the dump. Ran CHECK TABLE to confirm.
5. Fill the gaps: ~200 missing rows in two order tables (last two days). The client had payment gateway records; we wrote a script pulling order data from Stripe/PayPal APIs to fill in.

One and a half days, $8,500 HKD. The owner immediately had us set up automated backups — daily mysqldump + hourly binlog, offsite. She sleeps much better now.

Recovering .frm and .ibd Files

Sometimes ibdata1 (system tablespace) is dead but per-table .ibd files survive. Use the discard/import tablespace method:

1. On a new MySQL, create empty tables with identical names and structures (find the CREATE TABLE statements; if lost, use mysqlfrm or ibd2sdi to reverse-engineer from the .ibd)
2. On the new table, run:

   ALTER TABLE your_table DISCARD TABLESPACE;

3. Copy the old .ibd into the new database directory, overwriting the empty .ibd
4. Then run:

   ALTER TABLE your_table IMPORT TABLESPACE;

5. MySQL checks whether the .ibd pages match the table structure; if yes, it mounts

Trickier in MySQL 8.0 because the data dictionary is no longer .frm files but inside mysql.ibd. If mysql.ibd is also dead, use ibd2sdi to extract SDI (serialized dictionary information) from each .ibd and manually rebuild structures. We have the tools and experience; DIY is error-prone.

Binlog: The Last Lifeline

If mysqldump fails or recent data is missing, check for binary logs. Binlogs record all write operations and can be replayed:

# See available binlogs
SHOW BINARY LOGS;
# Export a time range as SQL
mysqlbinlog --start-datetime="2026-10-01 00:00:00"             --stop-datetime="2026-10-06 12:00:00"             /var/log/mysql/mysql-bin.000123 > /backup/binlog_recover.sql

Prerequisite: binlog must be enabled and not expired (expire_logs_days). Many small firms never enable binlog — discovering this only during disaster. When we set up MySQL for clients, enabling binlog (7-day retention) is step one.

Preventing MySQL Crashes

1. Enable binlog: As above — lifesaving. Add to my.cnf:

   log_bin = /var/log/mysql/mysql-bin.log
   expire_logs_days = 7
   server-id = 1

2. Automated backups: Daily mysqldump (full) plus binlog (incremental). Write a cronjob; don't rely on manual. We have ready-made scripts for clients.
3. Monitor disk space: Disk-full is a common MySQL killer. Alert below 20%. Don't wait until writes fail.
4. Use good drives: Don't put databases on cheap desktop drives. Use enterprise SSDs or NAS drives with RAID. We've seen too many databases on single budget drives — tears when they die.
5. Regular CHECK TABLE: Monthly, to catch damage early:

   mysqlcheck -u root -p --all-databases --check

6. Back up before upgrading: Major upgrades (5.7→8.0) need full backups first. Failed upgrades can't roll back; backups remove the fear.

Pricing Reference

• innodb_force_recovery works, mysqldump export: $4,000-$7,000 HKD, 1 day
• Table-by-table handling, gap filling: $7,000-$12,000, 2-3 days
• Per-.ibd extraction, structure rebuild: $10,000-$20,000, 3-5 days depending on table count
• Physical disk damage: plus disk recovery fees, from $6,000

FAQ

1. Q: MySQL won't start. Can I delete ib_logfile0 and ib_logfile1 to let it rebuild?

A: Absolutely not! They may contain transactions not yet written to data files — deleting loses them for real. Correct approach: back up the entire datadir first, then test innodb_force_recovery on the backup. Only rebuild logs after confirming no important uncommitted transactions remain.

2. Q: MyISAM vs InnoDB — which is easier to recover?

A: MyISAM is structurally simpler (.frm + .MYD + .MYI); myisamchk can fix it, even novices manage. But MyISAM has no transactions — crashes corrupt easily. InnoDB is more complex but has crash recovery; minor damage self-heals. Overall, everything should use InnoDB now — stop using MyISAM.

3. Q: My database is 200GB. Won't mysqldump be slow?

A: Yes. 200GB may take hours to dump, longer to restore. Speedups: --single-transaction avoids locking, don't do anything else during dump, increase innodb_buffer_pool_size for restore. For truly large DBs, consider Percona XtraBackup for physical backups — much faster. We have large-database experience and can design backup strategies.

4. Q: Cloud RDS (AWS RDS, Alibaba Cloud) means no worries, right?

A: Not quite. RDS handles hardware and basic backups, but can't save you from logical damage (accidental deletes, buggy code writing bad data). And RDS auto-backup retention is limited (usually 7-35 days). Critical data still needs your own offsite backups — don't rely entirely on cloud.

Summary

MySQL crashes aren't the end: remember back up datadir → read error log → graduated innodb_force_recovery → mysqldump export → restore on fresh machine. Don't delete log files, don't test on the original, don't run long-term in force_recovery mode. If you're stuck, WhatsApp +852 6558 6806 — we've recovered multi-GB MySQL databases, free initial assessment.

Learn more in our data backup services or 3-2-1 backup rule — prevention beats cure.