MySQL InnoDB Crash Recovery: Diagnosis and Emergency Procedures

InnoDB is designed for crash safety, but recovery after an unexpected shutdown still requires careful handling. Here is what happens during InnoDB crash recovery and how to handle edge cases.

How InnoDB Crash Recovery Works

InnoDB uses a write-ahead log (redo log) to replay committed transactions after a crash:

1. MySQL starts and detects the redo log is dirty
2. InnoDB replays all committed transactions from the redo log
3. Uncommitted transactions are rolled back (undo log)
4. The database opens for normal operation

Checking Recovery Status

-- After startup, check for recovery messages
SHOW ENGINE INNODB STATUS\G

-- Look for: '...started; log sequence number...'
-- and: 'Transaction list consists of X transactions'

innodb_force_recovery Levels

When the database will not start, use innodb_force_recovery in my.cnf:

[mysqld]
innodb_force_recovery = 1   # Start even if redo log is corrupt

Recovery levels (use the lowest that works):

• 1: Skip corrupt pages, continue startup
• 2: Prevent background thread from running
• 3: Do not run transaction rollbacks
• 4: Disable insert buffer merge
• 5: Do not look at undo logs (skip incomplete transactions)
• 6: Skip corrupt pages even if detected

Emergency Dump Procedure

# With force_recovery set, dump all data
mysqldump --single-transaction --all-databases > emergency_dump.sql

# Check for errors in dump
grep -i error emergency_dump.sql

# Restore to fresh instance
mysql -u root -p < emergency_dump.sql

Preventing Future Crashes

[mysqld]
innodb_flush_log_at_trx_commit = 1   # ACID compliance
sync_binlog = 1                       # Sync binlog on every commit
innodb_doublewrite = ON               # Extra protection against partial writes