5 Proven Methods to Repair Corrupt MySQL Table

MySQL Server stores all the information in the form of tables.  Certain factors like abrupt system shutdowns, bad sectors on your system’s hard disk, software issues, insufficient storage space, etc. can corrupt MySQL tables at any time. You might not be able to access the MySQL database's tables if this occurs. Also, you may fail to run queries which were related to the affected table and face errors. It can interrupt your workflow and productivity as well. In this post, we will share 5 proven methods to repair corrupt MySQL Table.

Methods to Detect corruption in MySQL Tables:

The MySQL Server offers a series of commands, such as Innochecksum utility and CHECK TABLE command, to detect corruption in MySQL tables. The InnoDB tables corruption is very less, as it has crash recovery, whenever the engine detect any issue in pages then it crashes the server to prevent further data loss. In such a case where the server is not accessible, the Innochecksum utility comes into the picture. You can use it to check corruption, especially in InnoDB tables. Here is the command:

shell> innochecksum [options] file_name

If your server is working, then you can run the CHECK TABLE command using the below command:

CHECK TABLE test_table FAST QUICK;

Proven methods to repair corrupt MySQL Table

Once you have confirmed the corruption in tables in MySQL database, then follow the methods described below to restore and repair them:

Method 1-Restore Backup File

If you have enabled binary logging or readable backup files, then you can use them to restore corrupt Backup files using point-in-time recovery. If the binary logging is disabled and you have a readable dump file, then you can use it to restore the corrupt MySQL Table . To do this, you require MySQL dump utility and permissions like SELECT privilege to run statements in the tables easily. Here is the command to restore the dump file using MySQL dump utility:

mysql -u root -p db_name < dump.sql

Note: Before executing the above command, ensure you have dropped and recreated the MySQL database. Make sure your MySQL Server is running.

Method 2- Use the mysqlcheck Command

The mysqlcheck command can also repairs corrupt tables in the MySQL database. To execute this, run below command:

mysqlcheck --r database_name table_name

Method 3- REPAIR TABLE command:

The REPAIR TABLE command can repair and rebuild the data (columns, table, view, index) etc in MySQL tables. When you execute this command, it reconstructs the data in tables. It can remove errors that occurred due to corruption in the MySQL database.Here’s the command:

REPAIR TABLE table name;

Method 4 - Use Myisamchk Command

You can also use myisamchk command to repair MyISAM tables. But to run this command, first, stop the MySQL Server. The command to execute is given-below:

myisamchk –recover TABLE

Next, restart the MySQL Server.

Method 5-Use Professional MySQL Repair tool

You can even use a powerful DIY MySQL repair tool like Stellar Repair for MySQL to repair database tables. The tool can resolve corruption errors due to disk errors, software bugs, system issues, and more. It supports all types of tables in MySQL regardless of storage engine. It lets you restore even the deleted data from the MySQL tables with complete precision and without any file size restriction. In addition, the tool supports repairing tables created in both Linux and Windows systems.

Advantages of Stellar Repair for MySQL:

·  Easy to download and install.

·  User-friendly Interface.

·  Repair MySQL tables without any data loss.

·  Recover SQL database tables created in 2022, 2019 and earlier versions.

·  Repair function, views, rules, tables, etc, from the MySQL database

·  Allows you to preview repairable data.

·  Recover deleted data from the MySQL tables.

Conclusion

Just like other database tables, MySQL tables are also prone to corruption and errors. The corruption in database tables can happen due to varied factors or reasons. If you are facing errors due to damaged tables in MySQL, you can follow the abovementioned methods. If you have not created a backup file then you can use repairing commands to repair tables in MySQL. If none of the methods help you in this, then use Stellar Repair for MySQL to simplify the process of repairing data from corrupt InnoDB and MyISAM tables with complete precision.