How to Fix Corrupted Tables in MySQL

Sometimes MySQL tables corrupt, which means that some error has occurred and the data within them can’t be read. Attempts to read from a corrupted table usually leads to the server crashing.

These are some of the most common reasons of corrupted tables:

• The hardware fails;
• The MySQL server stops unexpectedly
• The machine stops unexpectedly;
• There’s a software bug in the MySQL code;
• An external program modifies a table that is being modified by the server at the time.

If you think that one of the tables is corrupted, you should make a backup of your data before troubleshooting or attempting to fix it. This will ensure the risk of data loss is smaller.

To start, stop the MySQL service:

$
sudo systemctl stop mysql

After, copy all of your data into a new backup directory. If you’re using Ubuntu system, the default data directory is /var/lib/mysql/:

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

After you’ve made the backup, it’s time to start investigating if the table is corrupted. If the table is using the MyISAM storage engine, you can check if it’s corrupted by running a CHECK TABLE statement from the MySQL prompt:

mysql>
CHECK TABLE table_name;

You’ll see a message informing if it’s corrupted or not. If the MyISAM table is corrupted, it can be repaired by the REPAIR TABLE statement:

mysql>
REPAIR TABLE table_name;

If the repair is successful, you’ll see a message like the following in your output:

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

But if it’s still corrupted, the MySQL documentation suggests some alternative methods to repair corrupted tables.

In case the corrupted table uses the InnoDB storage engine, then the process for repairing it will be different. InnoDB features automated corruption checking and repair operations. It checks for corrupted pages by performing checksums on the pages it reads, and if there is a discrepancy it will stop the MySQL server automatically.

It’s not often a need to repair InnoDB tables, as it features a crash recovery mechanism that can resolve most issues when the server is restarted. However, if you find a situation where you need to rebuild a corrupted InnoDB table, the MySQL documentation recommends the use of Dump and Reload method.

Try restarting the MySQL service to see if it will allow your access to the server:

$
sudo systemctl restart mysql

If the server is still crashed or otherwise inaccessible, then it can be helpful to enable InnoDB’s force_recovery option. For this, you can edit the ```mysqld.cnf```` file:

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

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

After that, save and close the file. Then you can restart the MySQL service. If you can access the corrupted table, use the mysqldumputility to dump your table data to a new file. You can name it as you like, but in this guide we’ll name it out.sql:

$
mysqldump database_name  table_name > out.sql

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

$
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

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