CNDBA社区
1 PT 工具安装
Percona Toolkit 简称 pt 工具，是 Percona 公司开发用于管理 MySQL 的工具，里面有很多实用多脚本，可提高 DBA 的工作效率亦或是降低数据库运维风险。
官网地址：
下载并安装：
[dave@www.cndba.cn ~]# wget -c install https://www.percona.com/downloads/percona-toolkit/3.0.1/binary/redhat/7/x86_64/percona-toolkit-3.0.1-1.el7.x86_64.rpm
[dave@www.cndba.cn ~]# yum install percona-toolkit-3.0.1-1.el7.x86_64.rpm
Yum 安装会自动解决依赖包。
安装之后，相关程序放在/usr/bin 目录下：
[dave@www.cndba.cn bin]# pwd
/usr/bin
[dave@www.cndba.cn bin]# ll pt-* | wc -l
34
[dave@www.cndba.cn bin]# ll pt-*
-rwxr-xr-x 1 root root 41479 Feb 19 2017 pt-align
-rwxr-xr-x 1 root root 263436 Feb 19 2017 pt-archiver
-rwxr-xr-x 1 root root 168983 Feb 19 2017 pt-config-diff
-rwxr-xr-x 1 root root 166208 Feb 19 2017 pt-deadlock-logger
-rwxr-xr-x 1 root root 164925 Feb 19 2017 pt-diskstats
-rwxr-xr-x 1 root root 169055 Feb 19 2017 pt-duplicate-key-checker
-rwxr-xr-x 1 root root 49889 Feb 19 2017 pt-fifo-split
-rwxr-xr-x 1 root root 149725 Feb 19 2017 pt-find
-rwxr-xr-x 1 root root 67036 Feb 19 2017 pt-fingerprint
-rwxr-xr-x 1 root root 133185 Feb 19 2017 pt-fk-error-logger
-rwxr-xr-x 1 root root 218379 Feb 19 2017 pt-heartbeat
-rwxr-xr-x 1 root root 226169 Feb 19 2017 pt-index-usage
-rwxr-xr-x 1 root root 32344 Feb 19 2017 pt-ioprofile
-rwxr-xr-x 1 root root 250057 Feb 19 2017 pt-kill
-rwxr-xr-x 1 root root 21752 Feb 19 2017 pt-mext
-rwxr-xr-x 1 root root 5918944 Feb 19 2017 pt-mongodb-query-digest
-rwxr-xr-x 1 root root 6115680 Feb 19 2017 pt-mongodb-summary
-rwxr-xr-x 1 root root 102146 Feb 19 2017 pt-mysql-summary
-rwxr-xr-x 1 root root 394657 Feb 19 2017 pt-online-schema-change
-rwxr-xr-x 1 root root 24593 Feb 19 2017 pt-pmp
-rwxr-xr-x 1 root root 520677 Feb 19 2017 pt-query-digest
-rwxr-xr-x 1 root root 75712 Feb 19 2017 pt-show-grants
-rwxr-xr-x 1 root root 37723 Feb 19 2017 pt-sift
-rwxr-xr-x 1 root root 145551 Feb 19 2017 pt-slave-delay
-rwxr-xr-x 1 root root 128909 Feb 19 2017 pt-slave-find
-rwxr-xr-x 1 root root 181314 Feb 19 2017 pt-slave-restart
-rwxr-xr-x 1 root root 69579 Feb 19 2017 pt-stalk
-rwxr-xr-x 1 root root 90728 Feb 19 2017 pt-summary
-rwxr-xr-x 1 root root 422762 Feb 19 2017 pt-table-checksum
-rwxr-xr-x 1 root root 398362 Feb 19 2017 pt-table-sync
-rwxr-xr-x 1 root root 246863 Feb 19 2017 pt-table-usage
-rwxr-xr-x 1 root root 330981 Feb 19 2017 pt-upgrade
-rwxr-xr-x 1 root root 177018 Feb 19 2017 pt-variable-advisor
-rwxr-xr-x 1 root root 102277 Feb 19 2017 pt-visual-explain
[dave@www.cndba.cn bin]#
2 PT 工具说明
2.1 命令帮助
PT 工具的使用手册下载地址如下：
https://learn.percona.com/hubfs/Manuals/Percona_Toolkit/Percona-Toolkit-3-0/PerconaToolkit3.3.1.pdf
官方手册里对每个命令和参数都有说明，也可以使用 command —help 来查看每个命令的具体作用和使用方法。
[dave@www.cndba.cn bin]# pt-kill --help
pt-kill kills MySQL connections. pt-kill connects to MySQL and gets queries from
SHOW PROCESSLIST if no FILE is given. Else, it reads queries from one or more
FILE which contains the output of SHOW PROCESSLIST. If FILE is -, pt-kill reads
from STDIN. For more details, please use the --help option, or try 'perldoc
/usr/bin/pt-kill' for complete documentation.
Usage: pt-kill [OPTIONS] [DSN]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--charset=s -A Default character set
--config=A Read this comma-separated list of config files; if
specified, this must be the first option on the
command line
--create-log-table Create the --log-dsn table if it does not exist
--daemonize Fork to the background and detach from the shell
--database=s -D The database to use for the connection
--defaults-file=s -F Only read mysql options from the given file
……
2.2 工具分类
2.2.1 开发类工具
2.2.1.1 pt-duplicate-key-checker
功能：
检查mysql中冗余的索引。
用法：
pt-duplicate-key-checker –user=root –password=xxxx –host=localhost –socket=/tmp/mysql.sock
2.2.1.2 pt-online-schema-change
功能：
不锁表的情况下,修改表结构.
实现原理：
创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。
用法：
添加索引
pt-online-schema-change -u root -h 10.250.7.50 -p yang –critical-load=”Threads_running=200″ –alter=’add key indx_vid(vid) ‘ –execute D=houyi,t=ga删除字段
pt-online-schema-change -u root -h 10.250.7.50 -p yang –alter=’drop column vid ‘ –execute D=houyi,t=ga远程加索引
pt-online-schema-change -usjh -P3306 -h192.168.11.70 -psjh –alter=’add key idx_c1_c2(c1,c2)’ –execute D=sjh,t=yw
2.2.1.3 pt-show-grants
功能：
导出mysql.user权限。
工具执行流程：
1、先查找所有用户和Host
2、然后逐个执行show grants
用法：
pt-show-grants -u pt -p 213456 -S /tmp/mysql.sock
2.2.1.4 pt-upgrade
功能：
在多台服务器上执行查询，并比较有什么不同！这在升级服务器的时候非常有用，可以先安装并导数据到新的服务器上，然后使用这个工具跑一下sql看看有什么不同，可以找出不同版本之间的差异。
用法:
只查看某个sql在两个服务器的运行结果范例：
pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=cndba –query=”select * from user_data.collect_data limit 5″查看文件中的对应sql在两个服务器的运行结果范例：
pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=cndba aaa.sql查看慢查询中的对应的查询SQL在两个服务器的运行结果范例：
pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=cndba slow.log
此外还可以执行compare的类型，主要包含三个query_times,results,warnings，比如下面的例子，只比较sql的执行时间
pt-upgrade h=192.168.3.91 h=192.168.8.92 –user=root –password=cndba –query=”select * from user_data.collect_data” –compare query_times
2.2.2 性能类工具
2.2.2.1 pt-index-usage
功能：
从log文件中读取插入语句，并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。
用法：
从慢查询中的sql查看索引使用情况范例：
pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=root
将分析结果保存到数据库范例：
pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=cndba –no-report –create-save-results-database
2.2.2.2 pt-pmp
功能：
为查询程序执行聚合的GDB堆栈跟踪，先进性堆栈跟踪，然后将跟踪信息汇总。
用法：
pt-pmp -p 21933
pt-pmp -b /usr/local/mysql/bin/mysqld_safe
2.2.2.3 pt-visual-explain
功能：
格式化explain出来的执行计划按照tree方式输出，方便阅读.
用法：
mysql -uroot -pcndba -e “explain select email from test.collect_data where id=101992419″ |pt-visual-explain
2.2.3 配置类工具
2.2.3.1 pt-config-diff
功能：
对比配置文件中不一致的地方。
用法：
pt-config-diff h=192.168.8.85 h=192.168.8.22 -uwwg -pwwg
2.2.3.2 pt-mysql-summary
功能：
精细地对mysql的配置和sataus信息进行汇总。
用法：
pt-mysql-summary–user=root –password=root –host=localhost
2.2.3.3 pt-variable-advisor
功能：
分析mysql的参数变量，并可能对问题参数提出建议。
用法:
pt-variable-advisor –user=root –password=root localhost
2.2.4 监控类工具
2.2.4.1 pt-deadlock-logger
功能：
打印死锁信息。
核心参数 :
–daemonize ： 守护进程
–create-dest-table ：创建指定的表。
–dest ：创建存储死锁信息的表。
–database ：-D，指定链接的数据库。
–table ：-t，指定存储的表名。
–log ：指定死锁日志信息写入到文件。
–run-time ：运行次数，默认永久
–interval ：运行间隔时间，默认30s。
u，p，h，P ：链接数据库的信息。
用法：
把本地死锁信息永久保存在 远端 test.deadlock222的表里
pt-deadlock-logger –user=wwg –password=wwg –host=192.168.8.22 –interval=3 –create-dest-table –dest u=wwg,p=wwg,h=192.168.8.85,D=test,t=deadlock222
(root@localhost)[test]> select * from deadlock222;
直接打印：
[root@soccer-web-8-22-dev ~]# pt-deadlock-logger -uroot -proot -hlocalhost
2.2.4.2 pt-fk-error-logger
功能：
提取和记录mysql外键错误信息。
用法：
pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign_key_errors
写到远端：
pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign_key_errors –user=root –password=root –host=192.168.1.249
2.2.4.3 pt-mext
功能：
并行查看SHOW GLOBAL STATUS的多个样本的信息。
用法：
每隔10s执行一次SHOW GLOBAL STATUS，并将结果合并到一起查看
pt-mext -r — mysqladmin -uroot -p’root’ –socket=/tmp/mysql.sock ext -i10 -c2
-i sleep
-c 迭代多少次
2.2.4.4 pt-query-digest
功能：
分析查询执行日志，并产生一个查询报告，为MySQL、PostgreSQL、 memcached过滤、重放或者转换语句。
用法：
分析本地的慢查询文件
pt-query-digest –user=root –password=cndba /data/dbdata/localhost-slow.log
重新回顾慢查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，表结构如下：
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 );
从远端抓取slowlog
pt-query-digest –user=root –password=cndba –review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log
2.2.5 复制类工具
2.2.5.1 pt-heartbeat
用法：
pt-heartbeat [OPTION…] [DSN] –update|–monitor|–check|–stop
主库上起个守护进程
pt-heartbeat -D test –update –user=root –password=cndba -h192.168.8.22 –create-table –daemonize
从库监控延迟(实时监控)
pt-heartbeat -D test –monitor –user=wwg –password=wwg -h192.168.8.22 –master-server-id=203306
只监控一次就退出
pt-heartbeat -D test –check –user=root –password=cndba -h192.168.8.22 –master-server-id=203306
2.2.5.2 pt-slave-delay
功能：
设置从服务器落后于主服务器指定时间。
用法：
使从落后主1分钟，并每隔15秒钟检测一次，运行10分钟（不加 run-time 会永久执行）
pt-slave-delay –daemonize –user=root –password=root –delay 1m –interval 15s –run-time 10m –host=192.168.3.92
2.2.5.3 pt-slave-find
功能：
查找和打印mysql所有从服务器复制层级关系
用法：
查找主库为192.168.8.22 所有从库
pt-slave-find –user=wwg –password=wwg –host=192.168.8.22
2.2.5.4 pt-slave-restart
功能：
监视mysql复制错误，并尝试重启mysql复制当复制停止的时候。
用法：
跳过错误数：
pt-slave-restart –user=root –password=cndba –host=192.168.3.92 –skip-count=1
跳过错误类型：
pt-slave-restart –user=root –password=cndba –host=192.168.3.92 –error-numbers=1062
2.2.5.5 pt-table-checksum
功能：
检查mysql复制一致性
核心参数：
–no-check-binlog-format 如果不是row格式 都要带上
–replicate-check-only 只显示问题的表
用法：
主库上执行：
pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate=sjh.checksums –create-replicate-table -h192.168.1.134 -P 3306 -u wwg -p wwg –recursion-method=”processlist”
2.2.5.6 pt-table-sync
功能：
高效同步mysql表的数据
用法：
1.打印 ip 为主库ip
pt-table-sync –replicate=sjh.checksums –print –databases=hc_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg2.执行
pt-table-sync –replicate=sjh.checksums –execute –databases=hc_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg
2.2.6 系统类工具
2.2.6.1 pt-diskstats
功能：
查看服务器的IO。
用法：
pt-diskstats –interval=5 –show-timestamps –columns-regex=sdb
2.2.6.2 pt-fifo-split
功能：
可以模拟切割文件，并通过管道传递给先入先出队列而不用真正的切割文件。
用法：
一个每次读取一百万行，指定fifo文件为/tmp/my-fifo，并使用load data命令导入到mysql中。
pt-fifo-split infile.txt –fifo /tmp/my-fifo –lines 1000000 while [ -e /tmp/my-fifo ]; do mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘/tmp/my-fifo’ into table load_test fields terminated by ‘/t’ lines terminated by ‘/n’ (col1, col2);” sleep 1; done
2.2.6.3 pt-summary
功能：
友好地收集和显示系统信息概况
用法：
pt-summary
2.2.6.4 pt-stalk
功能：
在MySQL服务器出现短暂(5~30秒)的性能波动的时候，一般的性能监控工具都很难抓住故障现场，也就很难收集对应较细粒度的诊断信息。另外，如果这种波动出现的频率很低，例如几天才一次，我们也很难人为的抓住现场，收集数据。这正是pt-stalk所解决的问题。
用法：
pt-stalk –collect-tcpdump –function status –variable Threads_connected –threshold 2 –daemonize — –user=root –password=’root’
查看日志(或 –log 指定日志输出)：
tail -f /var/log/pt-stalk.log
查看生成的信息( –dest 指定目录)：
ls /var/lib/pt-stalk/
2.2.6.5 pt-ioprofile
功能：
对某个pid附加一个strace进程进行io分析。
用法：
pt-ioprofile –profile-pid=22544 –cell=sizes
先用 iotop -o 排查 什么进程 占用io 更高。
再用 pt-ioprofile 查看 pid 哪些 所属的文件 读写更 频繁。
2.2.7 实用类工具
2.2.7.1 pt-archiver
功能：
将mysql数据库中表的记录归档到另外一个表或者文件，也可以直接进行记录的删除操作。
用法：
每5000条 删除1次，每隔2000行输出一次 数据(基本不会照成延迟)
pt-archiver –source D=ibilling_pay_hupu,t=purchase_order –user=root –password=’root’ -S /tmp/mysql.sock –where “create_datetime<=’2015-06-20 00:00:00′” –purge –limit=5000 –progress 2000 –why-quit –no-check-charset > /opt/1.log用于把数据导出文件，不用删除原表中数据
pt-archiver –source h=127.0.0.1,P=58886,D=test,t=t_info_refresh –no-check-charset –where ‘id>1′ –progress 4000 –no-delete –file “/tmp/pt-archiver.sql” –limit=10000把数据归档至 归档库(默认保留最后条)。
pt-archiver –source P=3306,D=wwg,t=yw –user=root –password=root -S /tmp/mysql.sock –dest h=192.168.8.85,P=3306,u=wwg,p=wwg,D=ibilling,t=his_yw –no-check-charset –where ‘id < 5001′ –no-delete –statistics
2.2.7.2 pt-find
功能：
查找mysql表并执行指定的命令。
用法:
1.查找192.168.8.22中1天以前创建的InnoDB的表 ,并打印
pt-find –ctime +1 –host=localhost –engine InnoDB –user=root –password=root
2.查找192.168.8.22中1天以前更改过的数据库名字匹配%mysql%的并且引擎为MYISAM的表，并将表的引擎更改为InnoDB引擎。
pt-find –mtime +1 –dblike mysql –engine MyISAM –host=localhost –user=root –password=root –exec “ALTER TABLE %D.%N ENGINE=InnoDB”
3.查找192.168.8.22中wwg库和zhang库中的空表，并删除。
pt-find –empty wwg zhang –host=192.168.3.135 –user=root –password=cndba –exec-plus “DROP TABLE %s”;
4.查找192.168.8.22中超过10G的表：
pt-find –tablesize +10G –host=192.168.3.135 –user=root –password=cndba
2.2.7.3 pt-kill
功能：
找出匹配条件 myql 语句 kill掉。
核心参数：
–victims all 配置所有连接，默认是一个
–interval 30 默认每30秒循环执行一次，可以根据实际情况调整,如果不加，只执行一次；加上的话，会在后台不停的执行。
默认会过滤掉复制线程，请不必担心杀掉复制线程。
–busy-time 批次查询已运行的时间超过这个时间的线程；
–idle-time 杀掉sleep 了多少时间的连接线程，必须在–match-command sleep时才有效
–match-state 匹配状态
