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.