Percona Toolkit工具使用
Percona Toolkit简称pt工具—PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等
下载地址为： https://www.percona.com/downloads/percona-toolkit/LATEST/
安装：
| 1 2 3 4 5 6 7 8 9 10 |
|
| 1 2 3 4 5 6 7 |
|
1.pt-archive ----MySQL的在线归档，无影响生产
2.pt-kill -----自定义查杀。确保慢查询及恶性攻击对生产无影响
3.pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响
4.pt-query-digest -----慢查询Log的分析。
5.pt-slave-delay ---就是指定从库比主库延迟多长时间
6.pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理 这两个一搬是搭配使用
7. pt-find ---找出几天之前建立的表
8. pt-slave-restart -----主从报错，跳过报错
9.pt-summary ---整个系统的的概述
10.pt-mysql-summary ---MySQL的表述，包括配置文件的描述
11.pt-duplicate-key-checker ---检查数据库重复索引
pt-archive解释使用：##归档 用此操作的表必须有主键。
1.归档历史数据；
2.在线删除大批量数据；
3.数据导出和备份；
4.数据远程归档；
5.数据清理
1.pt-archive –help 参数，用这可以help一下
几个常用说明
--limit10000 每次取1000行数据用pt-archive处理
--txn-size 1000 设置1000行为一个事务提交一次，
--where‘id<3000‘ 设置操作条件
--progress5000 每处理5000行输出一次处理信息
--statistics 输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt- archive都会输出执行过程的）
--charset=UTF8 指定字符集为UTF8—这个最后加上不然可能出现乱码。
--bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作)
例：
(1).将表中CREATE_DATE字段小于2018-01-01 00:00:00时间的数据进行归档,不删除原表记录
pt-archiver --source h=192.168.X.X ,P=3306,u=用户,p=密码,D=库名,t=表名 --dest h=目标IP,P=3306,u=用户,p=密码,D=库名,t=表名 --no-check-charset --where CREATE_DATE<"2015-10-01 00:00:00"' --progress 5000 --no-delete --limit=10000 –statistics
注：字符要加上双单 ---"2015-10-01"
(2).阿里云RDS保留三个月数据
pt-archiver --source h=RDS地址,P=端口,u=用户名,p=密码,D=库名,t=表名 --where nextfetch<REPLACE(UNIX_TIMESTAMP(SUBDATE(current_timestamp(3),INTERVAL 92 DAY)),".","") --purge --limit=5000 --no-check-charset --no-version-check --statistics --progress 10000 （定时执行脚本进行删除操作）
注： 阿里云RDS要加 --no-version-check参数
（3）.远程归档
（远程归档） pt-archiver --charset ‘utf8’ --source h=127.0.0.1,P=6006,u=root,p=‘密码’,D=源库,t=源表 --dest h=10.59.1.152,P=6006,u=sys_dba,p=‘密码’,D=目标库,t=目标表 --where 'periodID=1' --progress 5000 --limit=5000 –statistics
pt-kill ##解释说明 kill MySQL连接的一个工具 kill掉你想Kill的任何语句，特别出现大量的阻塞，死锁，某个有问题的sql导致mysql负载很高黑客攻击。当有很多语句时你不可能用show processlist去查看，当QPS很高时，你根本找不到你找的语句或ID,这时就可以用Pt-kill来完成；他可以根据运行时间，开源IP,用户名，数据库名。SQL语句,sleep,running 等状态进行匹配然后kill
PT-kill 参数 --help
常用参数说明
--daemonize 放在后台以守护进程的形式运行；
--interval 多久运行一次，单位可以是s,m,h，d等默认是s –不加这个默认是5秒
--victims 默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，他们只是长期等待 这种种匹配按时间查询，杀死一个时间最高值。
--all 杀掉所有满足的线程
--kill-query 只杀掉连接执行的语句，但是线程不会被终止
--print 打印满足条件的语句
--busy-time 批次查询已运行的时间超过这个时间的线程；
--idle-time 杀掉sleep 空闲了多少时间的连接线程，必须在--match-command sleep时才有效—也就是匹配使用 -- –match-command 匹配相关的语句。
----ignore-command 忽略相关的匹配。 这两个搭配使用一定是ignore-commandd在前 match-command在后，
--match-db cdelzone 匹配哪个库
command有：Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump
例：
---杀掉空闲链接sleep 5秒的 SQL 并把日志放到/home/pt-kill.log文件中
/usr/bin/pt-kill --user=用户名 --password=密码 --match-command Sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
---查询SELECT 超过1分钟路
/usr/bin/pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
--- Kill掉 select IFNULl.*语句开头的SQL
pt-kill --user=用户名 --password=密码 --victims all --busy-time=0 --match-info="select IFNULl.*" --interval 1 -S /tmp/mysqld.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill123.log &
----kill掉state Locked
/usr/bin/pt-kill --user=用户名 --password=密码 --victims all --match-state='Locked' --victim all --interval 5 --kill --daemonize -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
---kill掉 qz_business_servic 库，web为110.59.2.37的链接
pt-kill --user=用户名 --password=密码 --victims all --match-db='qz_business_service' --match-host='10.59.2.37' --kill --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
---指定哪个用户kill
pt-kill --user=用户名 --password=密码 --victims all --match-user='root' --kill --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
---查询SELECT 超过1分钟路
pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
----kill掉 command query | Execute
pt-kill --user=用户名 --password=密码 --victims all --match-command= "query|Execute" --interval 5 --kill --daemonize -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
注：上面都是说针对手动或紧急情况下处理的事
pt-online-schema-change ###简称 pt-osc 在线更改表结构 MySQL 大字段的DDL操作：加减字段、索引、修改字段属性
注：被操作的表如果有 触发器，或外键不可用
工作原理：
1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2.在新表执行alter table 语句（速度应该很快）
3.在原表中创建触发器3个触发器分别对应insert,update,delete操作
4.以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5.Rename 原表到old表中，在把临时表Rename为原表
pt-online-schema-change --help 参数
--max-load 默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
--critical-load 默认为Threads_running=50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停
--user: -u，连接的用户名
--password： -p，连接的密码
--database： -D，连接的数据库
--port -P，连接数据库的端口
--host: -h，连接的主机地址
--socket: -S，连接的套接字文件
--statistics 打印出内部事件的数目，可以看到复制数据插入的数目。
--dry-run 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。
--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。
--execute 确定修改表，则指定该参数。真正执行。
--dry-run与--execute必须指定一个，二者相互排斥。
--print 打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。
--progress 复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
--quiet -q，不把信息标准输出。
例：
（1）.对t=learn_tracedb 对learn_tracedb 表的updateTime 列添加索引
pt-online-schema-change --user=root --password=‘密码' --port=6006 --host=127.0.0.1 --critical-load Threads_running=100 --alter "ADD INDEX index_updateTime (updateTime)" D=acc_tasktrace,t=learn_tracedb --print --execute
（2）.对cware_user_point 添加periodid列
pt-online-schema-change --user=root --password=‘密码' --port=6006 --host=127.0.0.1 --critical-load Threads_running=200 --alter "ADD COLUMN periodID int(11)" D=acc_cwaretiming,t=cware_user_point --print –execute
pt-query-digest ###-----慢查询Log的分析
pt-query-digest --help参数
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。
例：
（1）.分析指定时间段的慢查询
pt-query-digest /home/zb/data/my6006/log/mysql_slow_2018-11-07.log --since ‘2018-11-07 00:00:00’ --until ‘2018-11-11 15:50:00’
Overall: 总共有多少条查询，上例为总共2.7k个查询。
Time range: 查询执行的时间范围。
unique: 唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询，该例为64。
total: 总计
min:最小
max: 最大
avg:平均 95%: 把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值。
median: 中位数，把所有值从小到大排列，位置位于中间那个数。
针对某个用户的慢查询
（2）.pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /web/mysql/data/chinapen40-slow.log > slow_report5.log
pt-slave-delay ###---就是指定从库比主库延迟多长时间—---从库上执行
工作原理： 通过启动和停止从服务器的sql线程来设置从落后于主。它是通过slave的relay log（中继日志）的position(偏移量），不断启动，关闭replication SQL thread来保持主从一直延时固定长的时间来实现。因此不需要连接到主服务器。如果IO进程不落后主服务器太多的话，这个检查方式还是有效的,如果IO线程延时过大，pt-slave-delay也可以连接到主库来获取binlog的位置信息。
pt-slave-delay --delay=1m --interval=15s --run-time=10m u=root,p=123456,h=127.0.0.1,P=6006 --从服务器上执行
参数解释：
--delay ：从库延迟主库的时间，上面为1分钟。
--interval ：检查的间隔时间，上面为15s检查一次。（可选），不选则1分钟检查一次(默认)。
--run-time ：该命令运行时间，上面为该命令运行10分钟关闭。（可选），不选则永远运行。--一搬不加此参数
pt-table-checksum & pt-table-sync ###-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理 这两个一搬是搭配使用（一搬主从不一样肯定要查一下，不能直接修复就完事了。这个大家可用于辅助工具。）
参数讲解：
replicate=test.checksum：主从不一致的结果放到哪一张表中，一般我放在一个既有的数据库中，这个checksum表由pt-table-checksum工具自行建立。
databases=testdb ：我们要检测的数据库有哪些，这里是testdb数据库，如果想检测所有数据库那么就不要写这个参数了，如果有多个数据库，我们用逗号连接就可以了。 host='127.0.0.1' ：主库的IP地址或者主机名。
user=dba ：主机用户名。 ---确定此用户可以访问主从数据库
port=6006：主库端口号。
recursion-method=hosts ：主库探测从库的方式。
empty-replicate-table：清理上一次的检测结果后开始新的检测。
no-check-bin-log-format：不检查二进制日志格式，鉴于目前大多数生产数据库都将二进制日志设置为“ROW”格式，而我们的pt-table-checksum会话会自行设定使用“STATEMENT”格式，所以这个选项请务必加上。
例：
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --recursion-method=hosts --databases=log_manage h=localhost,u=sys_dba,p='密码',P=6006
结果参数：
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名
检测有差异之后到从库上执行一下修复
注：用这个前提是此表必须要有主键或唯一索引
pt-table-sync --sync-to-master --replicate=test.checksums h=127.0.0.1,u=dba,P=6006,p=‘密码’ --print ---------只打印不执行—看详细
pt-table-sync --sync-to-master --replicate=test.checksums h=127.0.0.1,u=dba,P=6006,p=‘密码’ --execute ----开始执行就修复了，再看一下就OK了 再检测就没有了
pt-slave-restart ###-----主从报错，跳过报错（基本不用）
--always :永不停止slave线程，手工停止也不行
--ask-pass :替换-p命令，防止密码输入被身后的开发窥屏
--error-numbers：指定跳过哪些错误，可用,进行分隔
--error-text：根据错误信息进行匹配跳过
--log：输出到文件
--recurse：在主端执行，监控从端
--runtime：工具执行多长时间后退出：默认秒， m=minute,h=hours,d=days
--slave-user --slave-password ：从库的账号密码，从主端运行时使用
--skip-count：一次跳过错误的个数，胆大的可以设置大些，不指定默认1个
--master-uuid :级联复制的时候，指定跳过上级或者上上级事务的错误
--until-master :到达指定的master_log_pos,file位置后停止， 格式：”file:pos“
--until-relay :和上面一样，但是时根据relay_log的位置来停止
例：
自动跳过主从同步1032的报错 建议大家在从库上如下这个就可以了，多个以逗号隔开就可以了
/usr/bin/pt-slave-restart --user=root --password=‘密码' --port=6006 --host=127.0.0.1 --error-numbers=1032
pt-summary ###打印出来的信息包括：CPU、内存、硬盘、网卡等信息，还包括文件系统、磁盘调度和队列大小、LVM、RAID、网络链接信息、netstat 的统计，以及前10的负载占用信息和vmstat信息
使用：pt-summary 就可以
其它参考：