An overview of MySQL database corruption and adequate measures of recovery

When it comes to designing web applications, MySQL always has an edge over other database management systems. It is employed by a majority of organizations that use the LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack. Despite being a reliable and the most popular open source database, it is subject to corruption. One of the primary reasons for MySQL database corruption is abnormal server shutdown. Other reasons include virus infection, operating system bugs, etc. These problems ultimately lead to critical data loss. To overcome such situations, you need to go for MySQL recovery through a reliable third-party utility.

You may encounter some strange and abnormal behavior while trying to access or use a corrupt MySQL table. You may receive the following error messages on your screen:

• Got error nnn from table handler

• Can't find file tbl_name.MYI (Errcode: nnn)

• Record file is crashed

• Unexpected end of file

• tbl_name.frm is locked against change

Cause: The above error messages indicate that you probably have some corruption in your MyISAM table that you are trying to access. If your MyISAM table is damaged, you cannot access the contents of the table.

This would eventually lead to a case of serious data loss. At this stage, you can use an updated backup to restore and regain access to all inaccessible database components. However, if the backup is missing, you should follow the below mentioned resolution steps.

Resolution: Try any of the following methods to resolve the above mentioned MySQL database corruption issues:

• Use Check Table: First, check the consistency of your tables using the Check Table command with suitable parameters. You should use the following syntax:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

• Use Repair Table: If the Check Table command showed errors, you should use Repair Table command to repair corruption in the MyISAM tables. For the same, use the following syntax:

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE

tbl_name [, tbl_name] ...

[QUICK] [EXTENDED] [USE_FRM]

If the problem still persists, you should take help of a proficient MySQL repair utility. These third-party tools perform extensive scanning of the corrupt MySQL database to retrieve precious database objects, such as tables, indexes. queries, constraints, etc.

They can easily recover all types of MySQL files, including '.myd', '.frm', and '.ibdata'. They also allow saving the details of the recovery process in a separate log file at any user-specified location. Moreover, they are compatible with Windows 7, Vista, Server 2003, XP, and 2000.