mysqlcheck
mysqlcheck is a maintenance tool that allows you to check, repair, analyze and optimize multiple tables at once from the command line.
Usage: ./client/mysqlcheck [OPTIONS] database [tables] OR ./client/mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...] OR ./client/mysqlcheck [OPTIONS] --all-databases
mysqlcheck can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) tables. Some of the options (like -e or -q) can be used at the same time. Not all options are supported by all storage engines.
The options -c, -r, -a, and -o are exclusive to each other, which means that the last option will be used, if several was specified.
The option --check will be used by default, if none was specified. You can change the default behavior by making a symbolic link, or copying this file somewhere with another name, the alternatives are:
| mysqlrepair | The default option will be -r (--repair) |
| mysqlanalyze | The default option will be -a (--analyze) |
| mysqloptimize | The default option will be -o (--optimize) |
The following groups are read from the my.cnf files: [mysqlcheck] and [client].
The following options to handle option files may be given as the first argument:
| --print-defaults | Print the program argument list and exit. |
| --no-defaults | Don't read default options from any option file. |
| --defaults-file=# | Only read default options from the given file #. |
| --defaults-extra-file=# | Read this file after the global files are read. |
Main arguments are:
| -A, --all-databases | Check all the databases. This is the same as --databases with all databases selected. |
| -a, --analyze | Analyze given tables. |
| -1, --all-in-1 | Instead of issuing one query for each table, use one query per database, naming all tables in the database in a comma-separated list. |
| --auto-repair | If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked, if corrupted ones were found. |
| --character-sets-dir=name | Directory for character set files. |
| -c, --check | Check table for errors. |
| -C, --check-only-changed | Check only tables that have changed since last check or haven't been closed properly. |
| -g, --check-upgrade | Check tables for version-dependent changes. May be used with --auto-repair to correct tables requiring version-dependent updates. Used when upgrading |
| --compress | Use compression in server/client protocol. |
| -B, --databases | Check several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names. |
| -#, --debug[=name] | Output debug log. Often this is 'd:t:o,filename'. |
| --debug-check | Check memory and open file usage at exit. |
| --debug-info | Print some debug info at exit. |
| --default-character-set=name | Set the default character set. |
| -F, --fast | Check only tables that haven't been closed properly. |
| --fix-db-names | Fix database names. Used when upgrading |
| --fix-table-names | Fix table names. Used when upgrading |
| -f, --force | Continue even if we get an SQL error. |
| -e, --extended | If you are using this option with --check, it will ensure that the table is 100 percent consistent, but will take a long time. If you are using this option with --repair, it will force using old slow repair with keycache method, instead of much faster repair by sorting. |
| -?, --help | Display this help message and exit. |
| -h, --host=name | Connect to host. |
| -m, --medium-check | Faster than extended-check, but only finds 99.99 percent of all errors. Should be good enough for most cases. |
| --write-binlog | Log ANALYZE, OPTIMIZE and REPAIR TABLE commands. Enabled by default; use --skip-write-binlog when commands should not be sent to replication slaves. |
| -o, --optimize | Optimize table. |
| -p, --password[=name] | Password to use when connecting to server. If password is not given, it's solicited on the tty. |
| -P, --port=# | Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). |
| --protocol=name | The protocol to use for connection (tcp, socket, pipe, memory). |
| -q, --quick | If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for wrong links. This is the fastest check. If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table. |
| -r, --repair | Can fix almost anything except unique keys that aren't unique. |
| -s, --silent | Print only error messages. |
| -S, --socket=name | The socket file to use for connection. |
| --ssl | Enable SSL for connection (automatically enabled with other flags). Disable with --skip-ssl. |
| --ssl-ca=name | CA file in PEM format (check OpenSSL docs, implies --ssl). |
| --ssl-capath=name | CA directory (check OpenSSL docs, implies --ssl). |
| --ssl-cert=name | X509 cert in PEM format (implies --ssl). |
| --ssl-cipher=name | SSL cipher to use (implies --ssl). |
| --ssl-key=name | X509 key in PEM format (implies --ssl). |
| --ssl-verify-server-cert | Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. |
| --tables | Overrides option --databases (-B). |
| --use-frm | When used with REPAIR, get table structure from .frm file, so the table can be repaired even if .MYI header is corrupted. |
| -u, --user=name | User for login if not current user. |
| -v, --verbose | Print info about the various stages. You can give this option several times to get even more information. |
| -V, --version | Output version information and exit. |
To see the default values for the options and also to see the arguments you get from configuration files you can do:
./client/mysqlcheck --print-defaults ./client/mysqlcheck --help
When running mysqlcheck with --auto-repair (as done by mysql_upgrade), mysqlcheck will first check all tables and then in a separate phase repair those that failed the check.
mysqlcheck --all-databases will ignore the MySQL internal log tables 'general_log' and 'slow_log' as these can't be checked, repaired or optimized.
