mysqlcheck

Check a table

$ mysqlcheck --check [table]

$ mysqlcheck --check [table] --user [username] --password [password]

$ mysqlcheck --repair [table]

$ mysqlcheck --optimize [table]

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

Common Options:
-u user, --user=user
-p[password], --password[=password]
-h host_name, --host=host_name
--all-databases
--analyze
--auto-repair
--check
--optimize
--repair
--quick

--all-databases, -A
    Process all databases accessible by the specified user.

--analyze, -a
    Analyzes the key distribution of tables to improve query performance. Equivalent to ANALYZE TABLE.

--auto-repair
    If a checked table is found to be corrupted, automatically repair it. This option implies --check.

--check, -c
    Checks tables for errors. Equivalent to CHECK TABLE.

--check-only-changed, -C
    Checks only tables that have changed since the last check or that have not been closed properly.

--compress
    Compresses all information sent between the client and the server if both support compression.

--databases, -B
    Treats all name arguments after --databases as database names. Performs the operation on all tables within those databases.

--force, -f
    Continues even if an SQL error occurs during processing a table or database.

--host, -h
    Connects to the MySQL server on the specified host.

--optimize, -o
    Optimizes tables by reclaiming wasted space and defragmenting data files. Equivalent to OPTIMIZE TABLE.

--password[=password], -p[password]
    The password to use when connecting to the server. If =password is omitted, mysqlcheck prompts for a password.

--port, -P
    The TCP/IP port number to use for the connection.

--quick, -q
    For --check, performs a quicker check. For --repair, performs a quicker repair without sorting rows.

--repair, -r
    Repairs corrupted tables. Equivalent to REPAIR TABLE.

--silent, -s
    Suppress output (except for errors). Only prints error messages.

--socket, -S
    The Unix socket file to use for connection (on localhost).

--tables, -t
    Overrides --all-databases or --databases; treats subsequent names as table names. Must be the first option after mysqlcheck.

--user, -u
    The MySQL user name to use when connecting to the server.

--verbose, -v
    Produce more verbose output. Use -vv for even more verbose output.

--version, -V
    Display version information and exit.

mysqlcheck is a command-line utility used for performing maintenance operations on MySQL database tables. Unlike myisamchk, which operates directly on MyISAM table files while the server is stopped, mysqlcheck connects to a running MySQL server. This client-server approach allows it to work with various storage engines, including InnoDB, MyISAM, and others, making it an essential tool for database administrators.

It can be used to check tables for errors, analyze table key distribution to improve query performance, optimize tables by reclaiming wasted space and defragmenting data files, and repair corrupted tables. These operations are crucial for maintaining database health, performance, and data integrity. While it shares functionality with SQL commands like CHECK TABLE, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE, mysqlcheck provides a convenient command-line interface for scripting and automated maintenance tasks.

mysqlcheck requires a running MySQL server to operate, as it connects to the server to perform its tasks.

While mysqlcheck can perform repair operations, especially on MyISAM tables, repairing InnoDB tables using mysqlcheck --repair or the equivalent REPAIR TABLE SQL statement is often a no-op or limited. InnoDB tables are designed for self-healing and crash recovery; severe corruption often requires more drastic measures like restoring from a backup, or using ALTER TABLE ... FORCE or mysqlpump/mysqldump followed by a restore.

Operations like --analyze, --optimize, and --repair typically acquire write locks on tables, which can block other operations and impact application availability, especially on busy production systems. --check operations acquire read locks. Always test these operations in a non-production environment first and schedule them during off-peak hours.

mysqlcheck supports several primary maintenance modes: --check, --analyze, --optimize, and --repair. These options are generally mutually exclusive; if you specify more than one, the last one specified on the command line typically takes precedence, or a combination might execute in a specific order (e.g., --auto-repair implies --check first). It's best practice to run them separately if precise control over each operation is needed.

When providing passwords, avoid using the -ppassword format directly on the command line, as it makes the password visible in system process lists (e.g., ps -ef). The recommended and more secure method is to use -p (without an immediate password) which prompts you to enter the password interactively. Alternatively, use a configuration file for credentials.

mysqlcheck emerged as a necessary utility in the MySQL ecosystem to perform table maintenance through the server, addressing the limitations of direct file-based utilities like myisamchk. As InnoDB gained prominence and became the default storage engine in MySQL 5.5, a server-side utility capable of interacting with InnoDB's transactional nature became even more critical. mysqlcheck provides a unified command-line interface for database administrators to manage tables across different storage engines, integrating seamlessly with the MySQL client-server architecture. Its development reflects the ongoing need for robust and flexible database maintenance tools.

mysql(1), mysqldump(1), mysqladmin(1), myisamchk(1), mysqld(8)