How to Repair Corrupt MySQL Table?

March 20, 2024

Reading Time: 4 minutes

MySQL database contains tables that store all the information. Sometimes, these tables get corrupted due to issues, such as insufficient storage space on the hard disk, bad sectors on the disk, abrupt system shutdown, and software issues. Besides this, bugs in MySQL code and malware infection in the system hosting the database are other factors that may cause table corruption. When the MySQL table gets corrupted, you may fail to execute queries relying on the affected table and get random errors. Sometimes, even your database becomes inaccessible, thus disrupting your workflow and productivity. In this article, we will learn how to repair corrupt MySQL database tables. But before moving to the solutions, let’s first see how to check corruption in MySQL tables.

Table of Contents

How to Detect Corruption in MySQL Tables?

You can use the CHECK TABLE and mysqlcheck commands to check MySQL tables for corruption. Let’s learn more about these commands:

CHECK TABLE Statement

The CHECK TABLE command in MySQL is used to check tables for errors. It can check table views and their references. This command can check tables created in InnoDB and MyISAM storage engines. If the CHECK TABLE command detects any issue, it shows an error with the table name marked as corrupted and prevents you from accessing that table. However, if it does not find any issue with the table, it may remove the corruption mark.

Here’ how to use the CHECK TABLE command in MySQL:

CHECK TABLE tbl_name [, tbl_name] … [option] …

option: {

FOR UPGRADE

| QUICK

| FAST

| MEDIUM

| EXTENDED

| CHANGED

}

Note:

You must have proper permissions to use the CHECK TABLE command.
To check the partitioned tables using the CHECK TABLE command, you need to add an ALTER TABLE statement.

Myisamchk Command

MySQL provides a built-in utility – myisamchk, to check, repair, and optimize the MyISAM tables. Here’s an example of the myisamchk statement to check the tables:

myisamchk [options] tbl_name …

tbl_name is the table name

Options parameter in the command specifies what you want the utility to do.

For example, if you specify —check-only-changed,-C, it will check only the tables that have changed since the last check.

If you do not mention any options in the command, it simply checks the table as the default operation.

myisamchk TABLE

Methods to Repair Corrupt MySQL Table

Once you have identified the corrupted table in MySQL, follow the below methods to repairing it.

1 – Use the CHECK TABLE Command

The CHECK TABLE command also repairs corrupt tables in the MySQL database. To repair the corrupt table, open the command-line terminal on the system hosting the MySQL Server and execute the below command.

mysqlcheck –r database_name table_name

If the CHECK TABLE command fails to repair the corrupt table, you can try rebuilding the table. You can use mysqldump file to rebuild the table. Here’s the syntax to create the dump file and to reload the file:

mysqldump db_name t1 > dump.sql

mysql db_name < dump.sql

Alternatively, you can use the REPAIR TABLE command to rebuild the table. This command supports MyISAM, ARCHIVE, and CSV tables. Here’s how to use REPAIR TABLE command to repair corrupt MyISAM table:

REPAIR TABLE table name;

2 – Use Myisamchk Command

To repair MyISAM tables, you can use myisamchk command. To use this command, you need to first stop the MySQL Server. When the server stops, execute the below command:

myisamchk –recover TABLE

After that, restart the MySQL Server.

3 – Use a Third-Party MySQL Database Repair Tool

If the above repair options fail to repair corrupt MySQL database table, then you can use an advanced MySQL database repair tool, like Stellar Repair for MySQL. This tool can repair even severely corrupted MySQL tables of both InnoDB and MyISAM storage engines and save them in a new database file. It can also recover all the components from the corrupt database, while preserving the original formatting. It supports both Linux and Windows operating systems. It supports MySQL version 8.x, 6.x, 5.x, 4.x, and 3.

Steps to use Stellar Repair for MySQL to repair corrupt table in MySQL database:

Download, install, and launch the Stellar Repair for MySQL software.

In the Select Data Folder dialog box, click Browse to select the data folder.

Select the data folder where the MySQL database files are located.
Select the database and the database version you are using and click Repair.

On the Repair Complete message box, click OK.

The tool will display all the repaired tables. Select the tables you want to recover and click Save on the File menu.

Note: You require the license of the Stellar Repair for MySQL to save the repaired file.

In the Save Database window, under Save As, select the required file format to save the tables. Provide the required credentials and then click Save.
When the process is completed, a Save Complete message is displayed.

Conclusion

You can use the CHECK TABLE command and mysqlcheck command, as mentioned above, to check and repair the corrupt tables in the MySQL database. You can rebuild the corrupt table using the REPAIR TABLE command. If you fail to repair MySQL tables using the native repair options, then you can use Stellar Repair for MySQL. It is a professional MySQL database repair tool that can even repair heavily corrupted tables in the MySQL database. It supports the repairing of both InnoDB and MyISAM tables. The tool can help you restore all the database objects, including tables, indexes, log system tables, etc., with complete integrity. This helps resolve complex corruption issues that occur due to corrupt data. You can download the demo version of Stellar Repair for MySQL to preview the recoverable database objects.