Recover Old MySQL Database After Corruption

Much to the annoying MySQL database corruption errors occurs that many of MySQL users always got. The list are:

• tbl_name.frm is locked against change
• Can't find file tbl_name.MYI (Errcode: nnn)
• Unexpected end of file
• Record file is crashed
• Got error nnn from table handler

These are the some common error list in a series of tweaks to the MySQL Database corruption errors. The all errors divided in four steps:

1. some errors are easily to handle,
2. some are easily resolve after restarting MySQL database or by simple repair.
3. some are difficult
4. Some are only resolved by third party tools

The most of the MySQL database corruption error occurs due to table corruption.

To get more detailed explanation about the error, you first run "perror aaa". In the place of aaa, you mention your error number. The following command will help you: "how to use "€œperror" to find the exact explaination about the error numbers:

shell> perror aaa
or 
shell> perror 126 or whatever the error message you got

Here are some most common error messages with explanation:

MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

All these errors are related to table corruption, you can easily fix it by using myisamchk utility except error 135 & 136.As I have already explain in my above lines that the repair process involves up to five steps:

NOTE: First stop your MySQL database server.

First Step: Check table: run following command to check error message:

Run myisamchk *.MYI

NOTE: If you get any unexpected error message when you are checking or if myisamchk crashes, directly go to Step 3.

Second Step: Easy safe repair: run this command to easily repair your corrupt table:

First, try myisamchk -r -q tbl_name

If the step get failed use myisamchk --safe-recover tbl_name. The command is used for Safe recovery mode it handles a few cases that regular recovery mode does not.

NOTE: If you get any unexpected error message when you are checking or if myisamchk crashes, directly go to Step 3.

Third Step: Difficult Repair: All above two steps wont't work in case if the index file is missing. So first you create a new index file:

1. First copy the data file to another safe place.
2. After that use these steps to create a new empty data file & index file:

shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit

3. Now copy the old data file onto the new created data file.
4. Now go back to Step 2, now it will work.

Fourth Step: At Last use third party MySQL Recovery Software: At last if all following steps won't work for you than go for third party tools. These tools are easy to use & handle corruption errors by few easy steps & get back your data.

We know we cannot stop corruption, but for the sake of safe database recovery, we should opt some steps with some tweaks for safe database recovery. Lets me know in the comments about any MySQL database issues that drive you nuts, and I'll see if I can find the solution to fix it.