How to Repair a Corrupted MySQL Table

Introduction

To maintain data integrity in MySQL, you may need to repair MySQL table structures, as MySQL is a Relational Database Management System (RDBMS) that stores related data in tables. These tables consist of rows called records and columns known as attributes, uniquely identifying each row by a key.

RDBMSes such as MySQL include functionality to preserve data integrity, consistency, and accuracy. CRUD (Create, Read, Update, Delete) transactions execute on RDBMS using SQL, following the ACID (Atomicity, Consistency, Isolation, Durability) mechanism.

• Atomicity - A CRUD transaction takes place in its entirety or it doesn't happen at all.
• Consistency - Data is in a consistent state when a CRUD transaction starts and ends.
• Isolation - Ensures concurrent transactions without leading to inconsistency. The intermediate transaction state is invisible to other transactions until it's committed.
• Durability - After a CRUD transaction completes successfully, the changes persist even in a system failure.

This helps the system achieve consistency and stability.

This guide covers how to repair MySQL tables.

Prerequisites

• Working knowledge of SQL.
• Properly installed MySQL.
• System running Linux like Ubuntu.

Causes of MySQL Table Corruption

Table corruption leads to data held within them being unreadable, with attempts to read data leading to the MySQL server crashing. Common causes of MySQL table corruption include:

• Server hardware failure.
• Unexpected machine shutdown.
• The MySQL server stopped in the middle of a write.
• Using 3rd party software to access the database.
• Software bugs within the MySQL code.
• Wrong data schema.

Steps to Repair a MySQL Table

Before attempting to fix or troubleshoot problems, create a backup of your data directory to reduce the risk of data loss.

1. First, stop the MySQL service:

$ sudo systemctl stop mysql

On Ubuntu, the default data directory is /var/lib/mysql.

2. Create a backup:

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

Determining MySQL Engine Type

As of MySQL version >= 5.5, InnoDB is the default storage engine and has a couple of features, including automated corruption checking and repair operations. Prior MySQL versions use MyISAM as the default storage engine, which is more susceptible to table corruption.

To check the MySQL engine type, enter the following query in the MySQL console:

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database_name';

Note: Replace 'database_name' with the name of your database.

This will display an output like this:

+--------------------+-------------+-----------+---
| Table_Name     |  Engine   |  Version |    ...
+--------------------+-------------+-----------+---
| customers      |  MyISAM   |    10    |    ...
+--------------------+-------------+-----------+---
| staff          |  InnoDB   |    10    |    ...
+--------------------+-------------+-----------+---

Repairing MyISAM Tables

Check for corruption on tables running the MyISAM storage engine, such as the customers table above, using the following MySQL prompt:

mysql> CHECK TABLE  customers;

Corruption errors on MyISAM tables are usually repaired with the REPAIR TABLE table_name prompt. E.g

mysql> REPAIR TABLE customers;

This will return an output telling the repair was successful. This method repairs most table errors on MyISAM storage engines.

Note: REPAIR TABLE requires SELECT and INSERT privileges.

If the repair does not result in an OK value, use the myisamchk utility command within the database directory:

$ myisamchk --safe-recover

This will most likely fix all corruption errors if the previous prompt fails to.

For more options with the myisamchk utility, check the documentation.

Steps to Repair InnoDB Tables in MySQL

InnoDB performs checksums on each page it reads. When it finds a checksum inconsistency - it automatically stops the MySQL server. The InnoDB storage engine is quite stable; thus, repairs are rarely needed. It features a crash recovery mechanism that can resolve most issues by restarting the server.

If a server restart doesn't fix the issue, use an alternative method called a DUMP and RELOAD to rebuild the table. This method involves creating a logical backup of the table, which preserves the table structure and data within, and finally reloading the table back into the database.

1. Start the InnoDB recovery process:

Enable InnoDB's force_recovery option by editing the configuration file:

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

2. Find the [mysqld] section within the file, add the following line and save:

…
[mysqld]
…
innodb_force_recovery = 1

The default value of innodb_force_recovery is 0. Change this to a value between one and six to start the InnoDB engine and dump the MySQL table.

Note: A innodb_force_recovery value greater than four increases the risk of further data corruption. If need be, increase incrementally from 1.

3. Restart the MySQL service:

$ sudo systemctl restart mysql.service

4. Dump the database:

If the MySQL service restarts successfully and you can access the corrupted table, the next step is to dump the table data to a new file using the mysqldump utility.

$ mysqldump database_name table_name > output.sql

This dumps the table data into a new file named output.sql.

5. Drop the table from the database:

The next step is to drop the table from the database:

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

The above command will drop the given table name from the database.

6. Restore the table:

The final step in the DUMP and RELOAD method is to restore the table to the database:

$ mysql -u user -p < output.sql

Conclusion

This guide covered how to repair a MySQL table, focusing on those running the MyISAM or InnoDB storage engines. For more details on how to repair MySQL tables, visit the MySQL reference documentation.