Ways to Repair MYSQL Databases

You can't execute any query(select ,update) against the mysql table,if the particular table was corrupted.At that time you need to check the table


How to check the table:

Go to the mysql client and then choose the database
and then tyoe the below command

CHECK TABLE tablename

* tablename-->your table 

out put

 

mysql> CHECK TABLE fixtures;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| sports_results.fixtures | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.01 sec)

If the table was corrupted you may be received the following errors,

• Record file is crashed
• Unexpected end of file
• can't find file tablethatshouldbethere.MYI
• tablethatwasworking.frm is locked against change
• Got error ### from table handler.

 now you need to be repair the table.


How to Repair the table :

Syntax: Repair table tablename <options>;
options is optional 
But some times without option the table doesn't repair so use the following options.

Options:

QUICK	The quickest, as the data file is not modified.
EXTENDED	Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.
USE_FRM	To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes.

Examples:


mysql>REPAIR TABLE table1 QUICK;

mysql>REPAIR TABLE table1 EXTENDED;

mysql>REPAIR TABLE table1 USE_FRM; 


After execute the repair query ,you can execute the query against the table