1. Check a Specific Table in a Database
If your application gives an error message saying that a specific table
is corrupted, execute the mysqlcheck command to check that one table.
The following example checks TableName table in DBNAME database.
# mysqlcheck -c DBNAME TableName -u root -p
Enter password:
DBNAME.TableName
OK
You should pass the username/password to the mysqlcheck command. If not,
you’ll get the following error message.
# mysqlcheck -c DBNAME TableName
mysqlcheck: Got error: 1045: Access denied for user
'root'@'localhost' (using password: NO) when trying to connect
Please note that myisamchk command that we discussed a
while back works similar to the mysqlcheck command. However, the advantage of
mysqlcheck command is that it can be executed when the mysql daemon is running.
So, using mysqlcheck command you can check and repair corrupted table while the
database is still running.
2. Check All Tables in a Database
To check all the tables in a particular database, don’t specify the
table name. Just specify the database name.
The following example checks all the tables in the DBNAME2 database.
# mysqlcheck -c DBNAME2 -u root -p
Enter password:
DBNAME2.JBPM_ACTION OK
DBNAME2.JBPM_BYTEARRAY OK
DBNAME2.JBPM_BYTEBLOCK OK
DBNAME2.JBPM_COMMENT OK
DBNAME2.JBPM_DECISIONCONDITIONS OK
DBNAME2.JBPM_DELEGATION OK
DBNAME2.JBPM_EVENT OK
..
3. Check All Tables and All Databases
To check all the tables and all the databases use the “–all-databases”
along with -c option as shown below.
# mysqlcheck -c
-u root -p --all-databases
Enter password:
DBNAME.TableName OK
DBNAME2.JBPM_ACTION OK
DBNAME2.JBPM_BYTEARRAY OK
DBNAME2.JBPM_BYTEBLOCK OK
..
..
mysql.help_category
error :
Table upgrade required. Please do "REPAIR TABLE `help_category`" or
dump/reload to fix it!
mysql.help_keyword
error :
Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or
dump/reload to fix it!
..
If you want to check all tables of few databases, specify the database
names using “–databases”.
The following example checks all the tables in DBNAME and DBNAME2
database.
# mysqlcheck -c
-u root -p --databases DBNAME DBNAME2
Enter password:
DBNAME.TableName OK
DBNAME2.JBPM_ACTION OK
DBNAME2.JBPM_BYTEARRAY OK
DBNAME2.JBPM_BYTEBLOCK OK
..
4. Analyze Tables using Mysqlcheck
The following analyzes TableName table that is located in DBNAME
database.
# mysqlcheck -a DBNAME TableName -u root -p
Enter password:
DBNAME.TableName
Table is already up to date
Internally mysqlcheck command uses “ANALYZE TABLE” command. While
mysqlcheck is executing the analyze command the table is locked and available
for other process only in the read mode.
5. Optimize Tables using Mysqlcheck
The following optimizes TableName table that is located in DBNAME
database.
# mysqlcheck -o DBNAME TableName -u root -p
Enter password:
DBNAME.TableName OK
Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you
delete lot of rows from a table, optimizing it helps to get the unused space
and defragment the data file. This might improve performance on huge tables
that has gone through several updates.
6. Repair Tables using Mysqlcheck
The following repairs TableName table that is located in DBNAME
database.
# mysqlcheck -r DBNAME TableName -u root -p
Enter password:
DBNAME.TableName OK
Internally mysqlcheck command uses “REPAIR TABLE” command. This will
repair and fix a corrupted MyISAM and archive tables.
7. Combine Check, Optimize, and Repair Tables
Instead of checking and repairing separately. You can combine check,
optimize and repair functionality together using “–auto-repair” as shown below.
The following checks, optimizes and repairs all the corrupted table in DBNAME
database.
# mysqlcheck -u root -p --auto-repair -c -o DBNAME
You an 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 DBNAME TableName
Enter password:
DBNAME.TableName
Table is already up to date
User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set
size 0
Non-physical pagefaults 344, Physical pagefaults 0,
Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12, Involuntary context
switches 9
8. 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
