percona-toolkit

参考文档 http://www.cnblogs.com/kevingrace/p/6261091.html

#percona-toolkit介绍 percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，这些任务包括

1）检查master和slave 数据一致性 2）有效的对记录进行归档 3）查找重复的索引 4）对服务器信息汇总 5)分析来自日志和tcpdump的查询 6）当系统出现问题时候收集重要的系统信息

#1.1.1 percona-toolkit 安装法一： [root@b6 local]#wget https://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm

安装该工具依赖的软件包 [root@b6 local]#yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI perl-Digest-MD5 -y

[root@b6 local]#rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm [root@b6 local]#rpm -ql percona-toolkit

=================================================== 法二： wget http://www.percona.com/redir/downloads/percona-toolkit/2.2.7/percona-toolkit-2.2.7.tar.gz tar zxvf percona-toolkit-2.2.7.tar.gz cd percona-toolkit-2.2.7 perl Makefile.PL make make test make install

#工具类别工具命令工具作用备注

1、开发类 pt-duplicate-key-checker 列出并删除重复的索引和外键 pt-online-schema-change 在线修改表结构 pt-query-advisor 分析查询语句，并给出建议，有bug 已废弃 pt-show-grants 规范化和打印权限 pt-upgrade 在多个服务器上执行查询，并比较不同

2、性能类 pt-index-usage 分析日志中索引使用情况，并出报告 pt-pmp 为查询结果跟踪，并汇总跟踪结果 pt-visual-explain 格式化执行计划 pt-table-usage 分析日志中查询并分析表使用情况 pt 2.2新增命令

3、配置类 pt-config-diff 比较配置文件和参数 pt-mysql-summary 对mysql配置和status进行汇总 pt-variable-advisor 分析参数，并提出建议

4、监控类 pt-deadlock-logger 提取和记录mysql死锁信息 pt-fk-error-logger 提取和记录外键信息 pt-mext 并行查看status样本信息 pt-query-digest 分析查询日志，并产生报告常用命令 pt-trend 按照时间段读取slow日志信息已废弃

5、复制类 pt-heartbeat 监控mysql复制延迟 pt-slave-delay 设定从落后主的时间 pt-slave-find 查找和打印所有mysql复制层级关系 pt-slave-restart 监控salve错误，并尝试重启salve pt-table-checksum 校验主从复制一致性 pt-table-sync 高效同步表数据

6、系统类 pt-diskstats 查看系统磁盘状态 pt-fifo-split 模拟切割文件并输出 pt-summary 收集和显示系统概况 pt-stalk 出现问题时，收集诊断数据 pt-sift 浏览由pt-stalk创建的文件 pt 2.2新增命令 pt-ioprofile 查询进程IO并打印一个IO活动表 pt 2.2新增命令

7、实用类 pt-archiver 将表数据归档到另一个表或文件中 pt-find 查找表并执行命令 pt-kill Kill掉符合条件的sql 常用命令 pt-align 对齐其他工具的输出 pt 2.2新增命令 pt-fingerprint 将查询转成密文 pt 2.2新增命令

#1.1.2 工具的三个组件 1）pt-table-checksum 负责监测mysql主从数据一致性 2）pt-table-sync 负责当主从数据不一致时修复数据，让他们保持数据一致性 3）pt-heartbeat 负责监控mysql主从同步延迟

percona-toolkit 安装 [root@b6 local]#wget https://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm

安装该工具依赖的软件包 [root@b6 local]#yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI perl-Digest-MD5 -y

[root@b6 local]#rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm [root@b6 local]#rpm -ql percona-toolkit

#1.1.2.1 pt-table-checksum 使用

pt-table-checksum 用于检测mysql主从的数据是否一致性。原理是在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后比较主从库相同数据块的checksum值，由判断主从数据是否一致性。检测过程根据唯一索引将表按row切分为块，以单位计算，可以避免锁表。检测时会自动判断复制延迟，master的负载，超过阈值会自动将检测暂停，减少对线上服务的影响。必要时，pt-table-checksum 会根据服务器负载动态改变chunk大小，减少从库的延迟。

为了减少对数据库的干扰，pt-table-checksum 还会自动侦测并连接到从库，如果失败，可以指定--recursion-method 选项来告诉从库在哪里，它的易用性还体现在复制若有延迟，在从库checksum会暂停直到赶上主库的计算时间点（也通过选项设定一个容忍的延迟最大值，超过这个值也可以认为不一致）

为了保证主数据库服务的安全，实现了保护措施： 1）自动设置innodb_lock_wait_timeout为1s,避免引起 2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置--max-load 选项来设置这个阈值 3）当用ctrl+c 停止任务后，工具会正常的完成当前的chunk检测，下次使用--resume选项启动可以恢复继续下一个chenk

pt-table-checksum [options] [dsn]

pt-table-checksum: 在主（master）上通过执行效验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。dsn 指向的时主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误。注意：第一次运行的时候需要加上 --create-replicate-table 参数，生成checksums 表。如果不加这个参数，那么就需要在对应库下面手工添加这张表了。

CREATE TABLE checksums ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB;

================================================================= 常用参数解释 --nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库 --no-check-binlog-format :不检查复制的binlog模式，要是binlog模式是row 则会报错。 --replicate-check-only : 只显示不同步信息 --replicate= :把checksum的信息写入到指定的表中，建议直接写到被检查的数据库当中 --databases= :指定需要被检查的数据库，多个则用逗号隔开 --tables=: 指定需要被检查的表，多个用逗号隔开 h= :master的地址 u= :用户名 p= :密码 p= :端口

*主库要授权让主库ip访问

注意： 1）根据测试，需要一个既能登陆主库也能登陆从库的账号 2）只能指定一个host，必须为主库的ip 3）在检查时会向表加s锁 4）运行之前需要从库的同步IO和SQL进程时yes状态

[root@b6 weibo]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=weibo.checksums --create-replicate-table --databases=weibo h=192.168.241.6,u=root,p=123456,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 10-16T18:54:41 0 0 3192749 10 0 4.274 weibo.random_available 10-16T18:54:54 0 0 12263666 21 0 13.336 weibo.random_invalid [root@b6 weibo]#

解释： TS：完成检查的时间 ERRORS:检查时候发生错误和警告的数量 DIFFS：0表示一致，1表示不一致。当指定—no-replicate-check时，会一直为0，当指定—replicate-check-only 会显示不同的信息。 ROWS：表的行数 CHUNKS：被划分到表中块的数目。 SKIPPED：由于错误或者警告或过大，则跳过块的数目 TIME：执行的时间 TABLE：被检查的表名

