How to Fix Corrupted InnoDB Tablespace in MySQL

MySQL database corruption can halt your applications and cause data loss. This guide provides a straightforward, step-by-step approach to diagnosing InnoDB tablespace corruption using MySQL error logs and resolving the issue safely using the innodb_force_recovery option, logical backups, and database restoration.

Diagnosing InnoDB Tablespace Corruption

Before attempting any fixes, you must confirm that the tablespace is actually corrupted.

Inspect the MySQL Error Log: Open your MySQL error log (usually located at /var/log/mysql/error.log or /var/log/mysqld.log). Look for specific error patterns such as:
- InnoDB: Database page corruption on disk or a failed read of file
- Assertion failure in InnoDB code
- Signal 11 (Segmentation fault) crashes when accessing specific tables
Run CHECK TABLE: If the MySQL service is still running, log into the MySQL command line and run the check command on the suspected table:
```
CHECK TABLE your_table_name;
```
If the output returns status “Corrupted” or “error”, you must proceed with the recovery process.

Step-by-Step Fix for InnoDB Corruption

Step 1: Stop the MySQL Service and Take a Physical Backup

Never attempt to fix database corruption on a live, active data directory without a backup. Stop the MySQL service and copy the entire data directory to a safe location.

# Stop MySQL
sudo systemctl stop mysql

# Backup the data directory (typically /var/lib/mysql)
sudo cp -R /var/lib/mysql /var/lib/mysql_backup

Step 2: Enable InnoDB Force Recovery

InnoDB has a read-only recovery mode that allows you to start the server and dump your data even if a tablespace is damaged.

Open your MySQL configuration file (my.cnf or mysqld.cnf, usually in /etc/mysql/ or /etc/).
Locate the [mysqld] section and add the following line:
```
[mysqld]
innodb_force_recovery = 1
```
Start the MySQL service:
```
sudo systemctl start mysql
```

Note: If MySQL fails to start, stop the service, increase the innodb_force_recovery value to 2, and try starting it again. You can increase this value up to 6 if necessary, but values higher than 4 carry a risk of permanent data loss. Only use the lowest value required to get the service running.

Step 3: Export/Dump the Corrupt Table or Database

Once MySQL starts in recovery mode, immediately export your data using mysqldump. Databases are read-only in this mode, so you cannot run INSERT or UPDATE statements.

mysqldump -u root -p --all-databases > all_databases_dump.sql

If only a specific database or table is corrupted, dump only that target to save time:

mysqldump -u root -p database_name table_name > table_backup.sql

Step 4: Drop the Corrupted Database or Tables

With your data safely exported to a .sql file, you need to remove the corrupted tablespace from the active MySQL system.

Stop the MySQL service:
```
sudo systemctl stop mysql
```
Open your configuration file, comment out or delete the recovery line:
```
# innodb_force_recovery = 1
```
Start MySQL in normal mode:
```
sudo systemctl start mysql
```
Log into MySQL and drop the corrupted database or table:
```
DROP DATABASE database_name;
```

Step 5: Restore the Data from the Dump File

Recreate the database and import the clean backup file you generated in Step 3.

CREATE DATABASE database_name;

Exit the MySQL shell and run the restore command:

mysql -u root -p database_name < table_backup.sql

Your InnoDB tablespace is now rebuilt, and your data is restored to a healthy, uncorrupted state.