How to repair damaged MyISAM tables?

-->

MyISAM tables in MySQL becomes corrupted due to various reasons like hardware fault, sudden system shutdown or server crash. Due to corruption you may lose all your hard earned data. So it is important to deal with corruption properly. First consider the symptoms of corrupt table:

When MyISAM tables got damaged its queries abort unexpectedly and following errors occur:

1. Can't find table_name. MYI
2. Record file is crashed
3. table_name .frm is locked against changes

There are four steps to repaircorrupt MyISAM tables.

Note: Stop the MySQL server (mysqld) before repairing the tables.

Step 1: Check table: Execute below command to check table for corruption

myisamchk *.MYI

Or

myisamchk -e *.MYI

If above command shows error then go to next step. In case myisamchk crashes while checking errors then go to step 3.

Step 2: Easy safe repair: Execute below command to repair the index file. This command will be able to repair your table only if your data file is in working condition.

Myisamchk -r -q table_name

If it fails to repair your table then follows below steps

1. First backup your datafile.
2. Run below command to remove incorrect and deleted rows from the datafile. This command will also reconstruct the indexes.

Myisamchk -r table_name

3. If command does not repair your table then run below command. It uses safe recovery mode.

Myisamchk –safe -recover table_name

Step 3: Difficult repair: Try this step only if your index file contain wrong information or destroyed (16 KB block) or index file is missing. You need to create a new index file. Follow below steps:

1. First move the data file to a safe location.
2. Create new data and index file. Here is how:

shell> mysql database_name

mysql> Set autocommit=1;

mysql> TRUNCATE TABLE table_name;

mysql> quit

Now copy the old data file back into new datafile. Again try myisamchk -r -q command. It should work now. You can also use REPAIR TABLE command.

Step 4: Very difficult repair: Try this step only if .frm description file has also got crashed. Usually this doesn't happen. Follow below steps:

1. Restore your description file from backup and go to step 3.
2. If backup is not available then create a copy of your corrupt table in another database. Now move the .MYI index file and .frm description file to crashed or corrupt database and also remove the new datafile. Now go to step 2 and try to reconstruct index file.