mysqlcheck

mysqlcheck is a utility for checking and repairing tables. It presents a command-line interface to the CHECK TABLE, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements. It's somewhat similar to myisamchk but is used while the server is running. mysqlcheck works by sending administrative queries to the server to be executed. This contrasts with myisamchk, which operates directly on table files and thus requires either that you coordinate table access with the server or bring down the server.

mysqlcheck was introduced in MySQL 3.23.38. All mysqlcheck options are supported for MyISAM tables. mysqlcheck can also analyze BDB tables, and, as of MySQL 3.23.40, can check InnoDB tables.

Usage
mysqlcheck can be run in any of three modes:

mysqlcheck [options] db_name [tbl_name] ...
mysqlcheck [options] --databases db_name ...
mysqlcheck [options] --all-databases

In the first case, the named tables in the given database are checked. If no tables are named, mysqlcheck checks all tables in the database. In the second case, all arguments are taken as database names and mysqlcheck checks all tables in each one. In the third case, mysqlcheck checks all tables in all databases.

Standard Options Supported by mysqlcheck
--character-sets-dir --host --socket
--compress --password --user
--debug --pipe --verbose
--default-character-set --port --version
--help --silent

As of MySQL 4, mysqlcheck also supports the standard SSL options.

Options Specific to mysqlcheck
mysqlcheck supports the following options to control how it processes tables. Following this list is a description of the equivalences between these options and the SQL statements to which they correspond.

--all-databases, -A (boolean)

Check all tables in all databases.

--analyze, -a

Perform table analysis by issuing an ANALYZE TABLE statement. (For example, this analyzes the distribution of key values.) The results of the analysis can help the server perform index-based lookups and joins more quickly.

--all-in-1, -1 (boolean)

Without this option, mysqlcheck issues separate queries for each table. This option causes mysqlcheck to group tables by database and name all tables within each database in a single query.

--auto-repair (boolean)

If any tables to be checked are found to have problems, run a second phase to repair them automatically after the check phase has finished.

--check, -c

Issue a CHECK TABLE statement to check for errors. This is the default action if no action is specified explicitly.

--check-only-changed, -C

Check only tables that have changed since they were last checked or that have not been closed properly.

--databases, -B (boolean)

Interpret all arguments as database names and check all tables in each database.

--extended, -e (boolean)

Perform an extended table check. If used with --repair, use a more extensive but slower repair method than is used for --repair by itself.

--fast, -F (boolean)

Check only tables that have not been closed properly.

--force, -f (boolean)

Continue executing even if errors occur.

--medium-check, -m

Perform table checking using a method that is faster than --extended but slightly less thorough. This checking mode should be sufficient for most circumstances.

--optimize, -o

Perform table optimization by issuing an OPTIMIZE TABLE statement.

--quick, -q (boolean)

For table checking, this option skips checking links in the data rows. Used with --repair, this option repairs only the index file and leaves the data file untouched. Giving this option twice is no different than giving it once, in contrast to myisamchk, which does behave differently when the option is specified twice.

--repair, -r

Perform table repair by issuing a REPAIR TABLE statement. This repair mode should correct most problems except the occurrence of duplicate values in an index that should be unique.

--tables

Override --databases.

--use-frm (boolean)

Used with --repair to perform a table repair operation that uses the .frm file to interpret the data file and rebuild the index file. This option can be used when the index file has been lost or corrupted. It was introduced in MySQL 4.0.5.

The relationship between mysqlcheck's options and the SQL statements that it issues is described by the following tables, which show the statements that correspond to mysqlcheck's options.

Table checking options (MyISAM and InnoDB tables only):

Option Corresponding Statement
--check CHECK TABLE tbl_list
--check-only-changed CHECK TABLE tbl_list CHANGED
--extended CHECK TABLE tbl_list EXTENDED
--fast CHECK TABLE tbl_list FAST
--medium-check CHECK TABLE tbl_list MEDIUM
--quick CHECK TABLE tbl_list QUICK

Table analysis options (MyISAM and BDB tables only);

Option Corresponding Statement
--analyze ANALYZE TABLE tbl_list

Table repair options (MyISAM tables only):

Option(s) Corresponding Statement
--repair REPAIR TABLE tbl_list
--repair--quick REPAIR TABLE tbl_list QUICK
--repair--extended REPAIR TABLE tbl_list EXTENDED
--repair--use-frm REPAIR TABLE tbl_list USE_FRM

Table optimization options (MyISAM tables only):

Option Corresponding Statement
--optimize OPTIMIZE TABLE tbl_list