#遇到报错 [root@b32 tools]# /root/perl5/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test h=192.168.241.34,u=root,p=123456 Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. 提示信息，找不到从库，所以执行失败。需要用参数 --recursion-method 指定模式解决，关于--recursion-method参数设置有

METHOD USES =========== ============================================= processlist SHOW PROCESSLIST hosts SHOW SLAVE HOSTS cluster SHOW STATUS LIKE 'wsrep_incoming_addresses' dsn=DSN DSNs from a table none Do not find slaves 默认是通过show processlist 找到 slave host的值。还有另外一种办法：在从库配置文件配置自己的端口和地址。

从库配置： [root@b34 tools]# cat /disk1/mysql/my.cnf|grep "report_*" report_host = 192.168.241.34 report_port = 3306 [root@b34 tools]#

mysql>

主库执行 [root@b32 tools]# /root/perl5/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=topic h=192.168.241.34,u=root,p=123456 --recursion-method=hosts Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 03-15T17:11:45 0 0 415 1 0 0.007 topic.Industry_Object 03-15T17:11:45 0 0 592 1 0 0.004 topic.category 03-15T17:11:45 0 0 3 1 0 0.005 topic.topic_tree

#1.1.2.2 pt-table-sync 使用通过pt-table-checksum 检查到不一致的数据表，可以使用pt-table-sync 工具 pt-table-checksum:高效的同步mysql表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。他不同步表结构，索引，或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

用法： pt-table-sync --replicate=weibo.checksums h=192.168.241.6,u=root,p=**** h=192.168.241.11,u=root,p=**** --print

参数解释： --replicate= : 指定通过pt-table-checksum得到的表，这2工具差不多都会一直用。 --databases= : 指定执行同步的数据库 --tables= :指定执行同步的表，多个用逗号隔开 --sync-to-master :指定一个dsn，即从的IP，他会通过show processlist或show slave status 去自动找主。 h= : 服务器地址，命令里有2ip,第一个是master地址，第二个是slave地址。 u= :账号 p= :密码 --print ; 打印，但不执行命令 --execute ; 执行命令

修复数据的时候，最好还是用—print打印出来，这样就可以知道数据有问题，可以人工干预下。处理之前做好备份

#监控脚本 1、#!/bin/bash 2、NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu
3、h=192.168.1.101,u=root,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p') 4、if [ $NUM -eq 1 ];then 5、 /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print 6、 /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute 7、else 8、 echo "data is ok" 9、fi

#1.1.2.3 pt-heartbeat 监控mysql主从复制延迟 mysql数据库主从复制延迟的监控，可以借助pt-heartbeat来实现。 pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表，然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟。具体流程： 1）在主上创建一张heartbeat表，按照一定时间频率更新表的字段（把更新时间写进去），监控操作运行后，heartbeat表能促使主从同步。 2）连接到从库上检查复制的删记录，和从库的当前时间进行比较，得出时间差异。

使用方法（主从库上都可以执行监控操作）： pt-heartbeat [postions] [dsn] --update|--monitor|--check|--stop 注意：需要指定参数至少有 –stop –update –monitor –check，其中—update,--monitor和—check是互斥的，--daemonize和—check 也是互斥。

参数解释 --ask-pass 隐式输入mysql密码 --charset 字符集设置 --check 检查从的延迟，检查一次就退出，除非指定—recurse会递归的检查所有服务器 --check-read-only 如果服务器开启只读模式，该工具会跳过任何插入 --create-table

在主上创建心跳监控的表，如果该表不存在，可以手动建立，建议存储引擎改成memory。通过更新该表知道主从延迟的差距。

CREATE TABLE heartbeat ( ts varchar(26) NOT NULL, server_id int unsigned NOT NULL PRIMARY KEY, file varchar(255) DEFAULT NULL, position bigint unsigned DEFAULT NULL, relay_master_log_file varchar(255) DEFAULT NULL, exec_master_log_pos bigint unsigned DEFAULT NULL );

Heartbeat 表一直在更改ts和position，而ts是我们检查复制延迟的关键。 --daemonize 执行时，放入到后台执行 --user=-u 连接数据库的账号 --database= -D 连内数据库的名称 --host = -h 连接的数据库地址 --password= -p 连接数据库的密码 --port= -p 连接数据库的端口 --socket= -S 连接数据库的套接字文件 --file (--file=output.txt) 打印—monitor最新的记录到指定的文件 --frames ( --frames=1m,2m,3m) 在—monitor里输出的（）里的记录段，默认是1m,2m,3m。可以指定1个或者多个用逗号隔开。--frames=1s, 可用单位有秒（s），分钟(m)，小时(h)，天(d)。 --interval 检查，更新的间隔时间。默认是1s。 --log 开启daemonized 模式的所有日志将会被打印到指定的文件中。 --monitor 持续监控从的延迟情况，通过—interval指定的间隔时间，打印出从的延迟信息，--file则可以把这些信息打印到指定的文件 --master-server-id 指定主的server_id，若没有指定则该工具会连接到主上找其server-id。 --print-master-server-id 在—monitor和--check模式下，指定该参数则打印出主的server-id --recurse 多级复制的检查深度 --recursion-method 指定复制检查的方式。默认为processlist,hosts。 --update 更新主上的心跳表 --replace 使用—replace代替—update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行 --stop 停止运行该工具（--daemonize），在/tmp创建一个”pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize） --table 指定心跳表名，默认heartbeat.

实例说明： master：192.168.1.101 slave：192.168.1.102 同步的库：huanqiu、huanpc 主从库都能使用root账号、密码123456登录先操作针对huanqiu库的检查，其他同步的库的检查操作类似！ mysql> use huanqiu;
Database changed

