Check and Repair Mysql Tables

 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