Debug School

rakesh kumar
rakesh kumar

Posted on

mysqlcheck — A Table Maintenance Program

mysqlcheck
learn mysqlcheck
mysqlcheck
mysqlcheck

How to Check and Repair MySQL Tables Using Mysqlcheck

mysqlcheck

  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 fullscreen mode Exit fullscreen mode 
Enter password:
thegeekstuff.employee    OK
Enter fullscreen mode Exit fullscreen mode

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

# mysqlcheck -c thegeekstuff employee
Enter fullscreen mode Exit fullscreen mode 
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
Enter fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

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

  1. 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 fullscreen mode Exit fullscreen mode

Enter password:
thegeekstuff.employee OK
alfresco.JBPM_ACTION OK
alfresco.JBPM_BYTEARRAY OK
alfresco.JBPM_BYTEBLOCK OK
..
..

mysql.help_category
Enter fullscreen mode Exit fullscreen mode 
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
Enter fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

Enter password:
thegeekstuff.employee OK
alfresco.JBPM_ACTION OK
alfresco.JBPM_BYTEARRAY OK
alfresco.JBPM_BYTEBLOCK OK
..

  1. Analyze Tables using Mysqlcheck The following analyzes employee table that is located in thegeekstuff database. 
# mysqlcheck -a thegeekstuff employee -u root -p
Enter fullscreen mode Exit fullscreen mode

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.

  1. Optimize Tables using Mysqlcheck The following optimizes employee table that is located in thegeekstuff database. 
# mysqlcheck -o thegeekstuff employee -u root -p
Enter fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

Enter password:
thegeekstuff.employee OK
Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.

  1. 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Using mysqlcheck for MySQL Database Tables

mysqlcheck for MySQL Database Tables
Important Note: Before we proceed with table check, repair, or analysis operations, it’s best advised to create a backup of your table(s), for some circumstances might lead to a potential data loss.

As a result, we’ve split this tutorial into four sections for your convenience:

Part 1: Creating a MySQL Database Back-Up
Part 2: Running mysqlcheck Command
Part 3: Using mysqlcheck in Compound Commands
Part 4: mysqlcheck Command Modifications
Part 1: Creating a MySQL Database Back-Up
Enter fullscreen mode Exit fullscreen mode

To create a backup of all your existing MySQL databases, follow these steps:

Step 1: Log in to your MySQL server using Secure Shell (SSH).

Step 2: Stop MySQL server using the appropriate command based on your Linux distribution:

For CentOS and Fedora, type:

service mysqld stop
Enter fullscreen mode Exit fullscreen mode

For Debian and Ubuntu, type:

service mysql stop
Enter fullscreen mode Exit fullscreen mode

Step 3: Input the following command to copy all of your databases to a directory name based on the current time.

cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)
Enter fullscreen mode Exit fullscreen mode

Step 4: Restart MySQL server with the command appropriate for your Linux distribution:

For CentOS and Fedora, type:

service mysqld start
Enter fullscreen mode Exit fullscreen mode

For Debian and Ubuntu, type:

service mysql start
Part 2: Running mysqlcheck Command
To use the mysqlcheck table command and check tables, follow these steps:

Step 1: A*s an administrator, change your directory to MySQL Data Directory*.

cd /var/lib/mysql
Enter fullscreen mode Exit fullscreen mode

Step 2: Now type in the mysqlcheck command to check for an existing table in a database. In our example, we are checking for a table called “email” under the database “customers”.

$ mysqlcheck -c customers email
Enter fullscreen mode Exit fullscreen mode

customers.email OK
Notice our result. If a table passes the check, mysqlcheck displays OK for the table.

Not only this, mysqlcheck command can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) table within your databases. The -c, -r, -a, and -o options work exclusively.

Some of the options (like -e (–extended) or -q (–quick)) can also be used at the same time but not all options are supported by all storage engines.

Running mysqlcheck to Analyze a Table in MySQL Database
As an example, the following command analyzes the “email” table within our “customers” database:

