December 13, 2018

MySQL mysqlcheck commands

mysqlcheck utility commands in MySQL

mysqlcheck

mysqlcheck --help
mysqlcheck --version

Usage: mysqlcheck [OPTIONS] database [tables]
OR     mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqlcheck [OPTIONS] --all-databases

mysqlcheck --login-path=dba -c testdb
mysqlcheck --login-path=root -r testdb emp
mysqlcheck --login-path=dba -o devdb dept

mysqlcheck --login-path=mydba --check --all-databases --medium-check --auto-repair --compress --force --medium-check --skip-write-binlog --debug-info
mysqlcheck --login-path=local --analyze --databases mydb --compress --skip-write-binlog --debug-info

mysqlcheck --login-path=dba --check --databases devdb
mysqlcheck --login-path=dba --analyze --databases proddb
mysqlcheck --login-path=dba --repair --databases testdb
mysqlcheck --host=linux001 --port=3308 --user=root --password -c proddb

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

Please check MySQL manual for latest information about the above. 
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 -c 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
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

Default options of mysql check, are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlcheck client

The following mysqlcheck options 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,  except for login file.
--defaults-file=#        Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=# Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

  -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.
  --bind-address=name IP address to bind to.
  --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.
  --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[=#]      This is a non-debug version. Catch this and exit.
  --debug-check        This is a non-debug version. Catch this and exit.
  --debug-info        This is a non-debug version. Catch this and exit.
  --default-character-set=name      Set the default character set.
  --default-auth=name Default authentication client-side plugin to use.
  --enable-cleartext-plugin     Enable/disable the clear text authentication plugin.
  -F, --fast          Check only tables that haven't been closed properly.
  --fix-db-names      Fix database names.
  --fix-table-names    Fix table names.
  -f, --force          Continue even if we get an SQL error.
  -e, --extended      If you are using this option with CHECK TABLE, it will ensure that the table is 100 percent consistent, but will take a long time. If you are using this option with REPAIR TABLE, 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. Use --skip-write-binlog when commands should not be sent to replication slaves. (Defaults to on; use --skip-write-binlog to disable.)
  --secure-auth        Refuse client connecting to server if it uses old (pre-4.1.1) protocol. Deprecated. Always TRUE
  -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.
  --plugin-dir=name    Directory for client-side plugins.
  -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.
  --skip-database=name Don't process the database specified as argument
  -S, --socket=name    The socket file to use for connection.

  --ssl-mode=name      SSL connection mode.
  --ssl                Deprecated. Use --ssl-mode instead.  (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert  Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name        CA file in PEM format.
  --ssl-capath=name    CA directory.
  --ssl-cert=name      X509 cert in PEM format.
  --ssl-cipher=name    SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.

  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2
  --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.
  -V, --version        Output version information and exit.

mysqlcheck variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-in-1                              FALSE
auto-repair                        FALSE
bind-address                      (No default value)
character-sets-dir               (No default value)
compress                           FALSE
databases                          FALSE
default-character-set          utf8
default-auth                       (No default value)
enable-cleartext-plugin       FALSE
fast                                   FALSE
fix-db-names                      FALSE
fix-table-names                  FALSE
force                                 FALSE
extended                           FALSE
host                                  (No default value)
write-binlog                       TRUE
secure-auth                       TRUE
plugin-dir                          (No default value)
port                                  3306
quick                                FALSE
silent                               FALSE
skip-database
socket                              /data/mysql/mysql.sock
ssl                                   TRUE
ssl-verify-server-cert         FALSE
ssl-ca                              (No default value)
ssl-capath                        (No default value)
ssl-cert                            (No default value)
ssl-cipher                         (No default value)
ssl-key                            (No default value)
ssl-crl                              (No default value)
ssl-crlpath                        (No default value)
tls-version                        (No default value)
use-frm                            FALSE
user                                (No default value)

Related MySQL Articles: mysqlshow utility commands 
mysqlrplcheck replication check utility

No comments:

Post a Comment