Ideas To Fix Corrupted Tables in MySQL

Tables in MySQL can occasionally become corrupted. It implies that a mistake might happen and render the information contained in them illegible. Therefore, attempting to read data from a damaged database would typically cause the server to fail.

Below are common causes of corrupted tables:

• The MySQL server stops in between of a write.
• Similar table that is modified by the server is modified by an external program.
• The machine gets shut down without giving any warning.
• The computer hardware fails.
• The MySQL code consists of a software bug.

In case you detect that one of your tables has been corrupted, take a backup of your data directory prior to troubleshooting or attempting to fix the table. This will help to reduce the risk of data loss.

First, stop the MySQL service:

$ sudo systemctl stop mysql

After this, copy your entire data into a new backup directory. The data directory is /var/lib/mysql/ is the default one on Ubuntu systems:

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

Once the backup is taken, you can begin investigating if the table is in fact corrupted. In case the table uses the MyISAM storage engine, you can verify if it’s corrupted by running a CHECK TABLE statement from the MySQL prompt:

mysql> CHECK TABLE table_name;

A message will get displayed after using the above command which will help you know whether or not it’s corrupted. If the MyISAM table is actually corrupted, it can basically be repaired by issuing a REPAIR TABLE statement:

mysql> REPAIR TABLE table_name;

If you assume that the corrupted table was repaired successfully, you will get the below output:

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

If the table corruption isn’t resolved yet, you can check the alternative methods for repairing the corrupted tables in the MySQL documentation.

Alternatively, if the corrupted table uses the InnoDB storage engine, then it will have a different process for repairing. InnoDB is the default storage engine in MySQL since the version 5.5, and it offers automated corruption checking and repair operations. InnoDB finds corrupted pages by performing checksums on every page it reads, and if there is a checksum discrepancy it will automatically stop the MySQL server.

You rarely need to repair InnoDB tables, as InnoDB offers a crash recovery process that can resolve most issues when the server restarts. But, if you face a situation where you need to rebuild a corrupted InnoDB table, use the “Dump and Reload” method recommended by the MySQL documentation. This includes regaining access to the corrupted table by using the mysqldump utility for creating a logical backup of the table. This will keep the table structure and the data within it, and then reload the table back into the database.

Keeping this in mind, try to restart the MySQL service to see if it allows you access to the server:

$ sudo systemctl restart mysql

In case the server remains crashed or otherwise inaccessible, then it is better to enable InnoDB’s force_recovery option. This can be done by editing the mysqld.cnf file:

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

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

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

[mysqld]

. . . innodb_force_recovery=1

Now, save and close the file, and then try to restart the MySQL service again. If you are successfully able to access the corrupted table, use the mysqldump utility to dump your table data to a new file. This file can be named as per your preference, but here it’s named as out.sql:

$ mysqldump database_name table_name > out.sql

Drop the table from the database. To avoid the need to reopen the MySQL prompt, use the following syntax:

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

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

$ mysql -u user -p < out.sql

Remember that the InnoDB storage engine is generally highly fault-tolerant as compared to the older MyISAM engine. Tables using InnoDB can still be corrupted, but due to its auto-recovery features, the risk of table corruption and crashes gets much lowered.