Thursday, April 15, 2010

Repairing Corrupt MySQL InnoDB table

MySQL InnoDB table corruption or damage results in inaccessibility of all its stored records. There can be different causes for corruption in table including improper system shutdown, fatal virus hit, application malfunction, and human errors. However, the most common way to identify that your database has been corrupted is when you view an error message, instead of the records. Restoration of data from an updated backup allows you to overcome such error messages. But restoration process can only be performed in case the user has maintained a valid backup. If you have not created a backup or the backup is corrupted, then you will need to repair the corrupt database table using an advanced third-party Repair MySQL Database tool.

Consider a practical case, where you receive the below error message when you attempt to access the records saved in the MySQL table (employee):

“#1051 - Unknown table 'employee'”

After the above error message appears, the data stored in the “employee” table gets inaccessible. Furthermore, the error message pops up whenever you open the table.


The main reason for the occurrence of the above error message is corruption of employee table.


The resolution for the above error message can be achieved by following steps:

1.The first steps is to add “innodb_force_recovery=4” to /etc/my.cnf configuration file.
2.Restart MySQL and dump the employee table.
3.Shutdown your database and erase the data directory.
4.Create default tables using mysql_install_db.
5.Remove “innodb_force_recovery=4” from /etc/my.cnf configuration file.
6.Restore the data from a valid backup.

The above steps fail to resolve your issue if you have not created a backup. In such situations, you will need to search for effective MySQL Repair tool that can repair the corrupt table. Such Repair MySQL tools are non-destructive in nature and perform repair without modifying the original data. The repair utilities employ intensive scanning methods to systematically repair the database and restore it on the user-specified destination. These tools can easily be used by both non-technical users.

1 comment:

  1. I am very enjoyed for this blog. Its an informative topic. Its opportunity is so fantastic and working style so speedy. I think it may be help all of you. Thanks a lot for enjoying this beauty blog with me. I am appreciating it very much! Looking forward to another great blog. Good luck to the author! All the best!
    Work Website Design Delhi