How to Repair Corrupted Tables in MySQL

Introduction

Sometimes MySQL tables can become corrupted, meaning that an error has occurred and the data in them cannot be read. Attempting to read from a corrupted table will usually result in a server crash.

Common reasons for table corruption

MySQL service stops in the middle of writing data
Table modification by external application while it is being modified by the server
Unexpected car shutdown
Computer hardware failure
Software bug in MySQL code

If you suspect that one of your tables is corrupted, you should back up your data directory before you begin repairing or troubleshooting. This will help you minimize the risk of data loss.

Steps to repair a corrupted table in MySQL

First, stop the MySQL service:

sudo systemctl stop mysql

Note: On some systems, such as Rocky Linux, the MySQL service may be called mysqld To be known.

Then copy all your data to a new backup directory. On Ubuntu systems, the default data directory is /var/lib/mysql/ is:

cp -r /var/lib/mysql /var/lib/mysql_bkp

After you have backed up the table, you are ready to check if the table is actually corrupted. If the table is not backed up by the storage engine, MyISAM If you use MySQL, you can restart it by running the command CHECK TABLE Check from within the MySQL prompt:

sudo systemctl start mysql

CHECK TABLE table_name;

If the table is corrupt, you will be shown a message confirming this. If a MyISAM table is corrupt, you can usually fix it using the command REPAIR TABLE Fixed it:

REPAIR TABLE table_name;

If the repair is successful, you will see a message similar to this in the output:

Output
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status | OK |
+--------------------------+--------+----------+----------+

If the table is still corrupt, the MySQL documentation provides alternative methods for repairing corrupted tables.

Repairing corrupted tables with the InnoDB storage engine

If the table is corrupted from the storage engine InnoDB The repair process will be different depending on which version of MySQL you are using. InnoDB has been used as the default storage engine for MySQL since version 8.0 and has automatic corruption checking and repair features. InnoDB detects bad pages using the checksums of each page it reads and automatically stops the MySQL server if it finds a difference in the checksums.

It is rare to need to repair InnoDB tables, as InnoDB has a crash recovery mechanism that can resolve most issues by restarting the server. However, if you find yourself in a situation where you need to rebuild an InnoDB table, the MySQL documentation provides instructions on how to do so. Dump and Reload recommends. This method involves regaining access to the corrupted table, using the tool mysqldump It is used to make a logical backup of a table that preserves the table structure and the data within it, and then reinserts the table into the database.

Restart the MySQL service

First, try restarting the MySQL service to see if this gives you access to the server:

sudo systemctl restart mysql

If the server is still down or unavailable, you can use the option force_recovery Enable InnoDB in the settings. To do this, create a file mysqld.cnf Edit . On Ubuntu and Debian systems, this file is usually located in /etc/mysql/ On Red Hat and Rocky systems, this file is usually located in /etc/my.cnf.d It is located.

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

In the section [mysqld] Add the following line:

. . .
[mysqld]
. . .
innodb_force_recovery=1

After saving the changes and closing the file, try starting the MySQL service again. If you can access the corrupted table, use the tool mysqldump Use this to back up the table data. You can name this file whatever you want, but here it will be called out.sql We call it:

mysqldump database_name table_name > out.sql

Then delete the table from the database. To do this, you can use the following command:

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

Finally, create the table using the file dump Retrieve the one you recently created:

mysql -u user -p < out.sql

Note that the InnoDB storage engine is generally more resilient than the older MyISAM engine. Tables using InnoDB can still become corrupted, but due to its self-healing features, the risk of table corruption and crashes is much lower.

Result

By following this guide, you learned how to repair corrupted tables in MySQL and used different methods to resolve issues in both MyISAM and InnoDB storage engines. Always back up your data before making any changes to avoid the risk of data loss.