MySQL的命令工具（持续积累更新）

随着学习的深入，会发现MySQL也有很多命令工具方便我们的开发，这篇文章就用来记录我学习过程中对MySQL工具的一些积累：

一、mysqlcheck

MySQL mysqlcheck客户端主要用来检查（check）、修复（repair）、分析（analyze）、优化（optimize）表。使用mysqlcheck的好处是不需要停止服务器来检查或修复表，mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABLE的方式。
1.语法

mysqlcheck -u username -p password [-c/-r/-a/-o] dbname [tablename]
# -c   check table tablename(检查表)
# -r   repair table tablename(修复表)
# -a   analyze table tablename(分析表)
# -o   optimize table tablename(优化表)

2.官方说明
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 consult the 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.

这个程序可以用来检查(-c、-m、-c)、修复(-r)、分析(-a)或优化(-o)表。有些选项(如-e或-q)可以同时使用。并非所有存储引擎都支持所有选项。请查阅MySQL手册以获得上述最新信息。选项-c、-r、-a和-o是互斥的，这意味着如果指定了几个选项，将使用最后一个选项。

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

如果没有指定，则默认使用-c选项。你可以通过创建一个符号链接来改变默认的行为，或者用另一个名字复制这个文件，备选方案是:
mysqlrepair:默认选项是-r
mysqlanalyze:默认选项是a
mysqloptimize:默认选项是-o

3.用法
mysqlcheck [OPTIONS] database [tables]
OR mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3…]
OR mysqlcheck [OPTIONS] --all-databases

4.参数说明
The following 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
–server-public-key-path=name
File path to the server public RSA key in PEM format.
–get-server-public-key
Get server public key
–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.

二、myisamchk

1.语法

myisamchk  [option] tablename[.frm]
#最后可以是表名，也可以是表结构文件

2.官方描述
myisamchk Ver 2.7 for Linux at x86_64
Description, check and repair of MyISAM tables.Used without options all tables on the command will be checked for errors
Usage: myisamchk [OPTIONS] tables[.MYI]

适用于x86_64的Linux myisamchk Ver 2.7
描述，检查和维修MyISAM表。命令上的所有表将被检查是否有错误。
用法：myisamchk [OPTIONS] tables[.MYI]

3.参数说明
Global options:
-H, --HELP Display this help and exit.
-?, --help Display this help and exit.
-t, --tmpdir=path Path for temporary files. Multiple paths can be
specified, separated by colon (😃, they will be used
in a round-robin fashion.
-s, --silent Only print errors. One can use two -s to make
myisamchk very silent.
-v, --verbose Print more information. This can be used with
–description and --check. Use many -v for more verbosity.
-V, --version Print version and exit.
-w, --wait Wait if table is locked.

Check options (check is the default action for myisamchk):
-c, --check Check table for errors.
-e, --extend-check Check the table VERY throughly. Only use this in
extreme cases as myisamchk should normally be able to
find out if the table is ok even without this switch.
-F, --fast Check only tables that haven’t been closed properly.
-C, --check-only-changed
Check only tables that have changed since last check.
-f, --force Restart with ‘-r’ if there are any errors in the table.
States will be updated as with ‘–update-state’.
-i, --information Print statistics information about table that is checked.
-m, --medium-check Faster than extend-check, but only finds 99.99% of
all errors. Should be good enough for most cases.
-U --update-state Mark tables as crashed if you find any errors.
-T, --read-only Don’t mark table as checked.

Repair options (When using ‘-r’ or ‘-o’):
-B, --backup Make a backup of the .MYD file as ‘filename-time.BAK’.
–correct-checksum Correct checksum information for table.
-D, --data-file-length=# Max length of data file (when recreating data
file when it’s full).
-e, --extend-check Try to recover every possible row from the data file
Normally this will also find a lot of garbage rows;
Don’t use this option if you are not totally desperate.
-f, --force Overwrite old temporary files.
-k, --keys-used=# Tell MyISAM to update only some specific keys. # is a
bit mask of which keys to use. This can be used to
get faster inserts.
–max-record-length=#
Skip rows bigger than this if myisamchk can’t allocate
memory to hold it.
-r, --recover Can fix almost anything except unique keys that aren’t
unique.
-n, --sort-recover Forces recovering with sorting even if the temporary
file would be very big.
-p, --parallel-recover
Uses the same technique as ‘-r’ and ‘-n’, but creates
all the keys in parallel, in different threads.
-o, --safe-recover Uses old recovery method; Slower than ‘-r’ but can
handle a couple of cases where ‘-r’ reports that it
can’t fix the data file.
–character-sets-dir=…
Directory where character sets are.
–set-collation=name
Change the collation used by the index.
-q, --quick Faster repair by not modifying the data file.
One can give a second ‘-q’ to force myisamchk to
modify the original datafile in case of duplicate keys.
NOTE: Tables where the data file is currupted can’t be
fixed with this option.
-u, --unpack Unpack file packed with myisampack.

