MySQL InnoDB Corruption Recover/Repair Guide

Understanding InnoDB Corruption

InnoDB is the default storage engine for MySQL, known for its reliability and ACID compliance. However, various factors can lead to database corruption, including hardware failures, power outages, software bugs, or even ransomware attacks.

When InnoDB corruption occurs, MySQL may fail to start or certain tables become inaccessible. The error messages in the MySQL error log typically indicate the type and location of corruption:

[ERROR] InnoDB: Database page corruption on disk or a failed file read of page
[ERROR] InnoDB: Page [page id: space=5, page number=127] log sequence number ...
[ERROR] InnoDB: is in the future!

Common Types of InnoDB Corruption

1. Page Corruption

Individual data pages within the tablespace become corrupted, often due to disk errors or incomplete writes. This is one of the most common types of corruption.

2. Log Sequence Number (LSN) Issues

The LSN in the data pages doesn't match the redo log, usually caused by incomplete crash recovery or file system corruption.

3. System Tablespace Corruption

Corruption in ibdata1 can affect the entire database instance, as this file contains crucial system tables and undo information.

4. Individual Tablespace (.ibd) Corruption

When using file-per-table tablespaces, individual .ibd files can become corrupted independently, affecting only specific tables.

Recovery Methods

Method 1: innodb_force_recovery

MySQL provides the innodb_force_recovery option to start the server with different levels of recovery. Add this to your my.cnf and restart MySQL:

[mysqld]
innodb_force_recovery = 1

Start with level 1 and increase if necessary. Levels range from 1 to 6, with higher levels being more aggressive but risking more data loss:

• 1 (SRV_FORCE_IGNORE_CORRUPT): Ignore corrupt pages
• 2 (SRV_FORCE_NO_BACKGROUND): No background operations
• 3 (SRV_FORCE_NO_TRX_UNDO): No transaction rollbacks
• 4 (SRV_FORCE_NO_IBUF_MERGE): No insert buffer merge
• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): No undo log scan
• 6 (SRV_FORCE_NO_LOG_REDO): No redo log roll-forward

Method 2: Using DBRECOVER for MySQL

When standard recovery methods fail, DBRECOVER can extract data directly from corrupted InnoDB files without requiring a running MySQL instance:

# Open the corrupted tablespace
DBRECOVER> open tablespace '/var/lib/mysql/mydb'

# Scan for recoverable tables
DBRECOVER> scan tables
[INFO] Found 45 tables
[INFO] 43 tables fully recoverable
[INFO] 2 tables partially recoverable

# Export data from a specific table
DBRECOVER> recover table orders
[INFO] Analyzing table structure...
[INFO] Found 523,847 rows
[INFO] 12 rows in corrupted pages (skipped)

DBRECOVER> export to 'orders_recovered.sql'
✓ Exported 523,835 rows successfully

Method 3: Point-in-Time Recovery from Binary Logs

If you have binary logging enabled, you can restore from a backup and replay binary logs to recover data up to a specific point:

mysqlbinlog --start-datetime="2023-12-01 00:00:00" \
            --stop-datetime="2023-12-06 10:00:00" \
            /var/lib/mysql/mysql-bin.* | mysql -u root -p

Prevention Best Practices

1. Regular Backups: Implement automated backup procedures using mysqldump or MySQL Enterprise Backup
2. Binary Logging: Enable binary logs for point-in-time recovery capability
3. Hardware Quality: Use enterprise-grade storage with battery-backed write cache
4. UPS Protection: Protect database servers from power failures
5. Monitoring: Monitor disk health and MySQL error logs for early warning signs
6. Test Recovery: Regularly test your backup and recovery procedures

When to Contact Professional Support

If you're facing data loss and standard recovery methods have failed, professional database recovery services can help. DBRECOVER offers:

• 24/7 emergency support for critical recovery situations
• Direct data extraction from corrupted InnoDB files
• Recovery from ransomware-encrypted databases
• Expert assistance with complex recovery scenarios

Contact us at [email protected] for immediate assistance.