MySQL 自带工具

2015-07-25 Saturday     mysql , database

简单介绍下 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 TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE 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



如果喜欢这里的文章,而且又不差钱的话,欢迎打赏个早餐 ^_^


About This Blog

Recent Posts

Categories

Related Links

  • RTEMS
    RTEMS
  • GNU
  • Linux Kernel
  • Arduino

Search


This Site was built by Jin Yang, generated with Jekyll, and hosted on GitHub Pages
©2013-2018 – Jin Yang