Percona Toolkit 神器全攻略（实用类）

Percona Toolkit 神器全攻略（实用类）

Percona Toolkit 神器全攻略系列共八篇，前文回顾：

前文回顾
Percona Toolkit 神器全攻略

全文约定：$为命令提示符、greatsql>为GreatSQL数据库提示符。在后续阅读中，依据此约定进行理解与操作

实用类

在Percona Toolkit中实用类共有以下工具

• pt-align：将其它工具输出内容与列对齐
• pt-archiver：将表中的行存档到另一个表或文件中
• pt-find：查找表并执行命令
• pt-fingerprint：将查询转成密文
• pt-kill：Kill掉符合条件的SQL
• pt-k8s-debug-collector：从 k8s/OpenShift 集群收集调试数据（日志、资源状态等）
• pt-secure-collect：收集、清理、打包和加密数据

pt-align

概要

通过读取行并将其分成单词的方式来执行列对齐。该工具首先计算每行包含的单词数量，并尝试确定是否有一个占主导地位的数字，将其假设为每行的单词数量。接下来，pt-align会排除所有不符合该数量的行，并将下一行视为第一个非标题行。根据每个单词是否看起来像数字，它会决定列的对齐方式。最后，工具会遍历数据集，确定每列的宽度，并将它们格式化打印出来。

这对于调整vmstat或iostat的输出非常有帮助，使其更易于阅读。

用法

将其它工具的输出与列对齐，如果未指定FILES（文件）则读取STDIN（输入）

• pt-align [FILES]

如果工具打印以下输出（没有对齐）

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


选项

该工具的命令行参数如下

最佳实践

对齐vmstat

当查看 vmstat 时，有时会遇到列对齐不整齐的情况。此时，可以使用 pt-align 工具来解决这个问题

# 未使用pt-align工具
$ vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 205472 181304 60 948960 0 0 0 1 1 1 1 2 98 0 0

# 使用pt-align工具
$ vmstat | pt-align
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 0 205472 181260 60 948992 0 0 0 1 1 1 1 2 98 0 0


对齐iostat

当查看 iostat 时，有时会遇到列对齐不整齐的情况。此时，可以使用 pt-align 工具来解决这个问题

# 未使用pt-align工具
$ iostat
avg-cpu: %user %nice %system %iowait %steal %idle
0.86 0.00 1.51 0.00 0.00 97.62

# 使用pt-align工具
$ iostat | pt-align
0.86 0.00 1.51 0.00 0.00 97.62
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 0.11 0.33 1.31 6746368 27049993
dm-0 0.09 0.30 1.23 6263958 25264653
dm-1 0.03 0.02 0.09 452072 1782864


pt-archiver

将MySQL/GreatSQL表中的行存档到另一个表或文件中

概要

pt-archiver 是一款在线归档工具，不会影响生产，但是用此命令操作的表必须要有主键，它可以实现如下功能：

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

用法

• pt-archiver [OPTIONS] --source DSN --where WHERE

将表从oltp实例归档到olap的实例中

$ pt-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server --file '/var/log/archive/%Y-%m-%d-%D.%t' --where "1=1" --limit 1000 --commit-each


从子表删除孤立行

$ pt-archiver --source h=host,D=db,t=child --purge --where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'


选项

• 至少指定 --dest 、 --file 或 --purge 之一
• 如果 COPY 为 yes， --dest 中的 DSN 值默认为 --source 中的值

部分参数选项存在互斥，不可同时存在，详见：

所有参数选项如下：

最佳实践

创建一张archiver_test表，并生成1000条数据

greatsql> CREATE TABLE archiver_test (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INT,
gender VARCHAR(10),
timestamp TIMESTAMP
);

greatsql> select count(*) from archiver_test;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.01 sec)


在使用过程中可能会需要依赖例如：Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.此时按提示安装相应依赖包即可

归档到同一实例上的不同表

注意！此操作会删除源表的数据，若不删除源表数据请加上使用--no-delete

$ pt-archiver --source h=localhost,P=3306,u=root,D=test_db,t=archiver_test --charset=utf8mb4 --ask-pass --dest h=localhost,P=3306,u=root,D=test_db,t=archiver_test2 --ask-pass --where "id

伪代码如下

pt-archiver --source h=源ip,P=源端口,u=用户,p=密码,D=库名,t=表名 --ask-pass --dest h=目标IP,P=端口,u=用户,p=密码,D=库名,t=表名 --ask-pass --where "id

