How to Resolve MySQL Data Page Corruption: Step-by-Step Recovery Strategies

​Background​

In April 2023, a MySQL instance crashed unexpectedly, logging critical errors about index page corruption. Though the instance recovered automatically, a second crash in June prompted urgent investigation. Error logs revealed:

InnoDB: Apparent corruption of an index page [page id: space=3859, page number=842530]
InnoDB: Assertion failure in thread ... InnoDB: We intentionally crash the server...

‍

This article outlines strategies to identify and fix data page corruption based on instance health.

‍

Problem Analysis

​Data page corruption typically triggers error logs with specific space ID and page number details. Depending on instance status, recovery approaches differ:

Scenario 1: Instance Runs Normally​

Use system tables to locate corrupted pages:

1. ​Check Buffer Pool Metadata:

SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 
WHERE PAGE_NUMBER = <corrupted_page_number>;

‍

Note: Avoid running this on production due to performance overhead.

2. ​Identify Affected Index/Table:

Query INNODB_SYS_TABLES and INNODB_SYS_INDEXES to determine if the corrupted page belongs to a primary or secondary index. Example:

SELECT b.INDEX_ID, a.NAME AS Table_Name, b.NAME AS Index_Name 
FROM INNODB_SYS_TABLES a 
JOIN INNODB_SYS_INDEXES b ON a.SPACE = b.SPACE 
WHERE a.SPACE = <space_id> AND b.INDEX_ID = <index_id>;

‍

• Primary Index Damage: May cause data loss (InnoDB stores data in primary keys).
• ​Secondary Index Damage: Safe to drop and rebuild.

‍

Scenario 2: Instance Fails to Start​

​Method 1: Force Recovery with innodb_force_recovery

Incrementally raise innodb_force_recovery (from 1 to 6) in my.cnf:

[mysqld]
innodb_force_recovery = 1  # Start with lowest value

‍

• Values 1–3: Skip corrupted pages during recovery.
• ​Values 4–6: Risk permanent data loss; use only for backup extraction.

​Method 2: Repair with inno_space Tool​

Use the inno_space utility to bypass damaged pages:

# Remove corrupted page segments
./inno -f /path/to/t_user.ibd -d <page_number>

# Recompute checksums
./inno -f /path/to/t_user.ibd -u <page_number>

‍

After repair, restart MySQL and export data for safekeeping.

Key Solutions​

1. ​Preemptive Measures:
   • Regularly back up data using mysqldump or Percona XtraBackup.
   • Enable innodb_checksum_algorithm to detect corruption early.
2. ​Recovery Workflow:
   • ​Operational Instances: Query metadata to isolate corruption.
   • ​Crashed Instances: Use innodb_force_recovery or inno_space to salvage data.
3. ​Post-Recovery:
   • Restore from backups if corruption persists.
   • Rebuild secondary indexes to ensure integrity.

​Final Note​

While tools like innodb_force_recovery and inno_space aid recovery, ​no method guarantees 100% data integrity. Prioritize regular backups and operational hygiene to mitigate risks.