How to recover Corrupted Table [MySql]

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

They  can get corrupted due to a variety of reasons,  we can identify the errors from 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 let’s start by logging into the server by SSH, and following 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 the “myisamchk” utility to repair corrupted tables provided that the MySql service is turned off.

That’s all Folks..!!