How to Check and Repair MySQL Tables Using Mysqlcheck

sachin

When taking mysqldump, it prompts error mysqldump: Got error: 145: Table './mydb/mytable' is marked as crashed and should be repaired when using LOCK TABLES every time when it encouters a crashed table. and then I have to repair it in order to move ahead. I want to check for all the tables at once instead of one by one.

sachin

When your mysql table gets corrupted, use mysqlcheck command to repair it.
Mysqlcheck command checks, repairs, optimizes and analyzes the tables.

1. Check a Specific Table in a Database

mysqlcheck -c mydb mytable -u root -p
Enter password:
mydb.mytable    OK

2. Check All Tables in a Database

mysqlcheck -c mydb  -u root -p
Enter password:
mydb.mytable1    OK
mydb.mytable2    OK
mydb.mytable3    OK
mydb.mytable4    OK
..

3. Check All Tables and All Databases

mysqlcheck -c  -u root -p --all-databases
Enter password:
mydb1.mytable1    OK
mydb1.mytable2    OK
..
mydb2.mytable1    OK
mydb2.mytable2    OK

4. Additional Useful Mysqlcheck Options
   The following are some of the key options that you can use along with mysqlcheck.

-A, –all-databases Consider all the databases
-a, –analyze Analyze tables
-1, –all-in-1 Use one query per database with tables listed in a comma separated way
–auto-repair Repair the table automatically it if is corrupted
-c, –check Check table errors
-C, –check-only-changed Check tables that are changed since last check
-g, –check-upgrade Check for version dependent changes in the tables
-B, –databases Check more than one databases
-F, –fast Check tables that are not closed properly
–fix-db-names Fix DB names
–fix-table-names Fix table names
-f, –force Continue even when there is an error
-e, –extended Perform extended check on a table. This will take a long time to execute.
-m, –medium-check Faster than extended check option, but does most checks
-o, –optimize Optimize tables
-q, –quick Faster than medium check option
-r, –repair Fix the table corruption