How to Repair Corrupt MySQL Database Tables Step-by-Step

In the modern world, companies are not solely dependent on a specific database server platform. There are many database platforms available that are adequate to handle moderate workloads and client requirements of high availability and disaster recovery. MySQL is one of those database platforms which provides a lot of features and high performance.

Just like other RDBMS, MySQL is also prone to database and table corruption. The recent outage caused by Microsoft and CrowdStrike also impacted MySQL database servers. Due to operating system failures, the tables of the database or entire databases get corrupted.

In this article, I am going to show how we can corrupt and fix a table of MySQL database. This article is helpful to DBAs to simulate the failures and help them determine the best possible way to restore or repair the corrupt MySQL table.

Understanding MySQL Database Corruption

MySQL database corruption can manifest in various ways, including:

Inaccessible Tables

The entire table or specific subset of the table becomes inaccessible. When you try to access the corrupted table, you will encounter errors that point to the corruption of the index file or data file of a table.

Data Inconsistency Errors

If the table is corrupted, instead of meaningful data, your query might return some garbage values or inconsistent or incomplete results.

Unexpected Shutdowns

In some cases, MySQL might crash while accessing the table or running the backups using mysqldump. Once I encountered this error while simulating the corruption scenario. I corrupted the data file of a table. After I started the service, when I tried to access the table using a SELECT statement, MySQL services crashed automatically.

Error Messages During Database Operations

While accessing the corrupted table, you might encounter certain errors like:

ERROR 1016: Can't open file: 'table_name.MYI' (errno: 145)

Table ‘table_name’ is marked as crashed and should be repaired

Got error 28 from storage engine

ERROR 1030: Got error 127 from storage engine

These errors indicate that the data file or associated index files are corrupted. 

It's crucial to understand the root cause of the corruption to prevent future occurrences and ensure data integrity.

Prerequisites of Repairing MySQL Tables 

Before attempting any repairs, ensure you have the following:

• Complete backup of your MySQL database
• Sufficient disk space
• Administrative access to the MySQL server

Now, let us simulate the table corruption.

Corrupt MySQL Table

Before we learn about the process of fixing the database, first we will understand how to corrupt the database. For demonstration, I have created a database named "CorruptDB" on a MySQL database server. I have also created a table named corrupt_table in the CorruptDB database.

Here is the code to create a database and table.

Create database corruptDB;


Use corruptdb;


CREATE TABLE corrupt_table (

id INT AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(100)

) ENGINE=MyISAM;

I have added a million records to the table by running the following query. 

INSERT INTO corrupt_table (data)

SELECT CONCAT('RandomData-', FLOOR(1000 + (RAND() * 9000)))

FROM corrupt_table;

Note that the simulation of table corruption is done on my laptop. Do not try this on production, development, or any other environment. The table I am using in this demonstration is created with the MyISAM database engine. You can read about Alternative Storage Engines to learn more about the database engines of MySQL Server.

I have performed the following steps to corrupt the table.

Step 1: Stop MySQL Server Services

You need to stop the MySQL server. To do that, run PowerShell as administrator and execute the following command.

net stop MySQL

Alternatively, you can also stop it from Services.

Step 2: Corrupt the Index File of the Table

Now, we must corrupt the index file of the table. When you create any table in the MyISAM database engine, there are three files created when you create a table using the MyISAM database engine. 

• MYD files: This file contains actual data.
• MYI files: This is an index file.
• Frm files: The file contains a table structure.

We will corrupt the index file. The data files are located at the default location which is “C:\ProgramData\MySQL\MySQL Server 8.0\Data\corruptdb.” For corruption, we are using a hex editor.  

Download and install the hex editor. Open the MYI files using it. The file looks like the following image:

Replace the first 5 bytes with some random values. Save the file and close the editor.

Now, let us start the service and try to access the table.

Step 3: Start the MySQL Services and Access the Table

First, start the MySQL Services by executing the following command in PowerShell.

net start MySQL

Once services are started, execute the following query on the MySQL command line.

mysql> use corruptdb;

Database changed

mysql> select count(1) from corrupt_table;

The query returned the following error:

The error indicates that the index of the corrupt_table has been corrupted and must be repaired.

Manual Methods to Repair MySQL Tables

There are certain methods that you can use to repair the corrupted MySQL table. The first method is to use the check table and repair table commands.