需要目标表已创建且字段一致，否则报错："Table 'test_db.archiver_test2' doesn't exist " 字段不一致报错：”The following columns exist in --dest but not --source: name2“

检查下是否已经归档成功

greatsql> select count(*) from archiver_test2;
+----------+
| count(*) |
+----------+
| 99 |
+----------+
1 row in set (0.00 sec)


因为--dest会从--source继承相同的值，所以上面也可以改写成以下方式

$ pt-archiver --source h=localhost,P=3306,u=root,D=test_db,t=archiver_test --charset=utf8mb4 --ask-pass --dest t=archiver_test2 --where "id

也可用Socket来进行登录，如果使用单机多实例部署GreatSQL的时候采用这种方法要尤其注意选择对应Socket

$ pt-archiver --source u=root,D=test_db,t=archiver_test -S /data/MySQL/GreatSQL.sock --charset=utf8mb4 --dest t=archiver_test2 --where "id

也可以通过 my.cnf 配置文件读取用户名和密码，但是在 my.cnf 文件中需要配置好用户名和密码

$ vim /etc/my.cnf
[client]
user=your_user_name
pass=sectet

$ pt-archiver --source F=/etc/my.cnf,u=root,D=test_db,t=archiver_test --charset=utf8mb4 --dest t=archiver_test2 --where "id

归档时不写入Binlog

添加b=true指定归档操作不写入Binlog中

$ pt-archiver --source b=true,h=localhost,P=3306,u=root,D=test_db,t=archiver_test --charset=utf8mb4 --ask-pass --dest b=true,t=archiver_test3 --where "id

归档到文件

导出到外部文件，且不删除源端表的数据

$ pt-archiver --source h=localhost,D=test_db,t=archiver_test,u=root --where '1=1' --no-check-charset --no-delete --file="/data/bk/archiver_test.dat"


因文件没有utf8mb4编码，所以设置了no-check-charset不检查字符集

检查备份情况

$ tail -n 3 /data/bk/archiver_test.dat
997 Fukuda Akina 449 F 2022-02-15 05:54:27
998 Sara Nguyen 45 F 2018-07-08 13:35:45
999 Chan Ka Man 644 M 2012-02-08 18:18:14


pt-find

概要

此工具可以查找符合条件的表，并做一些相应的操作。默认操作是打印数据库和表名称

用法

• pt-find [OPTIONS] [DATABASES]

选项

最佳实践

查找大于1G的表

$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --tablesize +1G
`tpch`.`customer`
`tpch`.`lineitem`
`tpch`.`orders`
`tpch`.`part`
`tpch`.`partsupp`


查找修改过的表

# 30分钟之内
$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --mmin -30
`mysql`.`gtid_executed`
`test_db`.`archiver_test`

# 30分钟之前
$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --mmin +30
`aptest`.`sys_dept`
`aptest`.`sys_user`


此查找基于INFORMATION_SCHEMA.TABLES表中的Update_time列，如果information_schema_stats_expiry设置的更新时间过长，将导致Update_time列不会实时更新。因此，在这种情况下，将无法准确地检索在过去30分钟内发生修改的表。

查找无数据的表

$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --empty
`db2`.`t1`
`db2`.`t2`


查找表并修改存储引擎

# 查找1天内创建的MyISAM表
$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --ctime -1 --engine MyISAM
`test_db`.`myisam`

# 查找1天前的InnoDB表
$ pt-find --socket=/data/GreatSQL/mysql.sock --user=root --port=3306 --ctime +1 --engine InnoDB


InnoDB和MyISAM两个存储引擎名字必须按照标准输入，否则将无法进行正确的查找

查找空表并删除

# 避免不必要的删除错误，先查找哪些是空表，在删除
$ pt-find --socket=/data/GreatSQL/mysql.sock --empty test_db
`test_db`.`archiver_test3`

# 查找test_db库中空表并删除
$ pt-find --socket=/data/GreatSQL/mysql.sock --empty test_db --exec-plus "DROP TABLE %s"


验证是否删除成功

greatsql> SHOW TABLES IN test_db LIKE 'archiver_test3';
Empty set (0.00 sec)


所有表数据和索引总大小排序

$ pt-find --socket=/data/GreatSQL/mysql.sock --printf "%T\t%D.%N\n" | sort -rn
7992197120 `tpch`.`orders`
7817084928 `tpch`.`lineitem`
......

# 输出有些没对齐，可以使用pt-align工具对齐
$ pt-find --socket=/data/GreatSQL/mysql.sock --printf "%T\t%D.%N\n" | sort -rn | pt-align
7992197120 `tpch`.`orders`
7817084928 `tpch`.`lineitem`
......


