How to Recover MySQL InnoDB Corrupted Tables

In this tutorial, you'll learn about How to Recover MySQL InnoDB Corrupted Tables. We cover key concepts, practical examples, and best practices.

The Problem

Your MySQL server crashes or returns errors like Table 'mytable' is marked as crashed and should be repaired or InnoDB: Database page corruption on disk for InnoDB tables.

Quick Fix

Check Table Status with CHECK TABLE

mysql -u root -e "CHECK TABLE mydatabase.mytable;"
# Table                  Op      Msg_type   Msg_text
# mydatabase.mytable     check   Error      Table is marked as crashed

CHECK TABLE reports the table's health. For InnoDB, it also reports index page corruption that cannot be repaired with REPAIR TABLE.

Dump the Corrupted Table with --force

mysqldump -u root --force --skip-opt mydatabase mytable > dump.sql
# Warning: Could not read rows from table mytable
# -- Dump completed with warnings
mysqldump -u root --force --skip-opt mydatabase > full_dump.sql
# -- Dump completed with warnings

Use --force to continue dumping even when errors occur. --skip-opt avoids advanced options that may fail on corrupt tables. For multiple corrupt tables, dump the whole database.

Recover with innodb_force_recovery

sudo sed -i '/\[mysqld\]/a innodb_force_recovery = 1' /etc/mysql/my.cnf
sudo systemctl restart mysql
# (MySQL starts in recovery mode)
mysqldump -u root mydatabase > recovery_dump.sql
sudo sed -i '/innodb_force_recovery/d' /etc/mysql/my.cnf
sudo systemctl restart mysql
# (MySQL restarts normally)
mysql -u root mydatabase < recovery_dump.sql

Set innodb_force_recovery from 1 to 6 (increasing aggressiveness) to start MySQL despite corruption. Dump the data, reset the config, recreate the database, and restore.

Recreate the Table via .frm Discovery

mysql -u root -e "
CREATE TABLE mydatabase.mytable_new LIKE mydatabase.mytable;
ALTER TABLE mydatabase.mytable_new DISCARD TABLESPACE;
"
# Copy the old .ibd file to the new table location
# ALTER TABLE mydatabase.mytable_new IMPORT TABLESPACE;

If the table structure is intact but data pages are corrupt, creating a fresh table and importing the old tablespace can recover readable pages.

Use mysqlcheck for Automated Repair

mysqlcheck -u root --auto-repair --all-databases
# mydatabase.mytable                         OK
# mydatabase.mytable2                        warning : Table is marked as crashed
# mydatabase.mytable2                        repaired

mysqlcheck --auto-repair attempts to repair all damaged tables across databases. For InnoDB tables, this may not work perfectly and mysqldump recovery is more reliable, but it is worth trying first.

Additional Troubleshooting

# Check the error message and stack trace for more context
echo "Review the full error output to identify the root cause"

If the above steps do not resolve the issue, examine the complete error message and stack trace. Often the key detail is in the middle of the traceback rather than the final line. Search for the error message in the project documentation or issue tracker for additional solutions.

Prevention

• Run CHECK TABLE regularly as part of database maintenance
• Enable innodb_checksum_algorithm=crc32 to detect corrupt pages on read
• Use a replica for read traffic and take regular backups with mysqldump --all-databases
• Monitor MySQL error logs for InnoDB: Database page corruption messages