Repairing Corrupted MySQL Tables

Occasionally, MySQL tables can become corrupted, meaning that an error has occurred and the data held within them is unreadable. Attempts to read from a corrupted table will usually lead to the server crashing.

Common Causes of Corrupted Tables

• The MySQL server stops in middle of a write.
• An external program modifies a table that’s simultaneously being modified by the server.
• The machine is shut down unexpectedly.
• The computer hardware fails.
• There’s a software bug somewhere in the MySQL code.

Backup Before Troubleshooting

If you suspect that one of your tables has been corrupted, you should make a backup of your data directory before troubleshooting or attempting to fix the table. This will help to minimize the risk of data loss.

Stopping the MySQL Service

First, stop the MySQL service:

sudo systemctl stop mysql

Note: On some platforms such as Rocky Linux, the MySQL service may be called mysqld instead.

Creating a Backup

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

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

Checking for Corruption

After making the backup, you’re ready to begin investigating whether the table is in fact corrupted. If the table uses the MyISAM storage engine, you can check whether it’s corrupted by restarting MySQL and running a CHECK TABLE statement from the MySQL prompt:

sudo systemctl start mysql
CHECK TABLE table_name;

Repairing MyISAM Tables

A message will appear in this statement’s output letting you know whether or not it’s corrupted. If the MyISAM table is indeed corrupted, it can usually be repaired by issuing a REPAIR TABLE statement:

REPAIR TABLE table_name;

Output Example

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

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

Handling InnoDB Tables

If the corrupted table uses the InnoDB storage engine, the process for repairing it will be different. InnoDB is the default storage engine in MySQL as of version 8.0 and features automated corruption checking and repair operations. However, if you do encounter a situation where you need to rebuild a corrupted InnoDB table, use the “Dump and Reload” method.

Restarting MySQL

Try restarting the MySQL service:

sudo systemctl restart mysql

Using Force Recovery

If the server remains inaccessible, enable InnoDB’s force_recovery option by editing the mysqld.cnf file:

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

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

[mysqld]
innodb_force_recovery=1

Save the file and restart the MySQL service.

sudo systemctl restart mysql

Dumping and Reloading the Table

If you can successfully access the corrupted table, use the mysqldump utility to dump your table data to a new file. You can name this file whatever you like; in this example, we’ll name it out.sql:

mysqldump database_name table_name > out.sql

Then, drop the table from the database. To avoid having to reopen the MySQL prompt, you can use the following syntax:

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

Following this, restore the table with the dump file you just created:

mysql -u user -p < out.sql

Conclusion

Note that the InnoDB storage engine is generally more fault-tolerant than the older MyISAM engine. Tables using InnoDB can still be corrupted, but because of its auto-recovery features, the risk of table corruption and crashes is markedly lower.