mysql> CREATE TABLE heartbeat ( //主库上的对应库下创建heartbeat表，一般创建后从库会同步这张表（不同步的话，就在从库那边手动也手动创建） -> ts varchar(26) NOT NULL, -> server_id int unsigned NOT NULL PRIMARY KEY, -> file varchar(255) DEFAULT NULL, -> position bigint unsigned DEFAULT NULL, -> relay_master_log_file varchar(255) DEFAULT NULL, -> exec_master_log_pos bigint unsigned DEFAULT NULL -> ); Query OK, 0 rows affected (0.02 sec)

更新主库上的heartbeat,--interval=1表示1秒钟更新一次（注意这个启动操作要在主库服务器上执行） [root@master-server ~]# pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize Enter password: [root@master-server ~]# ps -ef|grep pt-heartbeat root 15152 1 0 19:49 ? 00:00:00 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize root 15154 14170 0 19:49 pts/3 00:00:00 grep pt-heartbeat

在主库运行监测同步延迟： [root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] ........ 解释：0表示从没有延迟。 [ 0.00s, 0.00s, 0.00s ] 表示1m,5m,15m的平均值。可以通过--frames去设置。

或者加上--master-server-id参数（主库my.cnf里配置的server-id值） [root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --master-server-id=101 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] ......... 也可以将主库的server-id打印出来（--print-master-server-id） [root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monit --host=192.168.1.102 --user=root --password=123456 --print-master-server-id 0.00s [ 0.00s, 0.00s, 0.00s ] 101 0.00s [ 0.00s, 0.00s, 0.00s ] 101 0.00s [ 0.00s, 0.00s, 0.00s ] 101 0.00s [ 0.00s, 0.00s, 0.00s ] 101 ......... [root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --check --host=192.168.1.102 --user=root --password=123456 --print-master-server-id 0.00 101

上面的监测命令会一直在运行状态中，可以使用--check监测一次就退出注意：使用了--check，就不能使用--monit --update，--monitor和--check是互斥的，--daemonize和--check也是互斥。

[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --check --host=192.168.1.102 --user=root --password=123456 0.00 [root@master-server ~]#

注意：如果想把这个输出结果加入自动化监控，那么可以使用如下命令使监控输出写到文件，然后使用脚本定期过滤文件中的最大值作为预警即可：注意--log选项必须在有--daemonize参数的时候才会打印到文件中，且这个文件的路径最好在/tmp下，否则可能因为权限问题无法创建 [root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize [root@master-server ~]# tail -f /opt/master-slave.txt //可以测试，在主库上更新数据时，从库上是否及时同步，如不同步，可以在这里看到监控的延迟数据 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] .......

下面是编写的主从同步延迟监控脚本，就是定期过滤--log文件中最大值（此脚本运行的前提是：启动更新主库heartbeat命令以及带上--log的同步延迟检测命令）。如果发生延迟，发送报警邮件。sendemail邮件发送环境部署参考：http://www.cnblogs.com/kevingrace/p/5961861.html [root@master-server ~]# cat /root/check-slave-monit.sh
1、#!/bin/bash 2、cat /opt/master-slave.txt > /opt/master_slave.txt 3、echo > /opt/master-slave.txt 4、max_time=cat /opt/master_slave.txt |grep -v '^$' |awk '{print $1}' |sort -k1nr |head -1 5、NUM=$(echo "$max_time"|cut -d"s" -f1) 6、 if [ $NUM == "0.00" ];then 7、 echo "Mysql主从数据一致" 8、 else 9、 /usr/local/bin/sendEmail -f [email protected] -t [email protected] -s smtp.huanqiu.cn -u "Mysql主从同步延迟" -o message-content-type=html -o message-charset=utf8 -xu 10、10、 [email protected] -xp WEE78@12l$ -m "Mysql主从数据同步有延迟" 11、 fi

[root@master-server ~]# chmod /root/check-slave-monit.sh [root@master-server ~]# sh /root/check-slave-monit.sh Mysql主从数据一致结合crontab，每隔一分钟检查一次

[root@master-server ~]# crontab -e #mysql主从同步延迟检查

- - - - /bin/bash -x /root/check-slave-monit.sh > /dev/null 2>&1 在从库上运行监测同步延迟(也可以在命令后加上--master-server-id=101或--print-master-server-id，同上操作)

[root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --user=root --password=123456 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] ........ [root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --user=root --password=123456 --check 0.00

[root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --user=root --password=123456 --log=/opt/master-slave.txt --daemonize [root@slave-server src]# tail -f /opt/master-slave.txt 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ]

如何关闭上面在主库上执行的heartbeat更新进程呢？方法一：可以用参数--stop去关闭 [root@master-server ~]# ps -ef|grep heartbeat root 15152 1 0 19:49 ? 00:00:02 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize root 15310 1 0 19:59 ? 00:00:01 perl /usr/bin/pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat [root@master-server ~]# pt-heartbeat --stop Successfully created file /tmp/pt-heartbeat-sentinel [root@master-server ~]# ps -ef|grep heartbeat root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat [root@master-server ~]#

这样就把在主上开启的进程杀掉了。但是后续要继续开启后台进行的话，记住一定要先把/tmp/pt-heartbeat-sentinel 文件删除，否则启动不了

方法二：直接kill掉进程pid（推荐这种方法） [root@master-server ~]# ps -ef|grep heartbeat root 15152 1 0 19:49 ? 00:00:02 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize root 15310 1 0 19:59 ? 00:00:01 perl /usr/bin/pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat [root@master-server ~]# kill -9 15152 [root@master-server ~]# ps -ef|grep heartbeat root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat

最后总结：通过pt-heartbeart工具可以很好的弥补默认主从延迟的问题，但需要搞清楚该工具的原理。默认的Seconds_Behind_Master值是通过将服务器当前的时间戳与二进制日志中的事件时间戳相对比得到的，所以只有在执行事件时才能报告延时。备库复制线程没有运行，也会报延迟null。还有一种情况：大事务，一个事务更新数据长达一个小时，最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当备库执行这条语句时，会临时地报告备库延迟为一个小时，执行完后又很快变成0。

#1.2 percona-toolkit其他组件命令用法下面这些工具最好不要直接在线上使用，应该作为上线辅助或故障后离线分析的工具，也可以做性能测试的时候配合着使用。

#1.2.1 pt-online-schema-change 功能介绍：功能为:在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作，注意执行这个工具的时候必须做好备份，操作之前最好要充分了解它的原理。工作原理是:创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

用法介绍： pt-online-schema-change [OPTIONS] DSN options可以自行查看help（或加--help查看有哪些选项），DNS为你要操作的数据库和表。有两个参数需要注意一下： --dry-run 这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。 --execute 这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。这一举措是为了让使用这充分了解了这个工具的原理。

使用示例：在线更改表的的引擎，这个尤其在整理innodb表的时候非常有用，如下huanqiu库的haha表默认是Myisam存储引擎，现需要在线修改成Innodb类型。

mysql> show create table huanqiu.haha; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | haha | CREATE TABLE haha ( id int(10) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 修改操作如下：

[root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --check-replication-filters Found 1 slaves: slave-server Will check slave lag on: slave-server Replication filters are set on these hosts: slave-server slave_skip_errors = ALL replicate_ignore_db = mysql replicate_do_db = huanqiu,huanpc Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-online-schema-change line 8083. 如上命令就是在主库上操作的，会提示它有从库，需要添加参数--nocheck-replication-filters，即不检查从库。（注意：下面命令中可以将localhost换成主库ip。另外：该命令只能针对某张表进行修改，因为它是针对alter操作的，而alter是针对表的操作命令。所以不能省略命令中"t=表名"的选项） [root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --nocheck-replication-filters
Found 1 slaves: slave-server ....... 2017-01-16T10:36:33 Dropped old table huanqiu._haha_old OK. 2017-01-16T10:36:33 Dropping triggers... 2017-01-16T10:36:33 Dropped triggers OK. Successfully altered huanqiu.haha.

然后再次查看huanqiu.haha表的存储引擎，发现已是Innodb类型的了。 mysql> show create table huanqiu.haha; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | haha | CREATE TABLE haha ( id int(10) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

若是在从库上，则可以直接执行（也可以将下面从库ip替换成localhost） [root@slave-server ~]# pt-online-schema-change --user=root --password=123456 --host=192.168.1.102 --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute No slaves found. See --recursion-method if host slave-server has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: ....... 2017-01-15T21:40:35 Swapped original and new tables OK. 2017-01-15T21:40:35 Dropping old table... 2017-01-15T21:40:35 Dropped old table huanqiu._haha_old OK. 2017-01-15T21:40:35 Dropping triggers... 2017-01-15T21:40:35 Dropped triggers OK. Successfully altered huanqiu.haha.

#1.2.2 pt-duplicate-key-checker 功能介绍：功能为从mysql表中找出重复的索引和外键，这个工具会将重复的索引和外键都列出来，并生成了删除重复索引的语句，非常方便用法介绍： pt-duplicate-key-checker [OPTION...] [DSN] 包含比较多的选项，具体的可以通过命令pt-duplicate-key-checker --help来查看具体支持那些选项，我这里就不一一列举了。DNS为数据库或者表。使用示例：

查看huanqiu库或huanqiu.haha表的重复索引和外键使用情况使用，如下命令： [root@master-server ~]# pt-duplicate-key-checker --host=localhost --user=root --password=123456 --databases=huanqiu ...
Total Indexes 6

[root@master-server ~]# pt-duplicate-key-checker --host=localhost --user=root --password=123456 --databases=huanqiu --table=haha ... Total Indexes 1

#1.2.3 pt-slave-find 功能介绍：查找和打印mysql所有从服务器复制层级关系用法介绍： pt-slave-find [OPTION...] MASTER-HOST 原理：连接mysql主服务器并查找其所有的从，然后打印出所有从服务器的层级关系。使用示例：

查找主服务器为192.168.1.101的mysql有所有从的层级关系（将下面的192.168.1.101改成localhost，就是查询本机mysql的从关系）： [root@master-server ~]# pt-slave-find --user=root --password=123456 --host=192.168.1.101 192.168.1.101 Version 5.6.33-log Server ID 101 Uptime 5+02:59:42 (started 2017-01-11T10:44:14) Replication Is not a slave, has 1 slaves connected, is not read_only Filters
Binary logging MIXED Slave status
Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 5.6.33 +- 192.168.1.102 Version 5.6.34-log Server ID 102 Uptime 4+22:22:18 (started 2017-01-11T15:21:38) Replication Is a slave, has 0 slaves connected, is not read_only Filters slave_skip_errors=ALL; replicate_ignore_db=mysql; replicate_do_db=huanqiu,huanpc Binary logging MIXED Slave status 265831 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 5.6.34

#1.2.4 pt-show-grants 功能介绍：规范化和打印mysql权限，让你在复制、比较mysql权限以及进行版本控制的时候更有效率！用法介绍： pt-show-grants [OPTION...] [DSN] 选项自行用help查看，DSN选项也请查看help，选项区分大小写。使用示例：

查看指定mysql的所有用户权限： [root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:22:12 -- Grants for ''@'localhost' GRANT USAGE ON . TO ''@'localhost'; -- Grants for 'data_check'@'%' .......

查看执行数据库的权限： [root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456' --database='huanqiu' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:23:16 -- Grants for ''@'localhost' GRANT USAGE ON . TO ''@'localhost'; -- Grants for 'data_check'@'%' GRANT SELECT ON . TO 'data_check'@'%' IDENTIFIED BY PASSWORD '*36B94ABF70E8D5E025CF9C059E66445CBB05B54F'; -- Grants for 'mksync'@'%' GRANT ALL PRIVILEGES ON . TO 'mksync'@'%' IDENTIFIED BY PASSWORD '*B5E7409B1A22D47C6F1D8A693C6146CEB6570475'; ........

查看每个用户权限生成revoke收回权限的语句： [root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456' --revoke -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:24:58 -- Revoke statements for ''@'localhost' REVOKE USAGE ON . FROM ''@'localhost'; -- Grants for ''@'localhost' ..........

#1.2.5 pt-upgrade 功能介绍：这个工具用来检查在新版本中运行的SQL是否与老版本一样，返回相同的结果，最好的应用场景就是数据迁移的时候。这在升级服务器的时候非常有用，可以先安装并导数据到新的服务器上，然后使用这个工具跑一下sql看看有什么不同，可以找出不同版本之间的差异。用法介绍： pt-upgrade [OPTION...] DSN [DSN...] [FILE] 比较文件中每一个查询语句在每台服务器上执行的结果（主要是针对不同版本的执行结果）。（--help查看选项）使用示例：

查看某个sql文件在两个服务器的运行结果范例： [root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /root/test.sql

Logs

....... host1: DSN: h=192.168.1.101 hostname: master-server MySQL: Source distribution 5.6.33

host2: DSN: h=192.168.1.102 hostname: slave-server MySQL: Source distribution 5.6.34 ...... queries_read 1 queries_with_diffs 0 queries_with_errors 0

查看慢查询中的对应的查询SQL在两个服务器的运行结果范例： [root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /data/mysql/data/mysql-slow.log .......

#1.2.6 pt-index-usage 功能介绍：这个工具主要是用来分析慢查询的索引使用情况。从log文件中读取插叙语句，并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。用法介绍： pt-index-usage [OPTION...] [FILE...] 可以直接从慢查询中获取sql，FILE文件中的sql格式必须和慢查询中个是一致，如果不是一直需要用pt-query-digest转换一下。也可以不生成报告直接保存到数据库中，具体的见后面的示例注意：使用这个工具需要MySQL必须要有密码，另外运行时可能报找不到/var/lib/mysql/mysql.sock的错，简单的从mysql启动后的sock文件做一个软链接即可。重点要说明的是pt-index-usage只能分析慢查询日志，所以如果想全面分析所有查询的索引使用情况就得将slow_launch_time设置为0，因此请谨慎使用该工具，线上使用的话最好在凌晨进行分析，尤其分析大量日志的时候是很耗CPU的。整体来说这个工具是不推荐使用的，要想实现类似的分析可以考虑一些其他第三方的工具，比如：mysqlidxchx, userstat和check-unused-keys。网上比较推荐的是userstat，一个Google贡献的patch。使用示例：

从满查询中的sql查看索引使用情况范例： [root@master-server ~]# pt-index-usage --host=localhost --user=root --password=123456 /data/mysql/data/mysql-slow.log 将分析结果保存到数据库范例： [root@master-server ~]# pt-index-usage --host=localhost --user=root --password=123456 /data/mysql/data/mysql-slow.log --no-report --create-save-results-database

#1.2.7 pt-visual-explain 功能介绍：格式化explain出来的执行计划按照tree方式输出，方便阅读。用法介绍： pt-visual-explain [OPTION...] [FILE...]

通过管道直接查看explain输出结果的范例： mysql> select * from huanqiu.haha; +----+-------------+ | id | name | +----+-------------+ | 1 | wangshibo | | 2 | wangshihuan | | 3 | 王世博 | | 10 | wangshiman | +----+-------------+ 4 rows in set (0.00 sec)

[root@master-server ~]# mysql -uroot -p123456 -e "explain select * from huanqiu.haha" |pt-visual-explain Warning: Using a password on the command line interface can be insecure. Table scan rows 4 +- Table table haha

[root@master-server ~]# mysql -uroot -p123456 -e "explain select * from huanqiu.haha where id=3" |pt-visual-explain Warning: Using a password on the command line interface can be insecure. Bookmark lookup +- Table | table haha | possible_keys PRIMARY +- Constant index lookup key haha->PRIMARY possible_keys PRIMARY key_len 4 ref const rows 1

查看包含查询语句的test.sql文件的范例： [root@master-server ~]# pt-visual-explain --connect /root/test.sql --user=root --password=123456

#1.2.8 pt-config-diff 功能介绍：比较mysql配置文件和服务器参数用法介绍： pt-config-diff [OPTION...] CONFIG CONFIG [CONFIG...] CONFIG可以是文件也可以是数据源名称，最少必须指定两个配置文件源，就像unix下面的diff命令一样，如果配置完全一样就不会输出任何东西。使用示例：

范例1：查看本地和远程服务器的配置文件差异： [root@master-server ~]# pt-config-diff h=localhost h=192.168.1.102 --user=root --password=123456 18 config differences Variable master-server slave-server ========================= ========================= ========================= binlog_checksum NONE CRC32 general_log_file /data/mysql/data/maste... /data/mysql/data/slave... hostname master-server slave-server innodb_version 5.6.33 5.6.34 log_bin_index /data/mysql/data/maste... /data/mysql/data/slave... log_slave_updates OFF ON relay_log_recovery OFF ON secure_file_priv NULL server_id 101 102 server_uuid d8497104-d7a7-11e6-911... d8773e51-d7a7-11e6-911... slave_net_timeout 3600 5 slave_skip_errors OFF ALL sync_binlog 1 0 sync_master_info 10000 1 sync_relay_log 10000 1 sync_relay_log_info 10000 1 system_time_zone CST EST version 5.6.33-log 5.6.34-log

范例2：比较本地配置文件和远程服务器的差异： [root@master-server ~]# pt-config-diff /usr/local/mysql/my.cnf h=localhost h=192.168.1.102 --user=root --password=123456 6 config differences Variable /usr/local/mysql/my.cnf master-server slave-ser ========================= ================= ================== =============== binlog_checksum none NONE CRC32 innodb_read_io_threads 1000 64 64 innodb_write_io_threads 1000 64 64 log_bin_index master-bin.index /data/mysql/dat... /data/mysql/... server_id 101 101 102 sync_binlog 1 1 0

#1.2.9 pt-mysql-summary 功能介绍：精细地对mysql的配置和sataus信息进行汇总，汇总后你直接看一眼就能看明白。工作原理：连接mysql后查询出status和配置信息保存到临时目录中，然后用awk和其他的脚本工具进行格式化。OPTIONS可以查阅官网的相关页面。用法介绍： pt-mysql-summary [OPTIONS] [-- MYSQL OPTIONS] 使用示例：

汇总本地mysql服务器的status和配置信息： [root@master-server ~]# pt-mysql-summary -- --user=root --password=123456 --host=localhost

#1.2.10 pt-deadlock-logger 功能介绍：提取和记录mysql死锁的相关信息用法介绍： pt-deadlock-logger [OPTION...] SOURCE_DSN 收集和保存mysql上最近的死锁信息，可以直接打印死锁信息和存储死锁信息到数据库中，死锁信息包括发生死锁的服务器、最近发生死锁的时间、死锁线程id、死锁的事务id、发生死锁时事务执行了多长时间等等非常多的信息。使用示例：

查看本地mysql的死锁信息 [root@master-server ~]# pt-deadlock-logger --user=root --password=123456 h=localhost D=test,t=deadlocks server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query localhost 2017-01-11T11:00:33 188 0 0 root 192.168.1.101 huanpc checksums PRIMARY RECORD X w 1 REPLACE INTO huanpc.checksums (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT() AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', member, city)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM huanpc.heihei /checksum table/ localhost 2017-01-11T11:00:33 198 0 0 root 192.168.1.101 huanpc checksums PRIMARY RECORD X w 0 REPLACE INTO huanpc.checksums (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT() AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', member, city)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM huanpc.heihei /checksum table/

#1.2.11 pt-mext 功能介绍：并行查看SHOW GLOBAL STATUS的多个样本的信息。用法介绍： pt-mext [OPTIONS] -- COMMAND 原理：pt-mext执行你指定的COMMAND，并每次读取一行结果，把空行分割的内容保存到一个一个的临时文件中，最后结合这些临时文件并行查看结果。使用示例：

每隔10s执行一次SHOW GLOBAL STATUS，并将结果合并到一起查看 [root@master-server ~]# pt-mext -- mysqladmin ext -uroot -p123456 -i10 -c3

#1.2.12 pt-query-digest 功能介绍：分析查询执行日志，并产生一个查询报告，为MySQL、PostgreSQL、 memcached过滤、重放或者转换语句。 pt-query-digest可以从普通MySQL日志，慢查询日志以及二进制日志中分析查询，甚至可以从SHOW PROCESSLIST和MySQL协议的tcpdump中进行分析，如果没有指定文件，它从标准输入流（STDIN）中读取数据。用法介绍： pt-query-digest [OPTION...] [FILE] 解析和分析mysql日志文件使用示例：（建议：当log很大的时候最好还是将日志文件移到其他机器上进行分析，以免过多耗费本机性能）

范例1：分析本地的慢查询日志文件（本例是慢查询日志，也可以是mysql的其他日志） [root@master-server ~]# pt-query-digest --user=root --password=123456 /data/mysql/data/mysql-slow.log

260ms user time, 30ms system time, 24.85M rss, 204.71M vsz Current date: Mon Jan 16 13:20:39 2017 Hostname: master-server Files: /data/mysql/data/mysql-slow.log Overall: 18 total, 2 unique, 0 QPS, 0x concurrency _____________________ Time range: all events occurred at 2017-01-11 11:00:33 Attribute total min max avg 95% stddev median ============ ======= ======= ======= ======= ======= ======= ======= Exec time 812s 2s 92s 45s 80s 27s 52s Lock time 0 0 0 0 0 0 0 Rows sent 0 0 0 0 0 0 0 Rows examine 0 0 0 0 0 0 0 Query size 1.10k 62 63 62.56 62.76 0.50 62.76

Profile Rank Query ID Response time Calls R/Call V/M Item ==== ================== ============== ===== ======= ===== ============= 1 0x50B84661D4CCF34B 467.9075 57.6% 10 46.7907 16.48 CREATE DATABASE huanqiu 2 0x9CC34439A4FB17E3 344.2984 42.4% 8 43.0373 16.22 CREATE DATABASE huanpc

Query 1: 0 QPS, 0x concurrency, ID 0x50B84661D4CCF34B at byte 2642 _____ This item is included in the report because it matches --limit. Scores: V/M = 16.48 Time range: all events occurred at 2017-01-11 11:00:33 Attribute pct total min max avg 95% stddev median ============ === ======= ======= ======= ======= ======= ======= ======= Count 55 10 Exec time 57 468s 2s 92s 47s 80s 28s 52s Lock time 0 0 0 0 0 0 0 0 Rows sent 0 0 0 0 0 0 0 0 Rows examine 0 0 0 0 0 0 0 0 Query size 55 630 63 63 63 63 0 63 String: Hosts Users root Query_time distribution 1us 10us 100us 1ms 10ms 100ms 1s ####### 10s+ ################################################################ CREATE DATABASE IF NOT EXISTS huanqiu /* pt-table-checksum */\G

Query 2: 0 QPS, 0x concurrency, ID 0x9CC34439A4FB17E3 at byte 3083 _____ This item is included in the report because it matches --limit. Scores: V/M = 16.22 Time range: all events occurred at 2017-01-11 11:00:33 Attribute pct total min max avg 95% stddev median ============ === ======= ======= ======= ======= ======= ======= ======= Count 44 8 Exec time 42 344s 2s 82s 43s 80s 26s 56s Lock time 0 0 0 0 0 0 0 0 Rows sent 0 0 0 0 0 0 0 0 Rows examine 0 0 0 0 0 0 0 0 Query size 44 496 62 62 62 62 0 62 String: Hosts Users root Query_time distribution 1us 10us 100us 1ms 10ms 100ms 1s ######### 10s+ ################################################################ CREATE DATABASE IF NOT EXISTS huanpc /* pt-table-checksum */\G

#整个输出分为三大部分： #1.2.12.1 整体概要（Overall）这个部分是一个大致的概要信息(类似loadrunner给出的概要信息)，通过它可以对当前MySQL的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95%分布值，中位数(median)，标准偏差(stddev)。这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小。

#1.2.12.2 查询的汇总信息（Profile）这个部分对所有“重要”的查询(通常是比较慢的查询)做了个一览表。每个查询都有一个Query ID，这个ID通过Hash计算出来的。pt-query-digest是根据这个所谓的Fingerprint来group by的。 Rank整个分析中该“语句”的排名，一般也就是性能最常的。 Response time “语句”的响应时间以及整体占比情况。 Calls 该“语句”的执行次数。 R/Call 每次执行的平均响应时间。 V/M 响应时间的差异平均对比率。在尾部有一行输出，显示了其他2个占比较低而不值得单独显示的查询的统计数据。

#1.2.12.3 详细信息这个部分会列出Profile表中每个查询的详细信息：包括Overall中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。 pt-query-digest还有很多复杂的操作，这里就不一一介绍了。比如：从PROCESSLIST中查询某个MySQL中最慢的查询：范例2：重新回顾满查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，表结构如下： mysql> use test; Database changed

mysql> CREATE TABLE query_review ( -> checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY, -> fingerprint TEXT NOT NULL, -> sample TEXT NOT NULL, -> first_seen DATETIME, -> last_seen DATETIME, -> reviewed_by VARCHAR(20), -> reviewed_on DATETIME, -> comments TEXT -> ); Query OK, 0 rows affected (0.02 sec)

mysql> select * from query_review; Empty set (0.00 sec)

[root@master-server ~]# pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_review /data/mysql/data/mysql-slow.log

mysql> select * from query_review; +----------------------+------------------------------------------+-----------------------------------------------------------------+---------- | checksum | fingerprint | sample | first_see +----------------------+------------------------------------------+-----------------------------------------------------------------+---------- | 5816476304744969035 | create database if not exists huanqiu | CREATE DATABASE IF NOT EXISTS huanqiu /* pt-table-checksum / | 2017-01-1 | 11295947304747079651 | create database if not exists huanpc | CREATE DATABASE IF NOT EXISTS huanpc / pt-table-checksum */ | 2017-01-1 +----------------------+------------------------------------------+-----------------------------------------------------------------+---------- 2 rows in set (0.00 sec)

从tcpdump中分析： [root@master-server ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes

然后打开另一个终端窗口： [root@master-server ~]# pt-query-digest --type tcpdump mysql.tcp.txt Pipeline process 3 (TcpdumpParser) caused an error: substr outside of string at /usr/bin/pt-query-digest line 3628, <> chunk 93. Will retry pipeline process 2 (TcpdumpParser) 100 more times.

320ms user time, 20ms system time, 24.93M rss, 204.84M vsz Current date: Mon Jan 16 13:24:50 2017 Hostname: master-server Files: mysql.tcp.txt Overall: 31 total, 4 unique, 4.43 QPS, 0.00x concurrency _______________ Time range: 2017-01-16 13:24:43.000380 to 13:24:50.001205 Attribute total min max avg 95% stddev median ============ ======= ======= ======= ======= ======= ======= ======= Exec time 30ms 79us 5ms 967us 4ms 1ms 159us Rows affecte 14 0 2 0.45 1.96 0.82 0 Query size 1.85k 17 200 61.16 192.76 72.25 17.65 .........

#1.2.13 pt-slave-delay 功能介绍：设置从服务器落后于主服务器指定时间。用法介绍： pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST] 原理：通过启动和停止复制sql线程来设置从落后于主指定时间。默认是基于从上relay日志的二进制日志的位置来判断，因此不需要连接到主服务器，如果IO进程不落后主服务器太多的话，这个检查方式工作很好，如果网络通畅的话，一般IO线程落后主通常都是毫秒级别。一般是通过--delay and --delay"+"--interval来控制。--interval是指定检查是否启动或者停止从上sql线程的频繁度，默认的是1分钟检查一次。使用示例：

范例1：使从落后主1分钟，并每隔1分钟检测一次，运行10分钟 [root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --run-time 10m --host=192.168.1.102 2017-01-16T13:32:31 slave running 0 seconds behind 2017-01-16T13:32:31 STOP SLAVE until 2017-01-16T13:33:31 at master position mysql-bin.000005/102554361

范例2：使从落后主1分钟，并每隔15秒钟检测一次，运行10分钟： [root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --interval 15s --run-time 10m --host=192.168.1.102 2017-01-16T13:38:22 slave running 0 seconds behind 2017-01-16T13:38:22 STOP SLAVE until 2017-01-16T13:39:22 at master position mysql-bin.000005/102689359

#1.2.14 pt-slave-restart 功能介绍：监视mysql复制错误，并尝试重启mysql复制当复制停止的时候用法介绍： pt-slave-restart [OPTION...] [DSN] 监视一个或者多个mysql复制错误，当从停止的时候尝试重新启动复制。你可以指定跳过的错误并运行从到指定的日志位置。使用示例：

范例1：监视192.168.1.101的从，跳过1个错误 [root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --skip-count=1 范例2：监视192.168.1.101的从，跳过错误代码为1062的错误。 [root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --error-numbers=1062

#1.2.15 pt-diskstats 功能介绍：是一个对GUN/LINUX的交互式监控工具用法介绍： pt-diskstats [OPTION...] [FILES] 为GUN/LINUX打印磁盘io统计信息，和iostat有点像，但是这个工具是交互式并且比iostat更详细。可以分析从远程机器收集的数据。使用示例：

范例1：查看本机所有的磁盘的状态情况： [root@master-server ~]# pt-diskstats 范例2：只查看本机sdc1磁盘的状态情况： [root@master-server ~]# pt-diskstats --devices-regex vdc1 #ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime 0.9 vdc1 0.0 0.0 0.0 0% 0.0 0.0 5.9 4.0 0.0 0% 0.0 1.0 0% 0 5.9 0.6 0.4 1.0 vdc1 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.0 0.7 0% 0 2.0 0.0 0.7

#1.2.16 pt-summary 功能介绍：友好地收集和显示系统信息概况，此工具并不是一个调优或者诊断工具，这个工具会产生一个很容易进行比较和发送邮件的报告。用法介绍： pt-summary 原理：此工具会运行和多命令去收集系统状态和配置信息，先保存到临时目录的文件中去，然后运行一些unix命令对这些结果做格式化，最好是用root用户或者有权限的用户运行此命令。使用示例：

查看本地系统信息概况 [root@master-server ~]# pt-summary

#1.2.17 pt-stalk 功能介绍：出现问题的时候收集mysql的用于诊断的数据用法介绍： pt-stalk [OPTIONS] [-- MYSQL OPTIONS] pt-stalk等待触发条件触发，然后收集数据帮助错误诊断，它被设计成使用root权限运行的守护进程，因此你可以诊断那些你不能直接观察的间歇性问题。默认的诊断触发条件为SHOW GLOBAL STATUS。也可以指定processlist为诊断触发条件，使用--function参数指定。使用示例：

范例1：指定诊断触发条件为status，同时运行语句超过20的时候触发，收集的数据存放在目标目录/tmp/test下： [root@master-server ~]# pt-stalk --function status --variable Threads_running --threshold 20 --dest /tmp/test -- -uroot -p123456 -h192.168.1.101 范例2：指定诊断触发条件为processlist，超过20个状态为statistics触发，收集的数据存放在/tmp/test目录下： [root@master-server ~]# pt-stalk --function processlist --variable State --match statistics --threshold 20 --dest /tmp/test -- -uroot -p123456 -h192.168.1.101 ....... 2017_01_15_17_31_49-hostname 2017_01_15_17_31_49-innodbstatus1 2017_01_15_17_31_49-innodbstatus2 2017_01_15_17_31_49-interrupts 2017_01_15_17_31_49-log_error 2017_01_15_17_31_49-lsof 2017_01_15_17_31_49-meminfo

#1.2.18 pt-archiver 功能介绍：将mysql数据库中表的记录归档到另外一个表或者文件用法介绍： pt-archiver [OPTION...] --source DSN --where WHERE 这个工具只是归档旧的数据，不会对线上数据的OLTP查询造成太大影响，你可以将数据插入另外一台服务器的其他表中，也可以写入到一个文件中，方便使用source命令导入数据。另外你还可以用它来执行delete操作。特别注意：这个工具默认的会删除源中的数据！！使用示例：

范例1：将192.168.1.101上的huanqiu库的haha表id小于10的记录转移到192.168.1.102上的wangshibo库下的wang_test表内，并归档到/var/log/haha_archive_20170115.log文件中（注意：转移前后，两张表对应转移字段要相同，字段属性最好也要相同；）源数据库机器192.168.1.101的huanqiu库下的haha表在转移前的信息： mysql> select * from huanqiu.haha;
+----+---------------+ | id | name | +----+---------------+ | 1 | changbo | | 2 | wangpengde | | 4 | guocongcong | | 5 | kevin | | 8 | mamin | | 9 | shihonge | | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 8 rows in set (0.01 sec)

目标数据库机器192.168.1.102的wangshibo库下的wang_test表在转移前的信息: mysql> select * from wangshibo.wang_test; +------+-----------+ | id | name | +------+-----------+ | 20 | guominmin | | 21 | gaofei | | 22 | 李梦楠 | +------+-----------+ 3 rows in set (0.00 sec)

接着在192.168.1.101机器上执行转移命令： [root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --dest h=192.168.1.102,D=wangshibo,t=wang_test --file '/var/log/haha_archive_20170115.log' --where "id<=10" --commit-each

上面命令执行成功后，再次观察转移前后信息发现源数据库机器192.168.1.101的huanqiu.haha表数据在转移后，源数据也删除了！ mysql> select * from huanqiu.haha; +----+---------------+ | id | name | +----+---------------+ | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 2 rows in set (0.00 sec)

查看归档日志： [root@master-server ~]# tail -f /var/log/haha_archive_20170115.log 1 changbo 2 wangpengde 4 guocongcong 5 kevin 8 mamin 9 shihonge

目标数据库192.168.1.102的wangshibo.wang_test表内已经移转到了新数据 mysql> select * from wangshibo.wang_test; +------+-------------+ | id | name | +------+-------------+ | 20 | guominmin | | 21 | gaofei | | 22 | 李梦楠 | | 1 | changbo | | 2 | wangpengde | | 4 | guocongcong | | 5 | kevin | | 8 | mamin | | 9 | shihonge | +------+-------------+ 9 rows in set (0.00 sec)

范例2：将192.168.1.101上的huanqiu库的haha表里id小于10的记录归档到haha_log_archive_2017.10.10.log文件中: mysql> select * from huanqiu.haha; +----+---------------+ | id | name | +----+---------------+ | 1 | changbo | | 2 | wangpengde | | 4 | guocongcong | | 5 | kevin | | 8 | mamin | | 9 | shihonge | | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 8 rows in set (0.00 sec)

[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --file 'haha_log_archive_2017.10.10.log' --where "id<=10" --commit-each

转移后的源数据已被删除 mysql> select * from huanqiu.haha; +----+---------------+ | id | name | +----+---------------+ | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 2 rows in set (0.00 sec)

查看归档文件 [root@master-server ~]# cat haha_log_archive_2017.10.10.log 1 changbo 2 wangpengde 4 guocongcong 5 kevin 8 mamin 9 shihonge

范例3：删除192.168.1.101上的huanqiu库的haha表中id小于10的记录： mysql> select * from huanqiu.haha; +----+---------------+ | id | name | +----+---------------+ | 1 | changbo | | 2 | wangpengde | | 4 | guocongcong | | 5 | kevin | | 8 | mamin | | 9 | shihonge | | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 8 rows in set (0.00 sec)

[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --purge --where 'id<=10' --no-check-charset <br>再次查看，发现数据已成功删除！ mysql> select * from huanqiu.haha; +----+---------------+ | id | name | +----+---------------+ | 11 | zhanglei | | 15 | zhanghongmiao | +----+---------------+ 2 rows in set (0.00 sec)

#1.2.19 pt-find 功能介绍：查找mysql表并执行指定的命令，和gnu的find命令类似。用法介绍： pt-find [OPTION...] [DATABASE...] 默认动作是打印数据库名和表名使用示例：

查找192.168.1.101中1天以前创建的InnoDB的表 ,并打印。 [root@master-server ~]# pt-find --ctime +1 --host=192.168.1.101 --engine InnoDB --user=root --password=123456 huanpc._heihei_new huanpc.checksums huanqiu._haha_new huanqiu.checksums huanqiu.heartbeat mysql.innodb_index_stats mysql.innodb_table_stats mysql.slave_master_info mysql.slave_relay_log_info mysql.slave_worker_info

范例2：查找192.168.1.101中1天以前更改过的数据库名字匹配%huanqiu%的并且引擎为Myisam的表，并将表的引擎更改为Innodb引擎。先查找出192.168.1.101上1天以前更改过的数据库名字匹配%huanqiu%的并且引擎为Myisam的表 [root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456 huanqiu._haha_new huanqiu.checksums huanqiu.heartbeat [root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456 huanpc._heihei_new huanpc.checksums

再将查找出的表的引擎改为Innodb [root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB" [root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"

最后再检查对应数据表的引擎范例3：查找192.168.1.101中huanqiu库和huanpc库中的空表，并删除。

1 [root@master-server ~]# pt-find --empty huanqiu huanpc --host=192.168.1.101 --user=root --password=123456 --exec-plus "DROP TABLE %s" 范例4：查找192.168.1.101中超过100M的表 [root@master-server ~]# pt-find --tablesize +100M --host=192.168.1.101 --user=root --password=123456

#1.2.20 pt-kill 功能介绍： Kill掉符合指定条件mysql语句用法介绍： pt-kill [OPTIONS] 加入没有指定文件的话pt-kill连接到mysql并通过SHOW PROCESSLIST找到指定的语句，反之pt-kill从包含SHOW PROCESSLIST结果的文件中读取mysql语句使用示例：

范例1：查找192.168.1.101数据库服务器运行时间超过60s的语句，并打印 [root@master-server ~]# pt-kill --busy-time 60 --print --host=192.168.1.101 --user=root --password=123456 范例2：查找192.168.1.101数据库服务器运行时间超过60s的语句，并kill [root@master-server ~]# pt-kill --busy-time 60 --kill --host=192.168.3.135 --user=root --password=123456 范例3：从proccesslist文件中查找执行时间超过60s的语句 [root@master-server ~]# mysql -uroot -p123456 -h192.168.1.101 -e "show processlist" > processlist.txt Warning: Using a password on the command line interface can be insecure. [root@master-server ~]# pt-kill --test-matching processlist.txt --busy-time 60 --print

lblog_wyhsf4ea

查看某个sql文件在两个服务器的运行结果范例： [root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /root/test.sql

Logs