Other actions:
-a, --analyze Analyze distribution of keys. Will make some joins in
MySQL faster. You can check the calculated distribution
by using ‘–description --verbose table_name’.
–stats_method=name Specifies how index statistics collection code should
treat NULLs. Possible values of name are “nulls_unequal”
(default for 4.1/5.0), “nulls_equal” (emulate 4.0), and
“nulls_ignored”.
-d, --description Prints some information about table.
-A, --set-auto-increment[=value]
Force auto_increment to start at this or higher value
If no value is given, then sets the next auto_increment
value to the highest used value for the auto key + 1.
-S, --sort-index Sort index blocks. This speeds up ‘read-next’ in
applications.
-R, --sort-records=#
Sort records according to an index. This makes your
data much more localized and may speed up things
(It may be VERY slow to do a sort the first time!).
-b, --block-search=#
Find a record, a block at given offset belongs to.

Default options 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: myisamchk
The following 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.

4.修复表说明
该部分内容转载自：https://blog.51cto.com/douya/1603934

用myisamchk修复是应注意的：
myisamchk 默认使用的内存太小了，如果要修复大表的话，显然速度会很慢，我们可以加内存参数，来加快运行：
myisamchk --sort_buffer_size=16M --key_buffer_size=16M --read_buffer_size=1M --write_buffer_size=1M

恢复步骤 1：
首先，应该执行仔细检查数据库报错的错误代码。通过mysql终端或者执行myisamchk命令都可以查看错误代码
myisamchk -im --verbose tablename
[root@node3 db1]# myisamchk -im --verbose t2

常见错误代码：
perro 126 127 132 134 135 136 141 144 145

对于error135（no more room in recoder file）和 error136 （no more romm in index file）来说可以简单的修复、
alter table tablename max_rows=xxx avg_row_length=yyyy;

恢复步骤2：
在使用myisamchk修复会优化时候，必须保证mysqld服务器没有使用该表，最好关闭mysql服务器。如果不关闭mysql，
在运行myisamchk之前应该执行flush tables tablename with read lock.如果服务器和myisam同时访问表，表可能会被破坏
mysql>flush table t2 with read lock;
myssql>unlock tables;

恢复步骤3：
最后使用myisamchk命令跟上-rq参数组合就可以修复指定表了，
如果修复失败，可以再尝试使用-Br参数组合
如果还不行，再试试使用-o参数
#myisam -rq tablename
#myisam -Br tablename
#myisam -o tablename
-r -o 参数都会扫描整个表 然后抽取出来存储成一个临时文件，建议一般都跟着-B 参数

用myisamchk 恢复丢失的索引表
step1：把MYD文件移到安全的地方、
step2：使用。frm文件恢复表结构
step3：mysql db_name
step4:mysql> set autocommit=1;
step5:mysql>truncate table tbl_name;
step6:mysql>quit
step7:将老的.MYD拷贝回来
step8：myisamchk -rq tablename

以上的2-7步骤可以替换为1-5
1 rm -rf t2.MYI(已损坏的索引文件
2 touch t2.MYI建一个空的MYI文件
3 mv /root/t2.MYD /usr/local/mysql/data/db1
4 chow -R mysql.mysql MYI
5 repair table tablename use_frm）