Checking, analyzing,optimizing and Repairing MySQL Tables through MySQLcheck

When a MySQL table got corrupted, We can use mysqlcheck command to repair it. Not only repair, mysqlcheck provides a excellent way to checks, repairs, optimizes and analyzes the tables. 

Lets see How it works (Ofcourse, Some examples included) 

1. Check a Specific Table in a Database

While being a system admin who maintains one of leading web hosting servers, I often came across Table crashes mostly with social engine, Joomla etc.. When a table says or feels like some tables are corrupted, I do run a mysqlcheck command to check that ones.

The following example checks “xyz” table in teksupport database.

# mysqlcheck -c teksupport xyz -u root –p    
Enter password:
teksupport.xyz    OK

You should pass the username/password to the mysqlcheck command. If not, you’ll get the following error message.

# mysqlcheck -c teksupport abc
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

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 alfresco database.

# mysqlcheck -c teksupport -u root -p
Enter password:
teksupport.ABC_TS2                            OK
teksupport.ABC_TS1                            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:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    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 teksupport and techie database.

# mysqlcheck -c  -u root -p --databases teksupport techie
Enter password:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK 
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    OK 
..

4. Analyze Tables using Mysqlcheck

The following analyzes employee table that is located in teksupport database.

# mysqlcheck -a teksupport XYZ1 -u root -p
Enter password:
teksupport.employee   Table is already up to date

Internally mysqlcheck command uses “ANALYZE TABLE” command. When mysqlcheck is executing, the analyze command the table will be locked and available for other process only in the read mode.i.e. No changes can be made on the data.

5. Optimize Tables using Mysqlcheck

The following optimizes employee table that is located in teksupport database.

# mysqlcheck -o teksupport XYZ1 -u root -p
Enter password:
teksupport.XYZ1               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 employee table that is located in teksupport database.

# mysqlcheck -r teksupport EMP1 -u root -p
Enter password:
teksupport.XYZ1          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 teksupport database.

# mysqlcheck -u root -p --auto-repair -c -o teksupport

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 teksupport EMP1
Enter password:
teksupport.EMP1   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 456, 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