How to Check and Repair MySQL Tables Using Mysqlcheck

by Ramesh Natarajan on December 21, 2011

When your mysql table gets corrupted, use mysqlcheck command to repair it.

Mysqlcheck command checks, repairs, optimizes and analyzes the tables.

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 employee table in thegeekstuff database.

# mysqlcheck -c thegeekstuff employee -u root -p
Enter password:
thegeekstuff.employee    OK

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

# mysqlcheck -c thegeekstuff employee
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 alfresco database.

# mysqlcheck -c alfresco  -u root -p
Enter password:
alfresco.JBPM_ACTION                               OK
alfresco.JBPM_BYTEARRAY                            OK
alfresco.JBPM_BYTEBLOCK                            OK
alfresco.JBPM_COMMENT                              OK
alfresco.JBPM_DECISIONCONDITIONS                   OK
alfresco.JBPM_DELEGATION                           OK
alfresco.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:
thegeekstuff.employee                              OK
alfresco.JBPM_ACTION                               OK
alfresco.JBPM_BYTEARRAY                            OK
alfresco.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 thegeekstuff and alfresco database.

# mysqlcheck -c  -u root -p --databases thegeekstuff alfresco
Enter password:
thegeekstuff.employee                              OK
alfresco.JBPM_ACTION                               OK
alfresco.JBPM_BYTEARRAY                            OK
alfresco.JBPM_BYTEBLOCK                            OK
..

4. Analyze Tables using Mysqlcheck

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

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

# mysqlcheck -o thegeekstuff employee -u root -p
Enter password:
thegeekstuff.employee         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 thegeekstuff database.

# mysqlcheck -r thegeekstuff employee -u root -p
Enter password:
thegeekstuff.employee        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 thegeekstuff database.

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

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 thegeekstuff employee
Enter password:
thegeekstuff.employee  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

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 9 comments… read them below or add one }

1 Karthik.P.R December 21, 2011 at 12:34 pm

That was a good information.

Small Info
1) Increasing the key_buffer_size will increases the repair speed.

2 Colin Brace October 13, 2012 at 3:39 am

This page is really useful. One comment:
when I run this
$ mysqlcheck -u root -p –auto-repair -c -o –all-databases
it returns an error message:
Error: mysqlcheck doesn’t support multiple contradicting commands.
$ mysql –version
mysql Ver 14.14 Distrib 5.5.28, for FreeBSD9.0 (amd64) using 5.2
Thanks.

3 hlosukwakha January 14, 2013 at 12:21 am

The options -c, -r, -a, and -o are exclusive to each other, which means that only the last option will be used if several are specified. In your query you used -c and -o together.

4 Scott April 2, 2013 at 11:07 am

Thanks for the article.

You may want to add that the commands are run from the command prompt and that you have to be in the path to the mysqlcheck file.

5 Bobby April 30, 2013 at 1:57 pm

Thank you very much. I’ve been struggling to clean up a few databases and SSH is not my strong area. I normally do this through phpMyAdmin but the database is too big and it times out. This is the only post I could find that clearly explained, and gave options. I really appreciate you taking the time to put it together!

6 Marc May 2, 2013 at 2:36 pm

Hi
I received just the FRM files from a database that I need to recover. I dont need the data, just the structure would be fine so the frm file should be sufficient.
I have been struggling with different repair options to fix the structure of the db but I keep getting.
Error : Table ‘intray20121206B.session’ doesn’t exist
status : Operation failed
I dont understand the error – it is obvious that the table does not exist that´s why I am using the repair option. (Even the repair table USE_FRM return the error). I used a HEX editor and I detected “InnoDB” and some structure information but 90% of the file are 00.
Do you have any idea what I could try? Any help would be greatly appreciated.

7 shiz June 20, 2013 at 7:43 pm

Very useful stuff. Thanks for the info.

8 Steve October 15, 2013 at 5:42 pm

Good info. Thanks! Saved my database.

9 Rahul July 25, 2014 at 5:39 pm

awesome info, thanks so much

Leave a Comment

Previous post:

Next post: