How to Repair a Corrupted MySQL Table

Database corruption can lead to application downtime and data loss. This guide provides a straightforward, step-by-step approach to safely checking and repairing corrupted tables in a MySQL database. You will learn how to diagnose table issues, safely back up your data before attempting fixes, and apply the correct recovery methods for both MyISAM and InnoDB storage engines.

Step 1: Back Up Your Database

Before executing any check or repair commands, you must back up your database. Attempting to repair a corrupted table can sometimes write over damaged sectors and cause permanent data loss.

To create a quick backup using the command line, run:

mysqldump -u [username] -p [database_name] > [backup_file_name].sql

If a specific table is corrupted and preventing a full dump, copy the raw database files from your MySQL data directory (usually /var/lib/mysql/) to a secure backup location while the MySQL service is stopped:

sudo systemctl stop mysql
cp -r /var/lib/mysql /path/to/backup/
sudo systemctl start mysql

Step 2: Check the Table for Errors

To confirm corruption, log into the MySQL shell and run the CHECK TABLE command. This command inspects the table’s structure and data for inconsistencies.

USE my_database;
CHECK TABLE my_table;

The output will display a status message. If the status is OK, the table is healthy. If it returns error or corrupt, you must proceed with a repair.

Step 3: Repairing MyISAM Tables

If your corrupted table uses the MyISAM storage engine, you can repair it directly from the MySQL command line or via the terminal.

Method A: Using SQL Commands

Run the following query in your MySQL client:

REPAIR TABLE my_table;

For severe corruption, you can use the extended repair option:

REPAIR TABLE my_table QUICK;

Method B: Using the myisamchk Utility

If the MySQL server fails to start or run queries, stop the MySQL service and use the myisamchk command-line tool on the physical table files:

sudo systemctl stop mysql
myisamchk -r /var/lib/mysql/my_database/my_table.MYI
sudo systemctl start mysql

Step 4: Repairing InnoDB Tables

The InnoDB engine does not support the REPAIR TABLE command. InnoDB has built-in auto-recovery mechanisms, but if it crashes and fails to start, you must use the innodb_force_recovery mode to rescue your data.

Method A: Run Optimize Table

For minor logical issues while the server is still running, rebuilding the table often resolves the corruption:

OPTIMIZE TABLE my_table;

Method B: InnoDB Force Recovery (For Server Crashes)

If MySQL refuses to start due to InnoDB corruption, follow these steps:

Open your MySQL configuration file (usually /etc/mysql/my.cnf or /etc/my.cnf).
Add the following line to the [mysqld] section:
```
[mysqld]
innodb_force_recovery = 1
```
(Note: Start with 1 and only increase up to 6 incrementally if the server fails to start. Modes 4 and above can permanently corrupt data, so use them with extreme caution).
Start the MySQL service:
```
sudo systemctl start mysql
```

Export the corrupted table’s data using mysqldump:

mysqldump -u [username] -p [database_name] [corrupted_table] > table_backup.sql

Log into MySQL, drop the corrupted table, and remove the innodb_force_recovery line from your configuration file.
Restart MySQL normally and restore the table from your dump file:
```
mysql -u [username] -p [database_name] < table_backup.sql
```