In the modern world, the companies are not solely dependent on a specific database server platform. There are many database platforms available that are adequate to handle moderate workload 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 the database and table corruption. The recent outage caused by Microsoft and cloud strike also impacted the MySQL database servers. Due to operating system failures, the tables of 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 the DBAs to simulate the failures and help them to 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 which points to the corruption of 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, the MySQL might crash while accessing the table or running the backups using mysqldump. While simulating the corruption scenario, once I encountered this error. I corrupted the data file of a table. After I start the service, when I try to access the table using SELECT statement, MySQL services crashed automatically.

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 indicates 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.

Pre-requisites 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 to fix the database, first we will understand how to corrupt the database. For demonstration, I have created database named corruptdb on MySQL database server. I have also created a table named corrupt_table in corruptdb database.

Here is the code to create 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 million records in the table by running 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 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 following command.

net stop MySQL

Alternatively, you can also stop it from the services.msc.

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 MyISAM database engine, there are three files created when you create a table using MyISAM database engine.

1. MYD Files: This file contains actual data.
2. MYI Files: This is an index file.
3. Frm files: The file contains 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 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 following query on MySQL command line.

mysql> use corruptdb;
Database changed
mysql> select COUNT(1) as Computed  from corrupt_table;

The query returned following error:

The error indicates that 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 check table and repair table command.

Check Table and Repair table command

You can restore the table using 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 the 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 verify that links between table and data are correct.
5. EXTENDED: This option thoroughly scans and verify 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 REPAIR TABLE command to fix the corruption in the table. The REPAIR TABLE command is used to recover the table structure and data from the corruption specially the table that has 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 following:

REPAIR TABLE [option]

You can specify the following options:

1. QUICK: It repairs only the index file of a table. It does not access the data file of a table.
2. EXTENDED: When we use this option, the command will perform a through repair. It also repair or recreate 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 QUICK option. Execute following command.

REPAIR TABLE corrupt_table

Here are the results.

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

use corruptdb;
select COUNT(1) as Computed  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 entire table from the backup. This method can be used when the table is highly corrupted and cannot be repaired by using REPAIR TABLE command.

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

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

In the syntax,

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

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

To restore the corrupt_table we can use following command.

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

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

use corruptdb;
select COUNT(1) as Computed  from corrupt_table;

Query output

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

Using phpMyAdmin

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

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

Here is a screenshot for the 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 learn about the possible reasons of MySQL database corruption and how to fix them. I have explained a step-by-step process to corrupt MySQL table using Hex editor. I have also covered how to fix them using CHECK TABLE and REPAIR TABLE command. We have also learned how to restore the table using mysqldump utility. Always ensure regular backups and maintain your database health to minimize the risk of corruption. Suppose, you do not have any valid backups, you can look at other database recovery software like Stellar Repair for MySQL which offers a comprehensive and reliable recovery option.