Check Table and Repair Table Commands

You can restore the table using the CHECK TABLE and REPAIR TABLE built-in commands of MySQL. These commands are used to diagnose and repair any MyISAM table.  

The check table command checks the integrity of the table. It checks the table structure, indexes, and data for any corruption and shows the details. The syntax is below:

CHECK TABLE [option]

You can specify the different options.

1. QUICK: This option quickly checks and identifies issues like corrupted indexes.
2. FAST: It checks tables that are not closed properly.
3. CHANGED: This option checks only those tables that are changed after the last CHECK TABLE execution.
4. MEDIUM: This option checks the records and verifies that the links between the table and data are correct.
5. EXTENDED: This option thoroughly scans and verifies the table structure and contents.

Here in this demo, we will perform a quick scan. Here is the command.

CHECK TABLE corrupt_table

Screenshot:

As you can see in the above screenshot, the error indicates that the index of the corrupt_table is corrupted and needs to be fixed. 

We will use the REPAIR TABLE command to fix the corruption in the table. The REPAIR TABLE command is used to recover the table structure and data from corruption, especially the table that has the MyISAM database engine. In case the index of the table is corrupted, the REPAIR TABLE command rebuilds the indexes.

The syntax of REPAIR TABLE is as follows:

REPAIR TABLE [option]

You can specify the following options:

• QUICK: It repairs only the index file of a table. It does not access the data file of a table.
• EXTENDED: When we use this option, the command will perform a thorough repair. It also repairs or recreates the index file by scanning all the records of the table.

In this demo, we have corrupted the index of the table; hence, we will use the QUICK option. Execute the following command.

REPAIR TABLE corrupt_table

Screenshot:

As you can see in the above screenshot, the corrupt_table has been repaired successfully. To verify, run the following query on MySQL Workbench:  

use corruptdb;

select count(1) from corrupt_table;

Query output:

As can you see, the table is now accessible.

Restore Table Using mysqldump Command

The second method is to restore the entire table from the backup. This method can be used when the table is highly corrupted and cannot be repaired by using the REPAIR TABLE command. 

To restore a MySQL table from the backup, we can use the mysqldump command. You can read the article "mysqldump — A Database Backup Program" to learn more about how to use the mysqldump command. The syntax to restore the table is below.

mysql -u [username] -p [database_name] < [table_dump.sql]

In the syntax:

• Username: Enter the user name that you are using to connect to the MySQL database.
• -p: Specify the password. If you keep it blank, MySQL will prompt for a password.
• [database_name]: Specify the name of the database in which you are trying to restore the table.
• Table_dump.sql: Specify the fully qualified name of the backup file.

For demonstration, I have taken a backup of the CorruptDB database which is located in the C:\MySQLData\Backup directory.

To restore the corrupt_table, we can use the following command.

mysql -u root -p corruptdb < C:\MySQLData\Backup\corrupt_table.sql

Once the command executes successfully, you will be able to access the table. Execute the following query to verify.

use corruptdb;

select count(1) from corrupt_table;

Query output: 

As you can see in the above screenshot, the table has been restored successfully.

Using phpMyAdmin

You can also use the phpMyAdmin tool to repair any corrupted MySQL database. phpMyAdmin is a graphical user interface to manage and maintain MySQL and MariaDB. For demonstration, I have installed it on my laptop. 

• To repair the table, launch phpMyAdmin and navigate to the database in which the corrupted table exists. 
• In the right pane, you can view the list of the tables that are created in the CorruptDB database. 
• Select Corrupt_table from the list and select the Repair table option from the drop-down box.

Here is a screenshot for reference:

Once the table is repaired, you can see the status of the corrupt_table becomes OK. Here is the screenshot:

Conclusion

In this article, we learned about the possible reasons for MySQL database corruption and how to fix them. I have explained a step-by-step process to corrupt MySQL tables using a hex editor. I have also covered how to fix them using the CHECK TABLE and REPAIR TABLE commands. We have also learned how to restore the table using the mysqldump utility. 

Repairing a corrupt MySQL database table can be challenging, but with the right tools and methods, it is manageable. Manual methods provide a basic solution, while phpMyAdmin offers a comprehensive and reliable recovery option. Always ensure regular backups and maintain your database health to minimize the risk of corruption.