Recover Corrupted Table [MySql]

Why does the database crash!? What can we do if it does? A database corruption can be due to many scenarios, like when a query does not find a table where its supposed to or when the query comes back with incorrect data.

They  can get corrupted due to a various of reasons,  we can to identify the errors form the relevant logs files, and you may find some entries like this,

  • Incorrect key file for table: ‘…’. Try to repair it
  • 126 = Index file is crashed
  • 127 = Record-file is crashed
  • 134 = Record was already deleted (or record file crashed)
  • 144 = Table is crashed and last repair failed
  • 145 = Table was marked as crashed and should be repaired

So how do we repair those tables which are corrupted?  First we need to know the name of those tables in order to proceed.

You can run the command “mysqlcheck -u root -p  –all-databases” to find all corrupted databases.

So lets start by logging into the server by SSH, and follow these steps

  1. [root@test ~]# mysql -u root -p (type in the password when prompted) 
  2. mysql> USE <database_name>; 
  3. mysql> CHECK TABLE <table_name>; (You can see that the output as NOT OK, go to step 3, else the table is fine) 
    mysql> check table MySql_table;
+--------------------------------+-------+----------+----------+
| Table                          | Op    | Msg_type | Msg_text |
+--------------------------------+-------+----------+----------+
| database.MySql_table | check | status   | OK       | OK
+--------------------------------+-------+----------+----------+

     

  4. mysql> REPAIR TABLE <table_name>; (which will provide an output as below) 
    mysql> repair table MySql_table;
+--------------------------------+-------+----------+----------+
| Table                          | Op    | Msg_type | Msg_text |
+--------------------------------+-------+----------+----------+
| database.MySql_table | repair | status   | OK       | OK
+--------------------------------+-------+----------+----------+

 

So that’s how you repair a corrupted MySql table. This way of repairing tables can be performed while the MySql service is up and running. You can use “myisamchk” utility to repair corrupted tables provided that the MySql service is turned off.

That’s all Folks..!!