Check a Specific Table in a Database
If your
application throws an error message saying that a specific table is corrupted,
execute the mysqlcheck command to check it.
The
following example checks t1 table in test database.
#
mysqlcheck -c test t1 -u root -p
Enter
password:
test.t1 OK
Kindly Note, that myisamchk
command works similar to the
mysqlcheck command. 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.
Check All Tables in a Database
To
check all the tables in a particular database follow this
The
following example checks all the tables in the test database.
#
mysqlcheck -c test -u root -p
Enter
password:
test.t1 OK
test.t2 Ok
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:
test.employee OK
live.JBPM_ACTION OK
dev.emp
error : Table emp required. Please do
"REPAIR TABLE `emp`" or dump/reload to fix it!
Check all tables of few databases
#
mysqlcheck -c -u root -p --databases test live
Enter
password:
test.employee OK
live.student OK
Analyze Tables using
Mysqlcheck
#
mysqlcheck -a test t1 -u root -p
Enter
password:
test.t1 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
Optimize Tables using Mysqlcheck
#
mysqlcheck -o test t1 -u root -p
Enter
password:
test.t1 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.
Repair Tables using Mysqlcheck
#
mysqlcheck -r test t1 -u root -p
Enter
password:
test.t1 OK
Internally
mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a
corrupted MyISAM and archive tables.
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.
#
mysqlcheck -u root -p --auto-repair -c -o test
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
add the
–debug-info as shown below for getting more information
#
mysqlcheck --debug-info -u root -p --auto-repair -c -o test t1
Enter
password:
test.t1 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
