Quick Solutions to Repair Corrupted Tables in MySQL: A Tutorial

MySQL is one of the most popular open-source RDBMS (Relational Database Management Systems). It is an integral part of the rapidly growing LAMP Stack and has gained impressive fandom among developers and system admins. Compared to non-relational databases, SQL provides excellent functionalities to arrange, store, and manage data effectively. The distinguishing table arrangement functionalities and stand-out capabilities of the SQL database make it a preferred choice at all levels of the organisation, both big and small.

MySQL tables can get damaged on rare occasions, indicating that such an error has happened and the data stored among them is unreadable. When you try reading data from a corrupted table, the server crashes and throws an error or a list of errors.

Potential Reasons Behind MySQL Table Corruption

The following are some of the most typical reasons for corrupted tables:

• MySQL service crashes after reboot.
• The machine shuts off unexpectedly due to a power breakdown.
• Bad memory or hardware failure.
• Software bugs in the OS or the SQL code.
• MySQL shuts down while writing.
• The system turns OFF while updating SQL.

Before fixing a table that you suspect is corrupted, create a backup of your information directory. This will help reduce the likelihood of data loss. In this quick guide, we’ll walk you through troubleshooting steps of repairing corrupted tables in MySQL.

Prerequisites

To follow along with this tutorial, you’ll need the following:

• The latest version of Ubuntu installed on your system.
• System users must have sudo privileges.
  • If you face difficulty accessing your sudo account, consider changing the MySQL root password.
• MySQL set up on your server.

Step 1: Stop MySQL Service and Data Backup

Initially, stop the MySQL service using the systemctl command:

After stopping the SQL service, the next step is to create backups of all the existing files into a new directory. Use Ubuntu’s default data directory /var/lib/mysql/ to copy all the system data:

Step 2: Identify the Error

Next, we’ll start our investigation and identify the corrupted table. If the table is using the MyISAM storage engine, use the CHECK TABLE statement and verify the status of the table:

You will see a message as output confirming the corrupted table or tables.

Step 3: Repair the Corrupted Table

If there is an error on the MyISAM table and you have identified it, use the REPAIR TABLE statement to get the error fixed:

If the fix was successful, you should see the following error-free message as output:

However, if you find the error reappearing, follow the MySQL version 8.0 official document to rebuild or repair tables.

Unlike the MyISAM storage engine, the InnoDB process involved in repairing tables is different in the InnoDB storage engine. InnoDB is the default storage engine in MySQL 8.0. It features automated corruption checking and repair operations. The MySQL InnoDB checks for corrupted pages by performing checksums on every page it reads. If there is a discrepancy detected, it will automatically stop the MySQL server.

The bottom line is that there is rarely a need to repair InnoDB tables. InnoDB features a crash recovery mechanism that can resolve most issues through a restart.

Try restarting the MySQL service to see whether you can gain access to the server:

If you fail to rebuild a corrupted InnoDB table even after the reboot, try the “Dump and Reload” method suggested in the MySQL documentation. This method involves regaining access to the corrupted table, using the mysqldump utility. A folder must be created to store and retain the logical backup of the table. Once the error gets fixed, it reloads the table back into the database.

Step 4: Leverage innodb_force_recovery Option

If the server stays crashed or unreachable, try using the force_recovery option in InnoDB. Turning ON the force_recovery option is an excellent way to restore MySQL databases. You can enable this option by modifying the mysqld.cnf file in the nano text editor:

Insert the following line under the [mysqld] section:

Save and close the file after rebooting the MySQL service.

Step 5: Create a mysqldump File

If you are able to access the corrupted table, use the mysqldump option to put all the dumps in the new file. Name the new file as per your preference and make sure to add the .sql extension at the end. Here we are naming the file as demo.sql:

After that, use the DROP TABLE query to remove the table from the database. You can use the following syntax to avoid needing to reopen the MySQL prompt:

Then, using the dump document demo.sql you just prepared, restore the table:

You have successfully fixed your MySQL corrupt table, and are ready to work the database seamlessly.

Conclusion

Table corruptions or unexpected system crashes are likely to happen if you are working with a large volume of data. It is worth mentioning that the MySQL InnoDB storage engine is more fault-tolerant than the previous MyISAM engine. Tables utilising InnoDB storage engine can still be corrupted, although the danger of table corruption and breakdowns is significantly reduced thanks to its auto-recovery features.

Finally,  there are many topics and tutorials on databases that you can explore on our blog:

• How to Install MySQL on Centos 7
• MySQL User — Create and Grant Permissions
• Setting Up Replication in MySQL on Ubuntu

Happy Computing!