Percona Toolkit 工具检查 Mysql

前言

Percona Toolkit 简称 pt 工具，PT-Tools 是 Percona 公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位 IO 占用高的表文件、在线 DDL 等，DBA 熟悉掌握后将极大提高工作效率。

参考文献

Percona Toolkit手册：https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html

Percona Toolkit下载：https://www.percona.com/downloads/percona-toolkit/LATEST/

Percona Toolkit下载（清华镜像）：https://mirrors.cnnic.cn/percona/tools/yum/release/latest/RPMS/x86_64/percona-toolkit-3.2.1-1.el6.x86_64.rpm。

安装步骤

使用wget下载到本地

wget --no-check-certificate https://mirrors.cnnic.cn/percona/tools/yum/release/latest/RPMS/x86_64/percona-toolkit-3.2.1-1.el6.x86_64.rpm

安装 pt 工具的依赖软件

yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y

安装 pt 工具

yum install percona-toolkit-3.2.1-1.el6.x86_64.rpm

查看安装情况

[root@localhost home]# yum list | grep percona-toolkit
percona-toolkit.x86_64                      3.2.1-1.el7                @/percona-toolkit-3.2.1-1.el7.x86_64
[root@localhost home]# pt-table-checksum --help

会自动安装perl依赖包和percona-toolkit工具，默认安装路径在/usr/bin/路径，帮助文档路径/usr/share/man/man1/，可以通过man 命名直接获取帮助文档。

shell>pt
pt-align                  pt-fifo-split             pt-kill                   pt-pmp                    pt-slave-restart          pt-variable-advisor
pt-archiver               pt-find                   pt-mext                   pt-query-digest           pt-stalk                  pt-visual-explain
ptaskset                  pt-fingerprint            pt-mongodb-query-digest   pt-secure-collect         pt-summary                ptx
pt-config-diff            pt-fk-error-logger        pt-mongodb-summary        pt-show-grants            pt-table-checksum         
pt-deadlock-logger        pt-heartbeat              pt-mysql-summary          pt-sift                   pt-table-sync             
pt-diskstats              pt-index-usage            pt-online-schema-change   pt-slave-delay            pt-table-usage            
pt-duplicate-key-checker  pt-ioprofile              pt-pg-summary             pt-slave-find             pt-upgrade

pt工具主要包含如上命令，今天主要介绍几个DBA必会的pt命令。

使用介绍

pt-archive

pt-archive是MySQL的在线归档工具，无影响生产，用此命令操作的表必须有主键。它实现的功能包括：

归档历史数据
在线删除大批量数据
数据导出和备份
数据远程归档
数据清理

--limit 10000 每次取1000行数据用pt-archive处理
--txn-size 1000 设置1000行为一个事务提交一次
--where 'id<3000' 设置操作条件
--progress 5000 每处理5000行输出一次处理信息
--statistics 输出执行过程及最后的操作统计（只要不加上--quiet，默认情况下pt- archive都会输出执行过程的）
--charset=UTF8 指定字符集为UTF8 这个最好加上不然可能出现乱码。
--bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作)

使用示例：

归档,不删除原表数据(–no-delete)

pt-archiver --source h=源ip,P=源端口,u=用户,p=密码,D=库名,t=表名 --dest h=目标IP,P=端口,u=用户,p=密码,D=库名,t=表名 --no-check-charset --where 'ID<100' --progress 5000 --no-delete --limit=10000 –statistics

归档, 删除原表记录（不用加no-delete）

pt-archiver --source h=源ip,P=源端口,u=用户,p=密码,D=库名,t=表名 --dest h=目标IP,P=端口,u=用户,p=密码,D=库名,t=表名 --no-check-charset --where 'ID<100' --progress 5000 --limit=10000 –statistics

加上字符

pt-archiver --charset 'utf8' --source h=源ip,P=源端口,u=用户,p=密码,D=库名,t=表名 --dest h=目标IP,P=端口,u=用户,p=密码,D=库名,t=表名 --no-check-charset --where 'ID<100' --progress 5000 --limit=10000 –statistics

直接删除，不归档

pt-archiver --source h=127.0.0.1,P=端口,u=root,p=‘密码',D=源库,t=源表 --where 'ID<100' --purge --limit=5000 --no-check-charset --statistics --progress 5000

pt-kill

pt-kill 是一个优秀的kill MySQL连接的一个工具，是percona toolkit的一部分，这个工具可以kill掉你想Kill的任何语句，特别出现大量的阻塞，死锁，某个有问题的sql导致mysql负载很高的情况。

