How to Check and Repair MySQL Tables Using Mysqlcheck

1. Check a Specific Table in a Database

# mysqlcheck -c <dbname> <tablename> -u root -p

2. Check All Tables in a Database

# mysqlcheck -c <dbname> -u root -p

3. Check All Tables and All Databases

# mysqlcheck -c  -u root -p --all-databases 

4. Analyze Tables using Mysqlcheck

# mysqlcheck -a <dbname> <tablename> -u root -p 

5. Optimize Tables using Mysqlcheck

# mysqlcheck -o <dbname> <tablename> -u root -p 

6. Repair Tables using Mysqlcheck

# mysqlcheck -r <dbname> <tablename> -u root -p 

7. Combine Check, Optimize, and Repair Tables

# mysqlcheck -u root -p --auto-repair -c -o <dbname> 

8. Check, Optimize, and Repair All Tables from All databases

# mysqlcheck -u root -p --auto-repair -c -o --all-databases 

9. Additional Useful Mysqlcheck Options

-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