$ mysqlcheck -a customers email
Enter fullscreen mode Exit fullscreen mode

customers.email OK
Running mysqlcheck to Repair Tables in a MySQL Database
The following command repairs all tables in the “customers” and “leads” databases:

$ mysqlcheck -r --databases customers leads
Enter fullscreen mode Exit fullscreen mode

Note: If you see a note stating: The storage engine for the table doesn’t support repair it means that you are doing REPAIR on an InnoDB.

Running mysqlcheck to Optimize Tables in a MySQL Database
The following mysqlcheck database command optimizes all tables in all your MySQL databases.

$ mysqlcheck -o --all-databases
For user reference, this is a table showcasing the most used options for the mysqlcheck database command.

Option Description

-c, –check    Check the tables for errors
-a, –analyze  Analyze the tables
-o, –optimize Optimize the tables
-r, –repair   Perform a repair that can fix almost anything except unique keys that are not unique
–auto-repair  If a checked table is corrupted, automatically fix it
-A, –all-databases    Check all tables in all databases. This is the same as –databases with all databases selected
-B, –databases    Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names
–tables   Overrides the –databases or -B option such that all name arguments following the option are regarded as table names
-g, –check-upgrade    Check tables for version-dependent changes. It may be used with –auto-repair to correct tables requiring version-dependent updates
–compress Compress all information sent between client and server
–debug-info   Print debugging information, memory, and CPU statistics when the program exits
-e, -–extended    Check and repair tables
-q, –quick    The fastest method of checking
-?, –help Display a help message and exit
Enter fullscreen mode Exit fullscreen mode

A more extensive list of other mysqlcheck database command options can be found on this page.

Likewise, these mysqlcheck table/database options can be combined together to perform a joint operation. Have a look at how this can be performed in the following section.

Part 3: Using mysqlcheck in Compound Commands
The mysqlcheck command-line utility can be extended for giving compound commands. For instance, let’s assume a case where there is a need to repair and optimize the “email” table from our previously stated “customer” database.

mysqlcheck table command options like -c (check), -r (repair), -a (analyze), and -o (optimize) options work exclusively and can be used concurrently in the same mysqlcheck command.

Running mysqlcheck to Optimize and Repair Tables in a MySQL Database
The following mysqlcheck command checks, optimizes, and auto-repairs all corrupted tables in the “customer” database. The auto repair option automatically fixes a checked table if found corrupted.

$ mysqlcheck --auto-repair -o customers
Enter fullscreen mode Exit fullscreen mode

And the following mysqlcheck command optimizes and auto-repairs all tables in all your MySQL databases.

$ mysqlcheck --auto-repair -o --all-databases
Part 4: mysqlcheck Command Modifications
The command mysqlcheck can be altered, changing its default behavior from checking tables (–check) to repairing or optimizing tables. This can be done by changing the binary “check” and replacing it with “repair”, or “analyze”, or “optimize”.

These are the commands you get after substitution:

Command Meaning

mysqlrepair The default option is –repair
mysqlanalyze    The default option is –analyze
mysqloptimize   The default option is –optimize
Enter fullscreen mode Exit fullscreen mode

All these commands when invoked would perform the same operation as mysqlcheck -[option] when used.

If you would like to learn about MySQL database export command-line utility, see our blog on MySQL Export Database Command Line: 3 Easy Methods. If you would like to know more about MySQL Analytics tools, visit our other informative blog here- MySQL Analytics Tools: A Quick Guide.

Conclusion
mysqlcheck table/database commands are ideal for automated optimizations of MySQL databases and tables. The more your database expands and the number of tables increases, the more likely it is that your tables/databases will encounter mistakes from time to time. In those circumstances, mysqlcheck can be a lifesaver.

Managing growing databases and the number of associated processes like business analytics isn’t an easy job. We find numerous business teams trapped and trying to figure out how to examine their data in a timely and efficient manner. One way of doing this is migrating your data to a Cloud-based Data Warehouse. But what if we told you that you can manage all your source connections from a single place?

Latest comments (0)