The Geek Diary

Understanding mysqlcheck and myisamchk utilities

by

mysqlcheck Client Program

In some cases, mysqlcheck is more convenient than issuing SQL statements directly. For example, if you provide it with a database name as its argument, mysqlcheck determines what tables the database contains and issues statements to process them all. You do not need to provide explicit table names as arguments. Also, because mysqlcheck is a command-line program, you can easily use it in operating system jobs that perform scheduled maintenance.

mysqlcheck is a command-line client that checks, repairs, analyzes, and optimizes tables. It works with InnoDB, MyISAM, and ARCHIVE tables. It performs three levels of checking:

  1. Table-specific
  2. Database-specific
  3. All databases

Some mysqlcheck maintenance options:

  • –analyze: Perform an ANALYZE TABLE.
  • –check: Perform a CHECK TABLE (default).
  • –optimize: Perform an OPTIMIZE TABLE.
  • –repair: Perform a REPAIR TABLE.

By default, mysqlcheck interprets its first non-option argument as a database name and checks all the tables in that database. If any other arguments follow the database name, it treats them as table names and checks just those tables. Oracle recommends that you run mysqlcheck with no
options first, and then run again if repairs are needed.

Some mysqlcheck modification options:

  • –repair –quick: Attempt a quick repair.
  • –repair: Repair normally (if quick repair fails).
  • –repair –force: Force a repair.

mysqlcheck examples:

shell> mysqlcheck --login-path=admin world_innodb 
shell> mysqlcheck -uroot -p mysql user --repair 
shell> mysqlcheck -uroot -p --all-databases 
shell> mysqlcheck --login-path=admin --analyze --all-databases

The mysqlcheck examples shown above demonstrate the following:

  • The first of the commands (with options) checks only the City and Country tables in theworld_innodb database.
  • In the example with the –databases (or -B) option, mysqlcheck interprets its arguments as database names and checks the tables in the world_innodb and test databases.
  • With the –all-databases (or -A) option, mysqlcheck checks all tables in all databases.

myisamchk Utility

Conceptually, myisamchk is similar in purpose to mysqlcheck. However, myisamchk does not communicate with the MySQL server. Instead, it accesses the table files directly. It is a non-client utility that checks MyISAM tables. It is similar to mysqlcheck with some of the following differences:

  • It can enable or disable indexes.
  • It accesses table files directly rather than through the server.
  • This avoids concurrent table access.

Perform table maintenance while avoiding concurrent table access with myisamchk:
1. Ensure that the server does not access the tables while they are being worked on. One way to guarantee this is to lock the tables or stop the server.
2. From a command prompt, change location into the database directory where the tables are located. This is the subdirectory of the server’s data directory that has the same name as the database containing the tables to be checked. (The reason for changing location is to make it easier to refer to the table files. This step can be skipped, but myisamchk has to include the directory where the tables are located.)
3. Invoke myisamchk with options indicating the operation to be performed, followed by arguments that name the tables on which myisamchk should operate. Each of these arguments can be either a table name or the name of the table’s index file. An index file name is the same as the table name, plus an .MYI suffix. Therefore, a table can be referred to either as table_name or as table_name.MYI.
4. Restart the server.

Note: Try –recover first, because –safe-recover is much slower.

Some myisamchk options:

  • –recover: Repair a table.
  • –safe-recover: Repair a table that –recover cannot repair.

myisamchk examples:

shell> myisamchk /var/lib/mysql/mysql/help_topic 
shell> myisamchk help_category.MYI
shell> myisamchk --recover help_keyword

Options for mysqlcheck and myisamchk

Options to control the type of maintenance performed:

mysqlcheck and myisamchk options

mysqlcheck and myisamchk both take many options to control the type of table maintenance operation performed. The table in the slide summarizes some of the more commonly used options, most of which are understood by both programs. Where that is not the case, it is noted in the relevant option description.

  • –analyze: Analyze the distribution of key values in the table. This can improve performance of queries by speeding up index-based lookups.
  • –auto-repair: Repair tables automatically if a check operation discovers problems.
  • –check or -c: Check tables for problems. This is the default action if no other operation is specified.
  • –check-only-changed or -C: Skip table checking except for tables that have been changed since they were last checked or tables that have not been properly closed. The latter condition might occur if the server crashes while a table is open.
  • –fast or -F: Skip table checking except for tables that have not been properly closed.
  • –extended, –extend-check, or -e: Run an extended table check. For mysqlcheck, when this option is given in conjunction with a repair option, a more thorough repair is performed than when the repair option is given alone. That is, the repair operation performed by –repair –extended is more thorough than the operation performed by –repair.
  • –medium-check or -m: Run a medium table check.
  • –quick or -q: For mysqlcheck, –quick without a repair option causes only the index file to be checked, leaving the data file alone. For both programs, –quick in conjunction with a repair option causes the program to repair only the index file, leaving the data file alone.
  • –repair, –recover, or -r: Run a table repair operation.

You May Also Like