pt-fingerprint

将查询转成密文

概要

此工具可以将SQL语句重新格式化为另一种抽象形式，既所有具体值都以?代替。可以适用于数据脱敏的场景。

用法

• pt-fingerprint [OPTIONS] [FILES]

选项

最佳实践

替换单个语句

$ pt-fingerprint --query "select a, b, c from users where id = 5 and greatsql = 666"
select a, b, c from users where id = ? and greatsql = ?

$ pt-fingerprint --query "INSERT INTO product(ID,NAME,PRICE) VALUES(1,'greatsql',666)"
insert into product(id,name,price) values(?+)


如果SQL语句中字段名或表名有数字，也会被替换

$ pt-fingerprint --query "select a1, b2, c3 from users4 where id = 500 and greatsql = 8032"
select a?, ?, c? from users? where id = ? and greatsql = ?


若不想替换字段名或表名可加上--match-embedded-numbers

$ pt-fingerprint --match-embedded-numbers --query "select a1, b2, c3 from users4 where id = 5 and greatsql = 666"
select a1, b2, c3 from users4 where id = ? and greatsql = ?


--match-md5-checksums参数使用也是同理，避免MD5值被替换

替换文件中语句

创建文件pt_fingerprint_test_sql.txt

$ vim pt_fingerprint_test_sql.txt
select a from users where greatsql = 666;
select b from users where greatsql = 777;
select c from users where
greatsql = 888;


替换文件中的所有SQL语句

$ pt-fingerprint pt_fingerprint_test_sql.txt
select a from users where greatsql = ?
select b from users where greatsql = ?
select c from users where greatsql = ?


不管文件内格式如何，pt-fingerprint工具都会规范化空格等

当然也可以用作替换慢日志（Slow.log）的SQL内容

pt-kill

Kill掉符合条件的SQL

概要

pt-kill可以Kill掉任何语句，特别出现大量的阻塞，死锁，或某个有问题的SQL导致MySQL/GreatSQL负载很高的情况。会默认过滤掉复制线程。

用法

• pt-kill [OPTIONS] [DSN]

选项

至少指定 --kill 、 --kill-query 、 --print 、 --execute-command 或 --stop 之一

部分参数选项存在互斥，不可同时存在，详见：

所有参数选项如下:

最佳实践

Kill查询指定时间的连接

每十秒钟记录一下用时超过三十秒的查询语句，并且将这些语句输出到/data/pt_slow.log文件中

$ pt-kill --user=root --ask-pass --match-info "select|SELECT" --match-command='Query' --busy-time 30 --victims all --interval 10 --daemonize --print --log=/data/pt_slow.log


• --match-command：匹配当前连接的命令，对应 SHOW PROCESSLIST 捕获的 Command 对应值（可选值：Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump）；
• --match-info：正则匹配正则运行的 SQL，区分大小写；
• --interval：多久运行一次。默认单位秒。默认值30秒

也可以加上--kill直接Kill掉符合条件的查询语句

$ pt-kill --user=root --ask-pass --match-info "select|SELECT" --match-command='Query' --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/data/pt_slow.log


--victims默认是oldest只Kill最先发起，存在时间最长的查询。all Kill掉所有满足的线程。all-but-oldest只保留最长的不Kill其它都Kill掉

Kill指定IP的会话

打印出指定IP的会话

$ pt-kill --user=root --ask-pass --match-db='test_db' --match-host "192.168.6.55" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/data/pt_ip.log


Kill指定IP的会话

$ pt-kill --user=root --ask-pass --match-db='test_db' --match-host "192.168.6.55" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/data/pt_ip.log


Kill指定用户的会话

Kill指定用户的会话

$ pt-kill --user=root --ask-pass --match-db='test_db' --match-user "greatsql" --victims all --interval 10 --daemonize --kill --log=/data/pt_user.log


Kill指定用户大于10秒的空闲链接

$ pt-kill --user=root --ask-pass --match-db='db_name' --match-user "greatsql" --victims all --interval 10 --daemonize --kill --match-command='Sleep' --idle-time 10 --log=/data/pt_user.log


pt-kill工具会挂在后台定时Kill符合条件的用户、语句。 若需要停止请使用kill -9 $(ps -ef| grep pt-kill |grep -v grep |awk '{print $2}')

pt-secure-collect

概要

pt-secure-collect用于收集、清理、打包和加密数据

用法

