MySQL tables can sometimes get corrupted. It means that an error can occur and the data held within them can become unreadable. So, the attempts to read from a corrupted table will generally result into the server crashing.
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.
Also Read:
