How to Repair MySQL Database Table Data – A Detailed Guide

What Might Corrupt MySQL Database Table?

For MySQL database corruption, there can be numerous reasons. Few of them are more common. So, we will discuss these obvious causes for MySQL database file corruption.

• When your MySQL server has bugs, you will probably face MySQL database file corruption.
• It also appears when the MySQL server crashes and the program terminates abruptly.
• Another common reason for MySQL file corruption is the intrusion of malicious programs.

Now, you have learned why MySQL database tables get corrupted. It is time to learn how to repair corrupt MySQL database table data.

Repair MySQL Database Table – Manually

In the manual method to fix MySQL repair DB tables, you will use certain inbuilt statements that will rebuild the corrupted table of the desired database. We provide you with different techniques that support both MyISAM and InnoDB storage engines. Some techniques are applicable for Windows users while the remaining are for Ubuntu users.

Follow the below steps to repair corrupted MySQL database tables.

#1. Repair damaged MySQL table using phpMyAdmin

• Login to your cPanel. In the Database section, select phpMyAdmin.
• After that, click on the Database and select the damaged tables.
• Click on the With Selected field to expand the drop-down menu.
• Choose the Repair Table option to repair the corrupted MySQL database table.

#2. Use the MySQLCHECK Command

The mysqlcheck command allows you to use various SQL statements like CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE commands efficiently as per the requirement. Follow the below instructions to use the MySQLCHECK statement.

• Use the command to open the MySQL database folder as a root user.

cd /var/lib/mysql

• After that, type mysqlcheck -r <DB_name> <table_name> and press Enter.
• It will repair the damaged table of the selected database.

#3. Use the Repair Table Query

The ‘Repair Table’ query enables you to restart the repair process when the server shuts down amidst the database recovery. Corruption of MySQL database tables occurs when you initiate a database recovery process that failed due to any reason. Moreover, It is mandatory to execute this command just after you notice the SQL table recovery process has failed. Otherwise, it will not permit the MySQL repair table.

#4. Use the ALTER TABLE Command

ALTER TABLE statement permits you to add, delete or modify a column in a table. With the help of this statement, you can also rebuild the corrupted MySQL database tables. The following commands are used for different operations.

• Rebuild the database table in the InnoDB storage engine:

ALTER TABLE <table_name> ENGINE = InnoDB;

• Rebuild the database table in the MyISAM storage engine:

ALTER TABLE <table_name> ENGINE = MyISAM;

• For unknown storage engine

SHOW CREATE TABLE <table name>;

Perform Engine Specific Diagnostic

Apart from the above methods, some storage engine-specific diagnostic processes help you to repair the database table associated with the particular storage engine. The two storage engines are MyISAM and InnoDB. Depending upon that, there are two methods to repair MySQL database tables.

#1. Use MyISAMCHK Command to Repair MyISAM Table

• Open the terminal window and type the following command.

systemctl mysql stop

• Now, locate the database as a root user by using the following command.

cd /var/lib/mysql/<database_name>

• After that, use the below command to check the integrity of the table.

myisamchk <table_name>

• Repair the damaged table by using the following command.

myisamchk –recover <table_name>

• Finally, restart the MySQL server using the below command.

systemctl restart mysql

#2. Execute the InnoDB Recovery Process

• First, open the Global Configuration File (my.cnf) via any text editor.
• After that, locate the mysqld section and add the below line.

innodb_force_recovery=4

Note: 4 is the recovery level. The levels start from 0 and go up to 6.

• Save the updated file and restart the MySQL server using the following command.

service mysql restart

• Now, import all the databases to the databases.sql file.

mysqldump –all-databases –add-drop-database –add-drop-table –routines > databases.sql

• Start the MySQL program and drop the damaged database via the DROP DATABASE command.
• After that, stop the SQL server using the below command.

service mysql stop

• Again, open my.cnf file using any desired text editor and comment out the innodb_force_recovery=4 line by adding ‘#’ before the command. Save the changes.
• Now, start the MySQL program via the below command.

service mysql start

• Use the following command to restore the database.

mysql < databases.sql

• Now, check the damaged database table.

These are a few methods you can adopt to repair MySQL database tables. Some of the above techniques are effective for Windows users, while the rest are suitable for Ubuntu users. Despite that, a few major drawbacks are associated with these manual procedures which make them inconvenient and unreliable for most users.

Drawbacks of the Manual Method to Repair Corrupted Database Table

The manual solutions are effective, yet they are not the most suitable solution for every user. They have various limitations that are listed below.

• Complex and time-consuming.
• Not ensure complete recovery of MySQL database table data.
• Does not restore or optimize all MySQL tables from heavily corrupted files.
• The steps to recover data are different for Windows and Linux OS.

Now, you might be thinking if the above solution is not reliable and effective, what else should one opt for? We suggest the professional MySQL Database Recovery Tool.

Best Solution to Repair Corrupt MySQL Database Table

DRS MySQL Database Recovery Tool is the most preferred solution to repair MySQL database tables, queries, indexes, stored procedures, views, columns, alias, etc. The application enables you to recover data from heavily corrupted MySQL database files. Moreover, it provides numerous functionalities to simplify and enhance database recovery. This application supports all versions of MySQL databases and works swiftly on your Windows PC having any OS version.

Conclusion

I hope after reading this article, you have learned how to repair MySQL database table data manually and professionally. You can repair table in MySQL via phpMyAdmin, MySQLCHECK command, repair tables, Alter table queries, etc. Also, you can adopt storage engine-specific recovery methods. However, these techniques are reliable when you have the technical competency and the database table corruption is minor. For severely corrupted files, we recommend the advanced MySQL Database Recovery Tool that can efficiently recover tables, queries, indexes, stored procedures, etc.