• pt-secure-collect [

默认情况下，pt-secure-collect 将收集以下输出：

• pt-stalk
• pt-summary
• pt-mysql-summary

采集命令

• pt-secure-collect collect

解密命令

• pt-secure-collect decrypt

加密命令

• pt-secure-collect encrypt

清理命令

• pt-secure-collect sanitize

选项

最佳实践

收集GreatSQL信息

以非加密方式收集GreatSQL信息并且不删除临时文件

$ pt-secure-collect collect --mysql-user=root --mysql-password="" --mysql-port=3306 --mysql-host=localhost --bin-dir=/usr/bin --temp-dir=/data/data_collection --no-encrypt --no-remove-temp-files

INFO[2024-03-11 17:05:02] Creating temporary directory: /data/data_collection
INFO[2024-03-11 17:05:02] Temp directory is "/data/data_collection"
INFO[2024-03-11 17:05:02] Creating output file "/data/data_collection/pt-stalk_2024-03-11_17_05_02.out"
INFO[2024-03-11 17:05:02] Running pt-stalk --no-stalk --iterations=2 --sleep=30 --host=localhost --dest=/data/data_collection --port=3306 --user=root --password=********
INFO[2024-03-11 17:06:35] Creating output file "/data/data_collection/pt-summary_2024-03-11_17_06_35.out"
INFO[2024-03-11 17:06:35] Running pt-summary
INFO[2024-03-11 17:06:36] Creating output file "/data/data_collection/pt-mysql-summary_2024-03-11_17_06_36.out"
INFO[2024-03-11 17:06:36] Running pt-mysql-summary --host=localhost --port=3306 --user=root --password=********
INFO[2024-03-11 17:06:49] Sanitizing output collected data
INFO[2024-03-11 17:06:57] Creating tar file "/data/data_collection/data_collection.tar.gz"


--mysql-port和--mysql-host虽有默认值但是还是需要指定，否则在调用运行其它工具时会报错

可以从输出上看到，pt-secure-collect工具调用了pt-stalk、pt-summary、pt-mysql-summary这三款工具

进入data_collection文件夹即可看到所有的临时文件，以及一个data_collection.tar.gz压缩文件

$ ls /data/data_collection
2024_03_11_17_05_03-df 2024_03_11_17_05_03-opentables2 2024_03_11_17_05_33-diskstats 2024_03_11_17_05_33-processlist
2024_03_11_17_05_03-disk-space 2024_03_11_17_05_03-output 2024_03_11_17_05_33-dmesg data_collection.tar.gz .......其余省略


加密文件

pt-secure-collect encrypt /data/pt_secure_collect.txt --outfile=/data/pt_secure_collect.aes
Encryption password: # 这里输入加密密码
Re type password: # 再次输入加密密码
INFO[2024-03-12 09:36:39] Encrypting file "/data/pt_secure_collect.txt" into "/data/pt_secure_collect.aes" # 加密成功


加密成功后此时会生成一个后缀为aes的文件，直接查看会乱码

$ ls
pt_secure_collect.aes pt_secure_collect.txt

$ cat pt_secure_collect.aes
5�66~x�y��+� ?i`��pESϡ>()�g�,�e�u #乱码


解密文件

$ pt-secure-collect decrypt /data/pt_secure_collect.aes
Encryption password: # 输入加密的密码
INFO[2024-03-12 09:41:35] Decrypting file "/data/pt_secure_collect.aes" into "pt_secure_collect"

$ cat pt_secure_collect
111
aaa
select * from test where id =2;


假设输入错误密码，不会提示密码错误，而是输出乱码结果

$ pt-secure-collect decrypt /data/pt_secure_collect.aes
Encryption password: # 这里假设输入错误密码
INFO[2024-03-12 09:44:45] Decrypting file "/data/pt_secure_collect.aes" into "pt_secure_collect"

$ cat pt_secure_collect
; �}X����#z1�e��s�/��E���OeB�6��,�� �#


加密文件

这个功能和上面介绍的pt-fingerprint工具有点类似，都是使用?替换关键信息

先造一个文本

$ cat pt_secure_collect.txt
select * from test where id =2;
ip = 192.168.6.66


使用sanitize功能，会隐去关键信息和主机名

$ pt-secure-collect sanitize --input-file=/data/pt_secure_collect.txt --no-sanitize-queries
select * from test where id =?;
ip = hostname


如果不隐去主机可以使用--no-sanitize-hostnames 如果不隐去查询可以使用--no-sanitize-queries

本文完 🙂 下章节将介绍Percona Toolkit 神器全攻略（配置类）