Mysqlcheck command can be used to check, repair, optimize and analysed table in MySQL when table gets corrupted.
# mysqlcheck -c <Database Name> <Table Name> -u <User Name> -p
Example:
# mysqlcheck -c world city -u root -p
Enter password:
world.city OK
Check All Tables in a Database
# mysqlcheck -c <Database Name> -u <User Name> -p
Example:
# mysqlcheck -c world -u root -p
Enter password:
world.city OK
world.country OK
world.countrylanguage OK
Check All Tables and All Databases
# mysqlcheck -c -u root -p --all-databases
If you want to check all tables of few databases, specify the database names using “--databases”.
# mysqlcheck -c -u root -p --databases <Database Name> <Database Name>
Analyze Tables using Mysqlcheck
# mysqlcheck -a <Database Name> <Table Name> -u <User Name> -p
Example:
# mysqlcheck -a world city -u root -p
Enter password:
world.city OK
Optimize Tables using Mysqlcheck
# mysqlcheck -o <Database Name> <Table Name> -u <User Name> -p
Example:
# mysqlcheck -o mysql columns_priv -u root -p
Repair Tables using Mysqlcheck
# mysqlcheck -r <Database Name> <Table Name> -u <User Name> -p
Example:
# mysqlcheck -r world city -u root -p
Combine Check, Optimize, and Repair Tables
The following checks, optimizes and repairs all the corrupted table in a database.
# mysqlcheck -u root -p --auto-repair -c -o <Database Name>
You can also check, optimize and repair all the tables across all your databases using the following command.
# mysqlcheck -u root -p --auto-repair -c -o --all-databases
If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.
# mysqlcheck --debug-info -u root -p --auto-repair -c -o <Database Name> <Table Name>
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