How to Fix Corrupted Tables in MySQL?

In MySQL, tables are used to store**** data and are created using the CREATE TABLE command. Sometimes, the tables in MySQL database gets corrupted. Due to this, the server may fail to retrieve or display them, leading to errors such as “DB Error”, “could not list tables”, “The table '/var/lib/mysql/temp/#sql_2d22_1' is full”,** and others. Corruption in tables can occur due to various factors, such as lack of storage space on the disk, faulty hardware, abnormal termination of mysqld process, etc. In this article, we will discuss various methods to fix corrupted tables in MySQL.

Methods to Fix Corrupted Tables in MySQL Server

Here are some easy methods that can help you repair and recover data from corrupted MySQL tables.

Method 1: Restore MySQL Tables from Backup

If you have an updated backup of the MySQL database, then a simple option is to recover corrupt database tables from the backup. You can use the mysqldump utility to restore the backup file in MySQL. This open-source command-line utility reloads the dump file, which reproduces all the statements in it.  Here are the steps to use the mysqldump utility to restore the backup file:

Note: Ensure that you have all the privileges and roles granted to your MySQL account using the SHOW GRANTS statement. Also, ensure that you have the ALTER, CREATE, RELOAD, and SELECT privileges for the backup file.

·       Drop and recreate your database using the following command:

mysql > drop db_namemysql > create db_name

·       Next, restore the database by executing the below command:

mysql -u root -p db_name < dump.sql

·       Now, verify if the restored database contains the correct data in the tables.

'mysql > use db_name;

mysql > show tables;'

Alternatively, you can use PhpMyAdmin to restore the backup file. Follow the steps below:

·       Open a web browser on your system and enter http://localhost/phpmyadmin to open phpMyAdmin.

·       Login to the phpMyAdmin.

·       In PhpMyAdmin cPanel, click the Import option.

·       Click Choose File to select the backup file on your local storage.

·       Next, choose SQL from the Format dropdown menu and then click Import.

·       Wait for the import process to complete. Once the process is complete, you will see a success message.

Note: You may face time-out issues when trying to restore large-sized database file using phpMyAdmin.  

PhpMyAdmin can also help repair and restore MySQL tables. Follow the below steps:

·       Log in to phpMyAdmin.

·       Select your database from the left panel.

·       Check the tables you want to repair.

·       Click on the With Selected dropdown and choose Repair Table.

Method 2: Run REPAIR TABLE Command

You can also repair the corrupt MySQL tables using the REPAIR TABLE statement. Here’s how to use this statement.

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]

TABLE tbl_name [, tbl_name] ...

[QUICK] [EXTENDED] [USE_FRM]

It helps you recover corrupted or damaged MySQL database tables’ data. Also, it supports both InnoDB and MyISAM tables. Sometimes, you may require to execute the command multiple times to prevent data overwrites, especially, when the server suddenly closed down while processing the query.

Method 3: Use myisamchk Command

If the tables are created in MyISAM storage engine or you are getting error message indicating corruption in MyISAM table, then you can use the myisamchk command. It can fix the corruption issues in MyISAM tables. Here’s how to run the myisamchk command:

·       First, stop the MySQL Server.

·       Next, run the below command:

myisamchk –recover TABLE

·       Now, restart the MySQL Server.

It can help you repair MyISAM tables but it does not support repairing and recovering unique keys from the corrupt tables. Also, in some cases, it causes data loss as it deletes corrupted rows while performing the repair operation.

A Quick Solution to Repair Corrupt MySQL Tables

To repair severely corrupted MySQL tables quickly, without any data loss or file size restrictions, you can use an advanced MySQL database repair tool. Stellar Repair for MySQL is one such tool that can help you repair multiple tables at once with complete precision. It can recover all the objects, including foreign keys and primary keys, from corrupted MySQL database. Additionally, it can repair tables created in both MyISAM and InnoDB storage engines. This tool also allows to perform selective recovery of database objects.

Conclusion

There can be umpteen reasons that can cause corruption in MySQL tables. If tables get corrupted, the SHOW TABLE command fails to display tables or you may encounter various errors and issues. You can restore the backup file, if available, to recover the corrupt tables. However, if the backup is not available, you can use the REPAIR TABLE command and the myisamchk command to repair the tables. However, these commands do not guarantee complete recovery of the data. For complete recovery, you can use Stellar Repair for MySQL. It can help you repair severely corrupted MySQL databases and recover all the objects without any data loss.