参考MySQL基础运维——percona-toolkit运维工具_ITPUB博客
一、percona-toolkit工具
percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的 mysql 任务和系统任务，这些任务包括：
-
检查 master 和 slave 数据的一致性
-
有效地对记录进行归档
-
查找重复的索引
-
对服务器信息进行汇总
-
分析来自日志和 tcpdump 的查询
-
当系统出问题的时候收集重要的系统信息
percona-toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 mysql 的最有名的工具，现在 Maatkit 工具已经不维护了，请大家还是使用 percona-toolkit 吧！ 这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类，作为一个优秀的 DBA，里面有的工具非常有用，如果能掌握并加以灵活应用，将能极大的提高工作效率。
1、percona-toolkit软件包下载
2、 percona-toolkit软件安装
软件安装存在三种安装方式，分别为RPM包、二进制包、源码安装。
需安装依赖包
| 1 |
yum install perl-DBI perl-DBD-MySQL
|
1.RPM包安装方式
| 1 |
rpm –ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm
|
2.二进制包安装
| 1 2 3 4 5 |
tar –xvf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install
|
下面介绍它自带的一些工具
2、pt-kill工具
1、工具介绍
pt-kill 是一个简单而且很实用的查杀mysql线程和查询的工具，主要是为了防止一些大/复杂/长时间查询占用数据库及系统资源，而对线上业务造成影响的情况。
2、常用参数
常用参数 |
含义 |
--user |
用户 |
--password |
密码 |
--port |
端口 |
--host |
主机 |
--socket |
本地套接字 |
--match-command |
匹配状态 |
--match-info |
匹配信息 |
--match-state |
匹配声明 |
--ignore-host/--match-host |
匹配主机 |
--ignore-db/--match-db |
匹配数据库 |
--ignore-user/--match-user |
匹配用户 |
--kill |
杀掉连接并且退出 |
--kill-query |
只杀掉连接执行的语句，但是线程不会被终止 |
|
|
打印满足条件的语句 |
--busy-time |
SQL运行时间的线程 |
--idle-time |
sleep时间的连接线程，必须在--match-command sleep时才有效 |
--interval |
query的间隔 |
--victim |
oldest|all|all-but-oldest 针对范围 |
--daemonize |
是否放到后台执行 |
--interval |
执行频率(s=seconds, m=minutes, h=hours, d=days) |
--log-dsn D=test,t=pk_log |
记录信息到表中 |
3、实际案例
以下均省略连接参数！
3.1 杀运行时间超过10s的SQL语句但保留线程
| 1 |
pt-kill --busy-time=10 --victims all --print --kill-query
|
3.2 查某IP来源且运行时间超过20s的会话
| 1 |
pt-kill --match-host "10.100.50.29" --print --victims all --busy-time=20s
|
3.3 每10秒检查一次，状态为sleep的进程就给干掉
| 1 |
pt-kill --match-command Sleep --victims all --interval 10 --kill --print
|
3.4 查杀select大于10s的会话
| 1 |
pt-kill --match-info "select|Select|SELECT" --print --victims all --busy-time 20s
|
3.5 查状态为sleep的进程且时间超过1h
| 1 |
pt-kill --match-command Sleep --victims all --idle-time=1h --print
|
3.6 查杀访问某用户的会话
| 1 |
pt-kill --match-user "test" --victims all --print --kill
|
3.7 将执行记录记录到表中
| 1 |
pt-kill --log-dsn D=test,t=pk_log --create-log-table --host=192.168.186.11 --user=root --password=mysql --port=3306 --busy-time=10 --print --kill-query
|
注：
指定库必须存在否则会报错！
test库中pk_log表，若不存在先创建表--create-log-table
3.8 杀掉正在进行filesort的sql
| 1 |
pt-kill --match-command Query --match-state “Sorting result” --busy-time 10 --interval 10 --run-time 1 --print --kill --victims all
|
3.9 后台执行杀掉正在进行filesort的sql
| 1 |
pt-kill --match-command Query --match-state “Sorting result” --busy-time 10 --interval 10 --run-time 1 --print --kill --victims all --daemonize
|
注：
--daemonize 会将该命令放到后台不断执行。
3.10 筛选文件中processlist
| 1 2 |
mysql -e "SHOW PROCESSLIST" > proclist.txt
pt-kill --test-matching proclist.txt --busy-time 60 --print
|
3、pt-query-digest工具
1、工具介绍
分析查询执行日志，并产生一个查询报告，为 MySQL、 PostgreSQL、memcached 过滤、重放或者转换语句。
2、常用参数
常用参数 |
含义 |
--create-review-table |
当使用--review参数把分析结果输出到表中时，如果没有表就自动创建 |
--create-history-table |
当使用--history参数把分析结果输出到表中时，如果没有表就自动创建 |
--filter |
对输入的慢查询按指定的字符串进行匹配过滤后再进行分析 |
--limit |
限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出 |
--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可以分析一段时间内的慢查询 |
3、应用案例
3.1 直接分析慢查询文件
| 1 |
pt-query-digest /var/lib/mysql/log/mysql-slow.log
|
3.2 分析最近12小时内慢查询
| 1 |
pt-query-digest --since=12h /var/lib/mysql/log/mysql-slow.log
|
3.3 分析指定时间范围内的慢查询
| 1 |
pt-query-digest /var/lib/mysql/log/mysql-slow.log --since '2019-11-27 09:30:00' --until '2019-11-27 10:00:00'
|
3.4 分析指含有select语句的慢查询
| 1 |
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/log/mysql-slow.log
|
3.5 针对某个用户的慢查询
| 1 |
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/log/mysql-slow.log
|
3.6 查询所有所有的全表扫描或full join的慢查询
| 1 |
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/log/mysql-slow.log
|
3.7 把查询保存到query_review表
| 1 |
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/log/mysql-slow.log
|
3.8 把查询保存到query_history表
| 1 2 |
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table /var/lib/mysql/log/mysql-slow.log_0001
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table /var/lib/mysql/log/mysql-slow.log_0002
|
3.9 通过tcpdump抓取mysql的tcp协议数据，然后再分析
| 1 2 |
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt
|
3.10 分析binlog
| 1 2 |
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
|
3.11 分析general log
| 1 |
pt-query-digest --type=genlog localhost.log
|
4、结果解析
总体统计结果
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
#该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
#310ms user time, 20ms system time, 25.21M rss, 207.20M vsz
#工具执行时间
#Current date: Wed Nov 27 10:58:44 2019
#运行分析工具的主机名
#Hostname: test
#被分析的文件名
#Files: /var/lib/mysql/log/mysql-slow.log
#语句总数量，唯一的语句数量，QPS，并发数
#Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
#日志记录的时间范围
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute total min max avg 95% stddev median
#============ ======= ======= ======= ======= ======= ======= =======
#语句执行时间
#Exec time 3s 3s 3s 3s 3s 0 3s
#锁占用时间
#Lock time 0 0 0 0 0 0 0
#发送到客户端的行数
#Rows sent 1 1 1 1 1 0 1
#select语句扫描行数
#Rows examine 0 0 0 0 0 0 0
#查询的字符数
#Query size 15 15 15 15 15 0 15
查询分组统计结果
#Profile
#Rank Query ID Response time Calls R/Call V/M
#==== ================================== ============= ===== ====== =====
#1 0x59A74D08D407B5EDF9A57DD5A41825CA 3.0004 100.0% 1 3.0004 0.00 SELECT
Rank 所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID 语句的ID，（去掉多余空格和文本字符，计算hash值）
Response 总的响应时间
time 该查询在本次分析中总的时间占比
calls 执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call 平均每次执行的响应时间
V/M 响应时间Variance-to-mean的比率
Item 查询对象
每一种查询的详细统计结果
#Query 1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 3414
#This item is included in the report because it matches --limit.
#Scores: V/M = 0.00
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute pct total min max avg 95% stddev median
#============ === ======= ======= ======= ======= ======= ======= =======
#Count 100 1
#Exec time 100 3s 3s 3s 3s 3s 0 3s
#Lock time 0 0 0 0 0 0 0 0
#Rows sent 100 1 1 1 1 1 0 1
#Rows examine 0 0 0 0 0 0 0 0
#Query size 100 15 15 15 15 15 0 15
#String:
#Hosts localhost
#Users root
#Query_time distribution
#1us
#10us
#100us
#1ms
#10ms
#100ms
#1s ################################################################
#10s+
#EXPLAIN /*!50100 PARTITIONS*/
select sleep(3)\G
ID 查询的ID号，和上图的Query ID对应
Databases 数据库名
Users 各个用户执行的次数（占比）
Query_time distribution
查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables 查询中涉及到的表
Explain SQL语句
|
4、pt-archiver工具
1、简单介绍
pt-archiver 是将MySQL数据库中的表数据归档到另外一个表或者文件，也可以直接进行记录的删除操作。
归档表必须存在主键。
2、pt-archiver使用场景
-
1、清理线上过期数据
-
2、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器
-
3、两张表之间的数据不完全相同，希望合并。此时加上–ignore或–replace选项，可以轻松实现
-
4、导出线上数据，到线下数据作处理
3、常用参数
常用参数 |
含义 |
| --source/--dest | 源端/目标端 |
| h/D/t/u/p | 主机IP/数据库名/表名/用户/密码 |
| --where | 操作条件 |
| --(no-)check-charset | 检查连接的字符集与表的字符集是否一致. |
| --limit X | 每次取X行数据用pt-archive处理 |
| --txn-size X | 设置X行为一个事务提交一次 |
| --progress X | 每处理X行输出一次处理信息 |
| --statistics | 输出执行过程及最后的操作统计 |
| --bulk-delete | 批量删除source上的旧数据 |
| --bulk-insert | 批量插入数据到dest主机(实际LOAD DATA插入) |
| --replace | 将insert into 语句改成replace写入到dest库 |
| --purge | 删除source数据库的相关匹配记录 |
| --file |
输出为本地文件%d Day of the month, numeric (01..31)%H Hour (00..23)%i Minutes, numeric (00..59)%m Month, numeric (01..12)%s Seconds (00..59)%Y Year, numeric, four digits%D Database name%t Table name |
--header |
本地文件头部加入列名 |
注：
1. 归档表必须存在主键
2. 需要配置client字符集为utf-8,如果你用了utf-8的编码,防止归档数据为乱码
[client]
default-character-set=utf8
4、应用案例
pt-archiver [OPTIONS] --source DSN --where WHERE
1.--dest, --file, --purge 必须指定其一
2.--ignore | --replace 只能选一
3.--txn-size | --commit-each 只能选一
4.--low-priority-insert | --delayed-insert 只能选一
5.--share-lock | --for-update 只能选一
6.--analyze | --optimize 只能选一
7.--no-ascend | --no-delete 只能选一
4.1 导出到外部文件，但是不删除源表里的数据
| 1 |
pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"
|
4.2 删除，不导出和迁移
| 1 |
pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456,A=UTF8 --charset=UTF8 --where "1=1" --limit 1000 --purge --commit-each --no-safe-auto-increment --progress=5 --statistics
|
4.3 全表归档到其他库
| 1 |
pt-archiver --source h=127.0.0.1,D=test,t=table1,u=root,p=123456 --dest h=127.0.0.1,D=test,t=table2,u=root,p=123456 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 2000 --statistics
|
注：
目标库需要开启load data local功能
SET GLOBAL local_infile = 1;
4.4 根据条件归档到其他库
| 1 2 3 4 |
pt-archiver \
--source h=72.0.0.111,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--dest h=192.168.210.126,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--charset=UTF8 --where " id < 1043835027 " --progress 10000 --limit=1000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge
|
参数解析 含义