How to Repair Corrupted Tables in MySQL

Introduction

Sometimes, MySQL tables can become corrupted, meaning that an error has occurred and the data stored in them cannot be read. Attempting to read from a corrupted table usually results in the server crashing.

Some common reasons for table corruption include:

MySQL server stops in the middle of a write.
An external application modifies a table that is being modified simultaneously by the server.
The device turns off unexpectedly.
Computer hardware fails.
There is a software bug somewhere in the MySQL code.

If you suspect that one of your tables is corrupted, you should back up your data list before troubleshooting or attempting to repair the table. This helps reduce the risk of data loss.

First, stop the MySQL service:

sudo systemctl stop mysql

Then copy all your data to a new backup folder. On Ubuntu systems, the default data directory is /var/lib/mysql/:

cp -r /var/lib/mysql /var/lib/mysql_bkp

Once you have the backup, you are ready to check if the table is actually corrupted. If the table uses the MyISAM storage engine, you can check for corruption by restarting MySQL and running the CHECK TABLE statement from the MySQL prompt:

sudo systemctl start mysql

CHECK TABLE table_name;

A message will appear in the output of this statement letting you know if it is corrupt. If the MyISAM table is indeed corrupt, it can usually be repaired by issuing the REPAIR TABLE statement:

REPAIR TABLE table_name;

Assuming the repair was successful, you will see a message like this in your output:

Output
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status | OK |
+--------------------------+--------+----------+----------+

If the table is still corrupt, the MySQL documentation suggests a few alternative methods for repairing corrupted tables.

On the other hand, if the corrupted table uses the InnoDB storage engine, the repair process is different. InnoDB is the default storage engine in MySQL since version 8.0 and has automatic corruption checking and repair. InnoDB checks for corrupted pages by performing checksums on every page it reads, and if it finds a collation discrepancy, it automatically stops the MySQL server.

It is rare to need to repair InnoDB tables, as InnoDB has a crash recovery mechanism that can resolve most issues when the server is restarted. However, if you encounter a situation where you need to rebuild a corrupted InnoDB table, the MySQL documentation recommends using the Dump and Reload method. This involves restoring access to the corrupted table, using the mysqldump utility to create a logical backup of the table that preserves the table structure and the data within it, and then reloading the table into the database.

With this in mind, restart the MySQL service to see if doing so allows you to access the server:

sudo systemctl restart mysql

If the server is down or inaccessible, it may be useful to enable the InnoDB force_recovery option. You can do this by editing the mysqld.cnf file. On Ubuntu and Debian systems, this file is usually located at etc/mysql. On Red Hat and Rocky systems, this file is usually located at /etc/my.cnf.d.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the [mysqld] section, add the following line:

. . .
[mysqld]
. . .
innodb_force_recovery=1

Save and close the file, and then restart the MySQL service. If you can successfully access the corrupted table, use the mysqldump utility to dump your table data into a new file. You can name this file whatever you like, but here we will name it dump.sql:

mysqldump database_name table_name > out.sql

Then drop the table from the database. To avoid reopening the MySQL command, you can use the following command:

mysql -u user -p --execute="DROP TABLE database_name.table_name"

After this, restore the table with the dump file you created:

mysql -u user -p < out.sql

Note that the InnoDB storage engine is generally more fault-tolerant than the older MyISAM engine. Tables using InnoDB can still become corrupted, but due to its self-healing features, the risk of table corruption and failure is significantly lower.