--daemonize 放在后台以守护进程的形式运行 
--interval 多久运行一次，单位可以是s,m,h，d等默认是s –不加这个默认是5秒 
--victims 默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，他们只是长期等待，这种匹配按时间查询，杀死一个时间最高值 
--all 杀掉所有满足的线程 
--kill-query 只杀掉连接执行的语句，但是线程不会被终止 
--print 打印满足条件的语句 
--busy-time 批次查询已运行的时间超过这个时间的线程 
--idle-time 杀掉sleep 空闲了多少时间的连接线程，必须在--match-command sleep时才有效—也就是匹配使用
----ignore-command 忽略相关的匹配
-- –match-command 匹配相关的语句 (这两个搭配使用一定是ignore-command在前，match-command在后)
--match-db cdelzone 匹配哪个库
match-command：Query，Sleep，Binlog Dump，Connect，Delayed insert，Execute，Fetch，Init DB，Kill，Prepare，Processlist，Quit，Reset stmt，Table Dump
match-State：Locked，login，copy to tmp table，Copying to tmp table，Copying to tmp table on disk，Creating tmp table，executing，Reading from net，Sending data，Sorting for order，Sorting result，Table lock，Updating

使用示例：

杀掉空闲连接sleep60秒的SQL

/usr/bin/pt-kill --host=127.0.0.1 --user=用户 --password=密码 --port=端口 --match-command Sleep --idle-time 60 --victim all --interval 5 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

查询SELECT超过1分钟的SQL

/usr/bin/pt-kill --host=127.0.0.1 --user=用户 --password=密码 --port=端口 --match-info "SELECT|select" --busy-time 60 --victim all --interval 5 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

kill掉state是Locked的SQL

/usr/bin/pt-kill --host=127.0.0.1 --user=用户 --password=密码 --port=端口 --victims all --match-state='Locked' --interval 5 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

指定用户进行kill

/usr/bin/pt-kill --host=127.0.0.1 --user=用户 --password=密码 --port=端口 --victims all --match-user='root' --interval 5 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

指定某个库和host地址进行kill

/usr/bin/pt-kill --host=127.0.0.1 --user=用户 --password=密码 --port=端口 --victims all --match-db='test' --match-host='10.0.0.51' --interval 10 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

将kill掉的SQL插入到表中

/usr/bin/pt-kill --host=127.0.0.1 --user=root --password=‘密码' --port=3306 --log-dsn D=库名,t=表名 --create-log-table --busy-time=60 --victims all --kill-query --match-info "SELECT|select" --print &

pt-online-schema-change

pt-online-schema-change简称pt-osc在线更改表结构，可以实现在线加减字段、索引、修改字段属性等功能。pt-osc模仿MySQL内部的改表方式进行改表，但整个改表过程是通过对原始表的拷贝来完成的，即在改表过程中原始表不会被锁定，并不影响对该表的读写操作。
首先，osc创建与原始表相同的不包含数据的新表（下划线开头）并按照需求进行表结构的修改，然后将原始表中的数据按逐步拷贝到新表中，当拷贝完成后，会自动同时修改原始表和新表的名字并默认将原始表删除。有两个注意点：被操作的表如果有触发器，或外键用不了。要特别注意（标准规范MySQL是不建议用外键与触发器的）如果有，要把外键与触发器去掉再操作。
工作原理：

创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)

在新表执行alter table 语句（速度应该很快）

在原表中创建触发器3个触发器分别对应insert,update,delete操作

以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表

Rename 原表到old表中，在把临时表Rename为原表

如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理

默认最后将旧原表删除，如果执行失败了，或手动停止了，需要手动删除下划线开头的表(_表名)及三个触发器

--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，不把信息标准输出。

使用示例：

添加索引

pt-online-schema-change --user=root --password='密码' --port=端口 --host=127.0.0.1 --critical-load Threads_running=100 --alter "ADD INDEX index_name (column_name)" D=库名,t=表名 --print --execute

添加列

pt-online-schema-change --user=root --password='密码' --port=端口 --host=127.0.0.1 --critical-load Threads_running=200 --alter "ADD COLUMN column_name int(10)" D=库名,t=表名 --print –execute

删除列

pt-online-schema-change --user=root --password='密码' --port=端口 --host=127.0.0.1 --critical-load Threads_running=200 --alter "drop column column_name" D=库名,t=表名 --print –execute

列与索引同时添加操作

pt-online-schema-change --user=root --password='密码' --port=6006 --host=127.0.0.1 --critical-load Threads_running=200 --alter "ADD COLUMN column_name int(10)" --alter "ADD INDEX index_name (column_name)" D=库名,t=表名 --print --execute

pt-query-digest

pt-query-digest，用来进行慢查询Log的分析

--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可以分析一段时间内的慢查询

使用示例：

指定时间段的慢查询

pt-query-digest /home/mysql/log/slow.log --since 'yyyy-mm-dd [hh:mm:ss]' --until 'yyyy-mm-dd [hh:mm:ss]'

把查询保存到query_review表

pt-query-digest --user=root –password=密码 --review h=localhost,D=test,t=query_review--create-review-table slow.log

分析指含有select语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /home/mysql/log/slow.log > slow_report.log

针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /home/mysql/log/slow.log > slow_report.log

pt-table-checksum & pt-table-sync

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=用户,p=密码,P=端口

