Fixing MySQL Database corruption

If you ask a DBA about his worst nightmare then most probably his answer would be “Database Corruption”. There is nothing worst than losing your years of hard earned data. In MySQL you still have the chances of getting your data back using its inbuilt utilities. This article can help you surely in recovery of your corrupt database.

For MyISAM Tables:
Myisamchk command: This command is used to identify and fix corruption in MyISAM tables. To check a table for corruption first shut down the server and then type below command on command prompt.
myisamchk /user/local/mysql/db/emp.myi

This command will display all the damaged tables.

Now repair the damaged table using below command. Before running the repair command, take the backup of damaged table. Again shutdown the server and run the command.
myisamchk -r /user/local/mysql/db/emp.myi

This command will repair the damaged table by reconstructing the table index file. If this option fails to recover the table then try –safe-recover option. It scans all the records one by one and restores the index. The syntax for command is:
myisamchk –safe-recover /user/local/mysql/db/emp.myi

REPAIR TABLE command: It is used to repair a damaged table. The syntax for command is:
mysql>REPAIR TABLE emp;

For MyISAM & InnoDB Table
Mysqlchk command: Just like myisamchk, mysqlchk check is also used to check and repair damaged tables. The main difference is that mysqlchk works on both innodb and myisam while myisamchk works only on myisam tables.

To check a single table for corruption, run below command:
mysqlchk -c comp [database_name] emp [table_name] -u root -p

To check multiple tables, run below command:
mysqlchk -c comp [database_name] emp [table_name1, table_name2, table_name3....table_nameN] -u root -p

If above command detect corruption then run below repair command:
Mysqlchk -r comp [database_name] emp [table_name] -u root -p

CHECK TABLE command: It is used to check table for errors. To run this command you don't need to shut down the server as it is executed by MySQL client. The syntax for command is:
mysql> CHECK TABLE emp;

MySQL Repair Tool: There are so many good third party tools available in market. One such effective tool is Stellar Phoenix database recovery for MySQL. It repairs damaged database for both myisam and innodb storage engines.