简单介绍下 MySQL 中自带的工具集。
简介
MySQL 提供了许多命令行工具，这些工具可以用来管理 MySQL 服务器、进行数据库备份和恢复、对数据库进行访问控制、对数据库进行压测等等。
首先，简单介绍下各个命令行。
----- MySQL安装、初始化
mysql_install_db
初始化 MySQL 数据目录程序，主要是新建数据库，初始化用户；5.7 已使用 mysqld 替换；
mysql_secure_installation
安装完之后进行一些安全性配置，例如设置root用户、删除test数据库等；
mysql_safe
一个shell脚本程序，用来安全启动mysqld程序；
mysql_tzinfo_to_sql
将时区信息转换为SQL语句，可以直接加载到MySQL的mysql.time_zone%文件中；
mysql_upgrade
用于升级时检查MySQL表；
----- MySQL客户端程序
mysql
MySQL命令行程序，用于链接数据库；
mysqlslap
压测程序，可以用来简单的对数据库进行性能压测；
mysqldump
逻辑备份程序；
mysqlimport
数据导入工具；
----- MySQL管理和实用程序
mysqlbinlog
用于处理二进制日志文件(binlog)，可以用于打印数据；
mysqldumpslow
用于处理慢查询日志文件；
mysqladmin
用于管理MySQL服务器；
----- 其它
my_print_defaults
读取配置文件中的配置选项；
mysql_tzinfo_to_sql
该命令用于加载时区表，首先看下 MySQL 中时区的设置。
----- 查看MySQL当前时间以及当前时区
mysql> SELECT curtime(); # 也可以使用now()
mysql> SHOW VARIABLES LIKE "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST | # system使用CST时区
| time_zone | SYSTEM | # 使用system时区，也就是上述的参数
+------------------+--------+
2 rows in set (0.00 sec)
----- 修改MySQL时区为北京时间，也即东8区；并立即生效
mysql> SET [GLOBAL | SESSION] time_zone='+8:00';
mysql> FLUSH PRIVILEGES;
----- 也可以直接修改配置文件，需要重启服务器
$ cat /etc/my.cnf
[mysqld]
default-time_zone = '+8:00'
Linux 系统中，与时区相关的内容保存在 /usr/share/zoneinfo 目录下；MySQL 中与时区相关的信息保存在 mysql.time_zone% 表中，可以通过该命令将时区信息导入到 MySQL 中。
----- 将全部时区信息，或者指定时区信息转换为SQL，并导入到MySQL中
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
$ mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql
----- 如果有闰秒，则同时设置
$ mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql
mysqlslap
这是一个压测工具，源码在 client/mysqlslap.c，测试的步骤为：A) 创建数据库+表；B) 插入测试数据；C) 执行压测；D) 删除创建的数据库。
常见参数为：
--delimiter
分隔符，用于分割命令行或文件指定的SQL；
--auto-generate-sql, -a
自动生成测试表和数据，并自动生成 SQL 脚本来测试并发压力；
--create-schema (mysqlslap)
测试 schema 名称，也就是 database；
--only-print
只打印测试语句而不实际执行；
--concurrency=N, -c N
控制并发，用于模拟多少个客户端同时执行select，可以指定多个数值，用于不同迭代时的并发；
--iterations=N, -i N
执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次；
--number-of-queries=N
总的测试查询次数，其值 = 并发客户数 x 每次查询数；
--debug-info, -T
最后执行结果打印内存和 CPU 的相关信息；
--auto-generate-sql-load-type=type
测试语句的类型，代表要测试的环境是读操作还是写操作还是两者混合的，取值包括：read，key，write，update和mixed(默认)；
--auto-generate-sql-add-auto-increment
代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持；
--number-char-cols=N, -x N
自动生成的测试表中包含多少个字符类型的列，默认1；
--number-int-cols=N, -y N
自动生成的测试表中包含多少个数字类型的列，默认1；
--query=name,-q
使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试；
--commint=N
多少条DML后提交一次；
--compress, -C
如果服务器和客户端支持都压缩，则压缩信息传递；
--engine=engine_name, -e engine_name
代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb；
--detach=N
执行N条语句后断开重连。
如下是常见的测试用例。
----- 单线程测试，打印执行的SQL，查看具体做了什么
$ mysqlslap --auto-generate-sql --only-print -uroot -pYourPassword
----- 设置并发以及循环次数，也就是说在并发为10,20,50时，均执行4次，返回10,20,50时的统计结果
$ mysqlslap --auto-generate-sql --concurrency=10,20,50 --iterations=4 --number-of-queries 1000 \
-uroot -pYourPassword
----- 测试不同类型存储引擎的性能指标
$ mysqlslap --auto-generate-sql --concurrency=10,20,50 --iterations=4 --number-of-queries 1000 \
--engine=myisam,innodb -uroot -pYourPassword
mysqldump
这是一个逻辑备份工具，其处理流程基本如下。
01 Connect root@localhost on
02 Query /*!40100 SET @@SQL_MODE='' */
03 Init DB foobar
04 Query SHOW TABLES LIKE 'foobar'
05 Query LOCK TABLES `foobar` READ /*!32311 LOCAL */
06 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
07 Query SHOW CREATE TABLE `foobar`
08 Query SHOW FIELDS FROM `foobar`
09 Query SHOW TABLE STATUS LIKE 'foobar'
11 Query SELECT * FROM `foobar`
12 Query UNLOCK TABLES
13 Quit
接下来，主要查看一些常见的参数。
-q
导出数据时加了一个SQL_NO_CACHE来确保不会读取缓存里的数据，第11行修改如下；
Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `foobar`
--lock-tables
跟上面类似，不过多加了一个READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入；
--lock-all-tables
备份前会发起一个全局的读锁，会阻止对所有表的写入，以此确保数据的一致性，备份完成后会话断开，会自动解锁；
会在开头增加如下命令；
Query FLUSH TABLES
Query FLUSH TABLES WITH READ LOCK
--master-data
和--lock-all-tables参数相同，同时多了个SHOW MASTER STATUS命令；
Query FLUSH TABLES
Query FLUSH TABLES WITH READ LOCK
Query SHOW MASTER STATUS
--single-transaction
InnoDB表在备份时，通常会启用参数来保证备份的一致性，其工作原理是设定本次会话的隔离级别为REPEATABLE READ，
以确保本次会话(dump)时，不会看到其他会话已经提交了的数据；同样开始增加如下命令：
Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Query BEGIN
Query UNLOCK TABLES
mysqldumpslow
该命令用于处理慢查询日志，这是一个 Perl 脚本程序。首先，需要设置好配置文件。
$ cat /etc/my.cnf
[mysqld]
long_query_time=2 # 设置慢查询的超时时间，单位为秒
log-slow-queries=slow-query.log
另外，可以通过 log-queries-not-using-indexes 参数设置没有使用索引的 SQL 。
可以使用该命令查看慢查询日志，常用的命令可以通过如下方式查看。
常用参数：
-h : 查看帮助信息；
-t NUM: 显示头NUM条记录；
-s ARG: 排序参数，有如下的选项，其中前面添加a表示倒序，如ac、at、al、ar；
c: 记录的次数；
t: 查询时的时间戳；
l: 查询使用的时间；
r: 返回记录数目；
-g REG: 根据正则表达式进行匹配，大小写不敏感；
实例：
----- 返回访问次数最多的20个SQL语句
$ mysqldumpslow -s c -t 20 slow-query.log
----- 按照时间返回前10条里面含有左连接的SQL语句
$ mysqldumpslow -s t -t 10 -g "left join" slow-query.log
通过这个工具可以查询出来那些 SQL 语句是性能的瓶颈，如下是上述命令的输出：
Count: 2 Time=2.31s (5s) Lock=0.00s (0s) Rows=1000.0 (2000), root[root]@[localhost]
SELECT * FROM sms_send WHERE service_id<=N GROUP BY content LIMIT N, N;
输出解析：
出现次数(Count) 2；
最大耗时时间(Time) 2.31s；
累计总耗费时间(Time) 5s；
等待锁的时间(Lock) 0.00s；
等待锁的总耗时(Lock) 0s；
发送给客户端的行总数(Rows) 1000；
扫描的行总数(Rows) 2000；
用户以及SQL语句本身，其中的数字会被替换为N。
mysqlcheck
数据库经常可能遇到错误，譬如数据写入磁盘时发生错误、索引没有同步更新、数据库宕机等；从而可能会导致数据库异常。
mysqlcheck 的功能类似 myisamchk，但其工作不同，前者需要在服务器运行的时候执行，而后者需要停服务。
实际上，mysqlcheck 只是提供了一种方便的使用 SQL 语句的方式，会根据不同类型拼接 SQL 语句，真正调用的还是 CHECK TABLE、REPAIR TABLE、ANALYZE TABLE 和 OPTIMIZE TABLE 命令。
可以通过 3 种方式来调用 mysqlcheck 。
----- 检查表
$ mysqlcheck [options] db_name [tables]
----- 检查多个数据库
$ mysqlcheck [options] ---database DB1 [DB2 DB3...]
----- 检查所有的数据库
$ mysqlcheck [options] --all--database
选项：
--database，-B
指定数据库名，可以为多个；
--all--database，-A
检查所有数据库；
源码在 client/check 目录下，处理过程简单介绍如下。
main()
|-get_options() ← 加载配置文件默认配置
| |-load_defaults() ← 通过load_default_groups指定配置文件加载的groups
|
|-mysql_check()
|-disable_binlog() ← 根据参数设置SET SQL_LOG_BIN=0
|-process_all_databases() ← 处理所有数据库
| |-process_one_db()
| |-process_all_tables_in_db()
| |-process_selected_tables()
|
|-process_selected_tables()
| |-handle_request_for_tables() ← 真正的拼接命令处
|
|-process_databases()
其中 handle_request_for_tables() 函数的处理流程如下。
static int handle_request_for_tables(string tables)
{
string operation, options;
switch (what_to_do) {
case DO_CHECK:
operation = "CHECK";
if (opt_quick) options+= " QUICK";
if (opt_fast) options+= " FAST";
if (opt_medium_check) options+= " MEDIUM"; /* Default */
if (opt_extended) options+= " EXTENDED";
if (opt_check_only_changed) options+= " CHANGED";
if (opt_upgrade) options+= " FOR UPGRADE";
break;
case DO_REPAIR:
operation= (opt_write_binlog) ? "REPAIR" : "REPAIR NO_WRITE_TO_BINLOG";
if (opt_quick) options+= " QUICK";
if (opt_extended) options+= " EXTENDED";
if (opt_frm) options+= " USE_FRM";
break;
case DO_ANALYZE:
operation= (opt_write_binlog) ? "ANALYZE" : "ANALYZE NO_WRITE_TO_BINLOG";
break;
case DO_OPTIMIZE:
operation= (opt_write_binlog) ? "OPTIMIZE" : "OPTIMIZE NO_WRITE_TO_BINLOG";
break;
case DO_UPGRADE:
return fix_table_storage_name(tables);
}
string query= operation + " TABLE " + tables + " " + options;
if (mysql_real_query(sock, query.c_str(), (ulong)query.length()))
{
DBError(sock,
"when executing '" + operation + " TABLE ... " + options + "'");
return 1;
}
print_result();
return 0;
}
也即是实际上会生成如下的命令。
CHECK TABLE table_name {QUICK|FAST|MEDIUM|EXTENDED|CHANGED|FOR UPGRADE}
REPAIR NO_WRITE_TO_BINLOG TABLE table_name {QUICK|EXTENDED|USE_FRM}
ANALYZE NO_WRITE_TO_BINLOG TABLE table_name
OPTIMIZE NO_WRITE_TO_BINLOG TABLE table_name
每个表会记录最近的一次检查时间，可以通过如下命令查看。
mysql> SELECT table_name, check_time FROM information_schema.tables
WHERE table_name = 'tbl-name' AND table_schema = 'db-name';
mysqladmin
该工具最常见的是用来关闭数据库，还可以查看 MySQL 运行状态、进程信息、关闭进程等，如下是常用的子命令；所有命令可以通过 --help 查看帮助文档。
mysqladmin [option] command [command-option] command ......
参数如下：
extended-status
可获得所有MySQL性能指标，即SHOW GLOBAL STATUS的输出
status
获取当前MySQL的几个基本的状态值，包括线程数、查询量、慢查询等
variables
打印出可用变量
ping
查看服务器是否存活
shutdown
关掉服务器
processlist
显示服务其中活跃线程列表
version
得到服务器的版本信息
password 'new-password'
新口令，将老口令改为新口令
extended-status
默认输出的都是累计值，可以通过 -r/--relative 查看各个指标的差值；然后再配合 -i/--sleep 指定刷新的频率。
$ mysqladmin -uroot -pnew-password -h127.1 -P3307 -r -i 1 extended-status |\
grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update \|Com_delete"
$ mysqladmin -uroot -pnew-password -h127.1 -P3307 -r -i 1 extended-status |\
awk -F"|" '{\
if($2 ~ /Variable_name/){\
print " <------------- " strftime("%H:%M:%S") " ------------->";\
}\
if($2 ~ /Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete/)\
print $2 $3;\
}'
$ mysqladmin -uroot -pnew-password -h127.1 -P3307 -r -i 1 extended-status |\
awk 'BEGIN{ FS="|"; count=0; } { \
if($2 ~ /Variable_name/ && ((++count)%20 == 1)){ \
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted";\
} \
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%6d %8d %7d %7d\n",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
}}'
mysqlbinlog
二进制日志 (Binary Log) 是由多个事件 (events) 组成，用于描述对于数据库的修改内容，MySQL 服务器以二进制的形式写入，可以通过该工具显示文件中具体事件的内容。
----- 备份时指定多个binlog文件
$ mysqlbinlog --stop-date="2015-04-20 9:59:59" mysql-bin.[0-9]* | \
mysql -u root -pyour-password
----- 只恢复单个库example
$ mysqlbinlog --stop-date="2015-04-20 9:59:59" mysql-bin.000001 | \
mysql -u root -pyour-password --one-database example
----- 指定起始时间以及库
$ mysqlbinlog --start-datetime='2015-08-05 00:00:00' --stop-datetime='2015-08-05 10:00:00' \
--database=db_name mysql-bin.000001
----- 也可以指定binlog的position位置
$ mysqlbinlog --start-postion=107 --stop-position=1000 --database=db_name mysql-bin.000001
----- 从远程服务器读取
$ mysqlbinlog -u username -p password -h127.1 -P3306 --read-from-remote-server \
--start-datetime='2015-08-05 00:00:00' --stop-datetime='2015-08-05 10:00:00' mysql-bin.000001
ROW格式解析
首先准备下数据。
CREATE DATABASE test;
USE test;
CREATE TABLE foobar (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL DEFAULT 'M',
address VARCHAR(30) NOT NULL
) Engine=InnoDB;
INSERT INTO foobar(name,sex,address) VALUES('Barton','M','Washington'),('Borg','M','New Mexico'),
('Steven','M','Colorado');
UPDATE foobar SET address='Texas';
可以直接通过 mysqlbinlog 解析。
----- 解析ROW格式binlog文件
$ mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000003
... ...
### INSERT INTO `test`.`foobar`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Barton' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='Washington' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
... ...
### UPDATE `test`.`foobar`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='Steven' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='Colorado' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='Steven' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='Texas' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
@1、@2、@3、@4 分别代表了第 1~4 列。
innochecksum
使用该工具时必须停止 MySQL 服务器，否则会报 Unable to lock file 错误，如果是在线的最好使用 CHECK TABLE 命令。
简单来说，该工具会读取 InnoDB 的表空间，计算每个页的 Checksum 值，然后与页中的值比较，如果不同则会报错。
innochecksum --verbose=FALSE --log=/tmp/innocheck.log
常用参数：
--help/info
查看帮助信息；
--verbose
打印详细信息，可以通过--verbose=FALSE关闭；
--count/-c
只打印页的数量信息；
--start-page=NUM/-s NUM; --end-page=NUM/-e NUM; --page=NUM/-p NUM
指定开始、结束页，或者只查看指定的页；
--strict-check/-C
指定checksum算法，通常有innodb、crc32、none，默认是从三者中选择，否则强制指定；
--page-type-summary/-S
打印文件中页的统计信息，包括了总页类型以及数量；
--page-type-dump=file-name/-D file-name
打印各个页的详细信息，将其输出到一个文件中；
常用示例：
----- 检查系统表空间，也可以使用table-name.ibd，默认出错时才会输出异常
innodbchecksum ibdata1
----- 保存文件中各个页的信息，并在最后打印统计信息
innodbchecksum -S -D /tmp/page.info schema/*.ibd
详细使用文档可以参考 innochecksum 。
my_print_defaults
会按照顺序读取配置文件，并提取相应属组的配置项，可以指定多个属组。
----- 使用示例
$ my_print_defaults mysqlcheck client
--user=myusername
--password=secret
--host=localhost
常见参数如下：
--config-file=file_name, --defaults-file=file_name, -c file_name
只读取如上选项指定的配置文件。
--defaults-extra-file=file_name, --extra-file=file_name, -e file_name
读取全局配置项且在读取用户配置前的配置文件。
如果不添加任何参数，可以看到配置文件默认的加载顺序。
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
参考
关于 MySQL 自带的程序，可以直接参考官方网站 Reference Manual - MySQL Programs 。