检测DIFF有异常时，立刻到从库去看，通过在从库中查询replicate参数指定的test.checksum表得出：

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM test.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;

检测有差异之后到从库上执行一下修复，前提是此表必须要有主键或唯一索引：

pt-table-sync --sync-to-master --replicate=test.checksums h=127.0.0.1,u=用户,P=端口,p=密码 --print

pt-find

pt-find，找出几天之前建立的表，使用示例：

找出大于10G的表

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --tablesize +10G

30分钟之修改过的表

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --mmin -25

没有数据的表

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --empty

找出1天以前的表，存储引擎是MyISAM

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口--ctime +1 --engine MyISAM

找出存储引擎为MyISAM的表修改为InnoDB

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=InnoDB"

找出test1，test2库里的空表并删除

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --empty test1 test2 --exec-plus "DROP TABLE %s"

找到所有的表，根据数据和索引总大小，从大到小排序

/usr/bin/pt-find --socket=/tmp/mysqld.sock --user=root --password=密码 --port=端口 --printf "%T\t%D.%N\n" | sort -rn

pt-mysql-summary

pt-mysql-summary，打印出来MySQL的描述信息，包括：版本信息，数据目录，命令的统计，用户，数据库以及复制等信息还包括各个变量（status、variables）信息和各个变量的比例信息，还有配置文件等信息。

pt-mysql-summary --user=root --password=密码 --host=127.0.0.1 --port=端口

Pt-summary

Pt-summary，打印出来的信息包括：CPU、内存、硬盘、网卡等信息，还包括文件系统、磁盘调度和队列大小、LVM、RAID、网络链接信息、netstat 的统计，以及前10的负载占用信息和vmstat信息。

pt-align

pt-align常用于列格式化输出，功能单一，但是实用性极强。
示例文件如下：

DATABASE TABLE   ROWS
foo      bar      100
long_db_name table  1
another  long_name 500

经过pt-align处理

DATABASE     TABLE     ROWS
foo          bar        100
long_db_name table        1
another      long_name  500

pt-config-diff

pt-config-diff，比较MySQL配置文件的不同
参数说明：

h:数据库主机
P:端口号
u:用户名
p:密码

使用示例：

比较host1和host2配置文件不同

pt-config-diff h=host1 h=host2

比较[mysqld]标签和host1不同

pt-config-diff /etc/my.cnf h=host1

输出结果如下：

pt-config-diff /etc/my-small.cnf /etc/my-large.cnf
2 config differences
Variable                  my.master.cnf   my.slave.cnf
========================= =============== ===============
datadir                   /tmp/12345/data /tmp/12346/data
port                      12345           12346

pt-deadlock-logger

pt-deadlock-logger可以记录MySQL中的死锁信息到指定的地方，便于集中分析。
使用示例：
将host1主机产生的死锁信息保存在host2主机test库下面的deadlocks表中，–create-dest-table表示

pt-deadlock-logger h=host1,P=端口,u=用户,p=密码 --dest h=host2,P=端口,u=用户,p=密码,D=test,t=deadlocks --create-dest-table --log=/home/mysql/deadlock.log --daemonize --interval 5 --run-time 2m --iterations 4

pt-heartbeat

pt-heartbeat是监控mysql主从复制延迟的。
pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表，然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟

格式

pt-heartbeat [OPTIONS] [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
);
heratbeat   表一直在更改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,5m,15m。可以指定1个，如：--frames=1s，多个用逗号隔开。可用单位有秒（s）、分钟（m）、小时（h）、天（d）。
--interval   检查、更新的间隔时间。默认是见是1s。最小的单位是0.01s，最大精度为小数点后两位，因此0.015将调整至0.02。
--log    开启daemonized模式的所有日志将会被打印到制定的文件中。
--monitor    持续监控从的延迟情况。通过--interval指定的间隔时间，打印出从的延迟信息，通过--file则可以把这些信息打印到指定的文件。
--master-server-id    指定主的server_id，若没有指定则该工具会连到主上查找其server_id。
--print-master-server-id    在--monitor和--check 模式下，指定该参数则打印出主的server_id。
--recurse    多级复制的检查深度。模式M-S-S...不是最后的一个从都需要开启log_slave_updates，这样才能检查到。
--recursion-method     指定复制检查的方式,默认为processlist,hosts。
--update    更新主上的心跳表。
--replace     使用--replace代替--update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行。
--stop    停止运行该工具（--daemonize），在/tmp/目录下创建一个“pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize）。
--table   指定心跳表名，默认heartbeat。

李斌

此平台用作学习交流日常分享

Percona Toolkit 工具检查 Mysql

前言

参考文献

安装步骤

使用介绍

pt-archive

pt-kill

pt-online-schema-change

pt-query-digest

pt-table-checksum & pt-table-sync

pt-find

pt-mysql-summary

Pt-summary

pt-align

pt-config-diff

pt-deadlock-logger

pt-heartbeat