MySQL: Check, Optimize and Repair Tables using Mysqlcheck

Recently, our MySQL MyISAM table corrupted due to certain reason.

Using mysqlcheck command, we can optimize, repair and analyse the tables. (For MyISAM Engine)

First, check a specific table:
You can execute this command by giving DatabaseName and Table name.

1 2 3

mysql> mysqlcheck -c TestDB TestTable -u root -p Enter Password:**** testdb.testtable       OK

Check all the tables of a Database:
Using below command, you can also check all the tables of a Database.

1 2 3 4 5 6

mysql> mysqlcheck -c TestDB -u root -p Enter password:**** testdb.tbl_first     OK testdb.tbl_sec       OK testdb.tbl_third     OK testdb.testtable     OK

Check all the tables of a multiple Database:

1 2 3 4 5 6

mysql> mysqlcheck -c -u root -p --databases testdb mydb Enter password:**** testdb.tbl_first     OK testdb.tbl_sec       OK testdb.tbl_third     OK mydb.tbl_first       OK

Check all the tables of all databases:

1	mysql> mysqlcheck -c  -u root -p --all-databases

Analyze specific table of a Database:
When you execute ANALYZE, it lock the whole table and you can access the table only for read purpose.

1 2 3

mysql>mysqlcheck -a dbname tablename -u root -p Enter password:**** dbname.tablename     OK

Optimize specific table of a Database:
When you perform bulk deletion from table, OPTIMIZE will recover all unused spaces which generated by deletion.

1 2 3

mysql>mysqlcheck -o dbname tablename -u root -p Enter password:**** dbname.tablename     OK

Repair specific table of a Database:
Below command repairs a corrupted MyISAM table.

1 2 3

mysql>mysqlcheck -r dbname tablename -u root -p Enter password:**** dbname.tablename     OK