How to Repair a Mysql Table

Repairing corrupted mysql tables using myisamchk / mysqlcheck / repair

 

1. myisamchk

 myisam is the default storage engine for mysql database. There is a chance of myisam tables gets corrupted easily. myisamchk helps to identify and fix corrupted tables in myisam.

when table created in mysql it will create different files under mysql some of them are *.MYD (mydata ) file to store data, *.MYI (myindex) to store the index and *.frm to store table format.

you have to shutdown mysqld before performing the repair 

Checking all corrupted tables using myisamchk

# myisamchk -c /var/lib/mysql/*.MYI >> /tmp/log.txt

redirect the output to a file then it will display only corrupted table names on the screen. log.txt contains info about all tables including good ones.

Repair the corrupted table using myisamchk

# myisamchk -r /var/lib/mysql/*.MYI

2.mysqlcheck

It is a client performs table maintenance: It checks, repairs, optimizes, or analyzes tables etc. It checks all tables and repair them if necessary.

both mysqlcheck and myisamchk are similar, but works differently. The main difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to perform table maintenance.

Checking tables using mysqlcheck

mysql> mysqlcheck [options] db_name tbl_name 
mysql> mysqlcheck [options] --databases  db_name 
mysql> mysqlcheck [options] --all-databases

some options include  --auto-repair,--check -c

Repair tables with mysqlcheck

# mysqlcheck -uroot -p -r tablename;
3.REPAIR 

# repair table tablename;