pt 工具的安装与使用

Percona Toolkit（简称 pt）是 Percona 公司开发用于管理 MySQL 的工具，功能包括检查主从复制的数据一致性、同步表数据、数据清理及归档、在线DDL等，DBA 熟悉掌握后将极大提高工作效率。

准备工作

安装 mysql 环境

为了演示，下面准备3台 MySQL 5.7.44 实例：

ip地址	机器名	OS版本	MySQL版本	备注
192.168.31.101	mysql101	Ubuntu 24.04.3 LTS	MySQL 5.7.44	GTID复制的主库、安装pt工具
192.168.31.102	mysql102	Ubuntu 24.04.3 LTS	MySQL 5.7.44	GTID复制的从库
192.168.31.103	mysql103	Ubuntu 24.04.3 LTS	MySQL 5.7.44	单实例

#.更改机器名
newhost=`ifconfig -a|grep inet|grep 192.168.31|awk '{print $2}'|tail -1|awk -F'.' '{print "mysql"$4}'`
hostnamectl set-hostname $newhost
cat /etc/hosts | grep `cat /etc/hostname` || echo 127.0.0.1 `cat /etc/hostname` >> /etc/hosts

#.开始安装
cd /opt
wget -c http://iso.sqlfans.cn/mysql/percona-server-5.7.44-48.tar.gz
wget -c http://iso.sqlfans.cn/mysql/config/5.7/my_innodb.cnf
wget -c http://iso.sqlfans.cn/mysql/install_percona_5744.sh
bash install_percona_5744.sh /opt /data 3306

#.登录测试
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"select host,user,plugin,account_locked from mysql.user;"

搭建 gtid 复制

登录任意节点，快速搭建主从复制（一主一从、已启用 GTID）：

#.1.定义全局变量
work_dir=`pwd`
port="3306"
mysql_m="192.168.31.101"
mysql_s="192.168.31.102"

#.2.在主库上创建复制专用账户
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"CREATE USER IF NOT EXISTS 'dba_repl'@'%' IDENTIFIED with mysql_native_password BY 'MA6RuouuZZn4x_Hd';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"GRANT SELECT,RELOAD,FILE,SUPER,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"FLUSH PRIVILEGES;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select user,host,plugin,account_locked from mysql.user;"

#.3.备份mysql主库的所有数据库
mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=on --triggers --events --routines --skip-tz-utc -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port --all-databases > ${work_dir}/mysqldump.sql

#.4.由于从库启用gtid,在导入备份之前先reset
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port < ${work_dir}/mysqldump.sql
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e "show databases;"

#.5.在从库上启动主从
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"STOP SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"RESET SLAVE ALL;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"CHANGE MASTER TO MASTER_HOST='${mysql_m}',MASTER_PORT=${port},MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"START SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"SHOW SLAVE STATUS \G;" | egrep "(Running|Last_IO_Err)"

#.6.开启从库的超级只读模式(根据需要)
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global read_only = on;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global super_read_only = on;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select @@read_only, @@super_read_only;"

安装 pt 工具

登录任意节点，安装 Percona Toolkit 3.0.13，示例安装在 mysql101 节点

#.CentOS.安装依赖
# yum install -y perl-Time-HiRes perl-DBI perl-DBD-MySQL perl-Digest-MD5

#.Ubuntu.安装依赖
apt-get install -y libdbi-perl libdbd-mysql-perl

#.配置环境
cd /opt/
wget -c http://iso.sqlfans.cn/mysql/percona-toolkit.tar.gz
tar xvf percona-toolkit.tar.gz
mv /opt/percona-toolkit-3.0.13 /usr/local/pt
echo "PATH=/usr/local/pt/bin:\$PATH" >> /etc/profile
source /etc/profile

#.确认版本
pt-find --version

基于 percona-toolkit-3.0.13 版本，包含的 pt 工具如下：

分类	名称	功能	备注
开发类	pt-duplicate-key-checker	列出并删除重复的索引和外键	-
-	pt-online-schema-change	在线修改表结构，Online DDL	常用命令，大表 DDL 等同 gh-ost
-	pt-show-grants	规范化和打印权限	常用命令
-	pt-upgrade	在多个服务器上执行查询，并比较不同	-
性能类	pt-index-usage	分析日志中索引使用情况，并出报告	-
-	pt-pmp	为查询结果跟踪，并汇总跟踪结果	-
-	pt-visual-explain	格式化执行计划	-
-	pt-table-usage	分析日志中查询并分析表使用情况	-
配置类	pt-config-diff	比较配置文件和参数	常用命令
-	pt-mysql-summary	对mysql配置和status进行汇总	-
-	pt-variable-advisor	分析参数，并提出建议	-
监控类	pt-deadlock-logger	提取和记录mysql死锁信息	-
-	pt-fk-error-logger	提取和记录外键信息	-
-	pt-mext	并行查看status样本信息	-
-	pt-query-digest	分析查询日志，并产生报告	常用命令
复制类	pt-heartbeat	监控mysql复制延迟	-
-	pt-slave-delay	设定从落后主的时间	-
-	pt-slave-find	查找和打印所有mysql复制层级关系	-
-	pt-slave-restart	监控salve错误，并尝试重启salve	-
-	pt-table-checksum	校验主从复制一致性	-
-	pt-table-sync	高效同步表数据	-
系统类	pt-diskstats	查看系统磁盘状态	-
-	pt-fifo-split	模拟切割文件并输出	-
-	pt-summary	收集和显示系统概况	-
-	pt-sift	浏览由pt-stalk创建的文件	-
-	pt-stalk	出现问题时，收集诊断数据	-
-	pt-ioprofile	查询进程IO并打印一个IO活动表	-
-	pt-secure-collect	收集、清理、打包和加密数据	-
实用类	pt-archiver	数据归档或清理	常用命令，datax 离线异构数据同步工具
-	pt-find	查找表并执行命令	常用命令
-	pt-kill	自动查杀工具	常用命令
-	pt-align	对齐其他工具的输出	pt 2.2新增命令
-	pt-fingerprint	将查询转成密文	pt 2.2新增命令
对mongo的支持	pt-mongodb-query-digest	-	-
-	pt-mongodb-summary	-	-

开发类

pt-duplicate-key-checker

准备测试数据，示例表 it.tab001 存在冗余索引

mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"create database if not exists it;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"drop table if exists it.tab001;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"create table if not exists it.tab001(id1 int,id2 int,id3 int);"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"alter table it.tab001 add index idx_tab001_1 (id1,id2,id3);"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"alter table it.tab001 add index idx_tab001_2 (id1,id2);"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"alter table it.tab001 add index idx_tab001_3 (id1);"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"show indexes from it.tab001;"

利用 pt-duplicate-key-checker 检查冗余索引

#.查看帮助
pt-duplicate-key-checker --help

#.检查冗余索引
pt-duplicate-key-checker -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 --databases=it

pt-online-schema-change

pt-online-schema-change 实现不停机操作，基于四个关键步骤：创建影子表、数据同步、触发器管理、原子切换，详细过程如下：

1.创建影子表：创建一个与原表结构相同的新表（称为影子表，比如 _table_new）
2.修改结构：在空表上执行 ALTER 语句，速度极快
3.创建触发器：在原表上创建 INSERT、UPDATE、DELETE 触发器。当数据复制期间原表有数据变动时，触发器会自动同步到影子表
4.拷贝数据：将原表数据以小块（Chunk）的形式分批拷贝到影子表（默认1000行），避免长时间锁表
5.切换表并清理：通过原子性的 RENAME 操作交换原表与影子表，随后删除旧表及触发器

准备测试数据，注意 pt-online-schema-change 操作的表必须要有主键或唯一索引

mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"create database if not exists it;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"drop table if exists it.tab002;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"create table if not exists it.tab002(id int, id2 int, PRIMARY KEY pk_id(id));"

利用 pt-online-schema-change 进行在线 DDL

#.查看帮助
pt-online-schema-change --help

#.使用 --dry-run 仅做验证（不执行）
pt-online-schema-change --user=dba_admin --password=cf_rB1NKCzbaQuPH --host=192.168.31.101 P=3306,D=it,t=tab002 \
 --charset=utf8 --alter "ADD COLUMN id3 int DEFAULT 0" --dry-run

#.使用 --execute 执行变更
pt-online-schema-change --user=dba_admin --password=cf_rB1NKCzbaQuPH --host=192.168.31.101 P=3306,D=it,t=tab002 \
 --charset=utf8 --alter "ADD COLUMN id3 int DEFAULT 0" --execute

#.针对上亿的大表, 使用 --max-load 设定最大负载、--chunk-size 设定每次复制的数据量
pt-online-schema-change --user=dba_admin --password=cf_rB1NKCzbaQuPH --host=192.168.31.101 P=3306,D=it,t=tab002 \
 --charset=utf8 --alter "ADD COLUMN id4 int DEFAULT 0" \
 --chunk-size=1000 --max-load Threads_running=20 --execute

#.验证执行结果
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"desc it.tab002;"

pt-show-grants

pt-show-grants 的功能是格式化打印输出 MySQL 上的赋权，可提取、排序和打印 MySQL 相关账户，以便有效地复制、比较以及版本控制。

#.查看帮助
pt-show-grants --help

#.场景1.输出某实例的所有账号的权限
pt-show-grants -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306

#.场景2.输出某实例的指定账号的权限
pt-show-grants -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 --only=dba_admin,dba_repl

#.场景3.输出某实例的指定账号的权限（开头添加 drop user，结尾添加 flush privileges）
pt-show-grants -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 --only=dba_admin,dba_repl --drop --flush

#.场景4.输出某实例的指定账号的权限（开头添加 revoke，分行单独列出 grant或revoke）
pt-show-grants -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 --only=dba_admin,dba_repl --revoke --separate

配置类

pt-config-diff

若两个实例配置差不多，性能就是不一样，无奈只能去比对数据库配置是否相同。
pt-config-diff 的功能是diff不同的 MySQL 配置文件以及不同的服务器变量设置。注意，该工具只能对CONFIG中都出现的变量才进行对比

#.定义全局变量
work_dir=`pwd`
mysql1="192.168.31.101"
mysql2="192.168.31.102"
port1="3306"
port2="3306"

#.查看帮助
pt-config-diff --help

#.场景1.检查主库和从库的参数配置(show variables)的一致性
pt-config-diff h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH h=$mysql2,P=$port2,u=dba_admin,p=cf_rB1NKCzbaQuPH --report-width=160

#.场景1.因用户名和密码相同, 也可以使用
pt-config-diff -udba_admin -pcf_rB1NKCzbaQuPH h=$mysql1,P=$port1 h=$mysql2,P=$port2 --report-width=160

#.场景2.检查本地文件(my.cnf)与单个远程实例参数(show variables)的一致性
cd /data/mysql_3306/
pt-config-diff my_3306.cnf h=$mysql2,P=$port2,u=dba_admin,p=cf_rB1NKCzbaQuPH --report-width=160

#.场景3.检查本地文件(my.cnf)与多个远程实例参数(show variables)的一致性
cd /data/mysql_3306/
pt-config-diff my_3306.cnf h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH h=$mysql2,P=$port2,u=dba_admin,p=cf_rB1NKCzbaQuPH --report-width=160

监控类

pt-query-digest

利用 pt-query-digest 分析慢查询日志，并产生报告

#.查看慢查询是否启用及保存路径
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"show variables where variable_name in ('slow_query_log','long_query_time','log_output','slow_query_log_file');"

#.查看帮助
pt-query-digest --help

#.分析查询日志
cd /data/mysql_3306/slow/
pt-query-digest mysql_slow.log

#.将分析结果保存到文件, 便于查看和分享
pt-query-digest mysql_slow.log > /tmp/pt_slow_report.log
more /tmp/pt_slow_report.log

#.分析过去24小时内的慢查询
pt-query-digest --since=24h mysql_slow.log

#.分析特定库的慢查询
pt-query-digest --filter='$event->{db} eq "it"' mysql_slow.log

复制类

pt-heartbeat

众所周知，用 Seconds_behind_master 衡量主从延迟存在局限性，而使用 pt-heartbeat 可以准确地查看主从延迟。

#.1.定义全局变量
work_dir=`pwd`
port="3306"
mysql_m="192.168.31.101"
mysql_s="192.168.31.102"

#.查看帮助
pt-heartbeat --help

#.2.主库.创建监控数据库,从库会同步创建
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"CREATE DATABASE IF NOT EXISTS monitor;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"SHOW DATABASES;"

#.3.主库.使用 --database 表示受监控的数据库, 成功后会在主库中创建一个带有时间戳的heartbeat表
pt-heartbeat --database=monitor --update -h $mysql_m -P $port -udba_admin -pcf_rB1NKCzbaQuPH --create-table --daemonize
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"SHOW TABLES FROM monitor LIKE 'heartbeat';"

#.4.从库.使用 --check 执行1次延迟监控
pt-heartbeat --database=monitor -h $mysql_s -P $port -udba_admin -pcf_rB1NKCzbaQuPH --check

#.5.从库.使用 --monitor 执行持续监控, 使用 --interval 指定间隔时间为2秒
pt-heartbeat --database=monitor -h $mysql_s -P $port -udba_admin -pcf_rB1NKCzbaQuPH --monitor --interval 2

pt-table-checksum

登录管理节点（示例 192.168.31.103），创建 dsns 表（示例 dbcenter.dsns_192_168 存储从库的 ip,dbport）

cat > /tmp/dbcenter_dsns.sql <<EOF
#.创建dsns表
CREATE DATABASE IF NOT EXISTS dbcenter;
DROP TABLE IF EXISTS dbcenter.dsns_192_168;
CREATE TABLE IF NOT EXISTS dbcenter.dsns_192_168 (
  id int(11) NOT NULL AUTO_INCREMENT, 
  parent_id int(11) DEFAULT NULL, 
  dsn varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

#.插入从库信息,若有多个从库建议全部填写
INSERT INTO dbcenter.dsns_192_168(parent_id,dsn) values(1,'h=192.168.31.102,u=dba_admin,p=cf_rB1NKCzbaQuPH,P=3306');
#INSERT INTO dbcenter.dsns_192_168(parent_id,dsn) values(2,'h=192.168.32.102,u=dba_admin,p=cf_rB1NKCzbaQuPH,P=3306');
EOF

利用 pt-table-checksum 分块校验，实现主从复制的数据一致性校验。返回信息中 DIFFS 表示数据不一致的行数。

#.定义全局变量
work_dir=`pwd`
port="3306"
mysql_m="192.168.31.101"
mysql_s="192.168.31.102"
mysql_admin="192.168.31.103"
port_admin="3306"

#.管理节点：创建dsns表
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_admin -P $port_admin -e"source /tmp/dbcenter_dsns.sql;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_admin -P $port_admin -e"select * from dbcenter.dsns_192_168;"

#.查看帮助
pt-table-checksum --help

#.简单测试：使用 --recursion-method=dsn 基于 dsns 表发现从库
pt-table-checksum --ask-pass h=$mysql_m,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH --no-check-binlog-format \
 --recursion-method=dsn=h=$mysql_admin,P=$port_admin,D=dbcenter,t=dsns_192_168

#.简单测试：使用 -h 指定主库ip、--check-slave-lag 指定从库ip、--ignore-databases 忽略特定库
pt-table-checksum --no-check-binlog-format \
 -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH \
 --ignore-databases=mysql,sys

#.生产场景1：针对【特定表】的一致性检查（若只有一个从库, 使用 --check-slave-lag 即可）
pt-table-checksum --max-load="Threads_running=20" --max-lag=300 \
 --nocheck-replication-filters --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it --tables=tab001,tab002 -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH

#.生产场景2：针对【特定表】的一致性检查（若有多个从库, 使用 --recursion-method=dsn 同时监控其他从库）
pt-table-checksum --max-load="Threads_running=20" --max-lag=300 \
 --nocheck-replication-filters --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it --tables=tab001,tab002 -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH \
 --recursion-method=dsn=h=$mysql_admin,P=$port_admin,D=dbcenter,t=dsns_192_168

#.生产场景3：针对【特定库】的一致性检查（将检验结果保存到表 it.checksums）
pt-table-checksum --max-load="Threads_running=20" --max-lag=300 \
 --nocheck-replication-filters --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it,monitor -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH \
 --recursion-method=dsn=h=$mysql_admin,P=$port_admin,D=dbcenter,t=dsns_192_168

#.生产场景4：针对【特定库】的一致性检查（将执行过程日志保存到文件）
pt-table-checksum --max-load="Threads_running=20" --max-lag=300 \
 --nocheck-replication-filters --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it,monitor -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH \
 --recursion-method=dsn=h=$mysql_admin,P=$port_admin,D=dbcenter,t=dsns_192_168 > /tmp/pt_checksum.log 2>&1


#.查看任务结果, 注意主库上的表 it.checksums 永远只保留最近一次任务的结果
root@mysql101:~# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select * from it.checksums;"
+---------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db      | tbl        | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+---------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| it      | tab001     |     1 |   0.017519 | NULL        | NULL           | NULL           | 5be5d4e4 |      100 | 5be5d4e4   |        100 | 2026-06-04 17:44:48 |
| it      | tab002     |     1 |   0.022797 | NULL        | NULL           | NULL           | 0        |        0 | 0          |          0 | 2026-06-04 17:44:48 |
| monitor | heartbeat  |     1 |   0.021049 | NULL        | NULL           | NULL           | 8afe5418 |        1 | 8afe5418   |          1 | 2026-06-04 17:44:49 |
+---------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

root@mysql101:~# cat /tmp/pt_checksum.log
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-04T17:44:48      0      0      100          0       1       0   0.365 it.tab001
06-04T17:44:48      0      0        0          0       1       0   0.394 it.tab002
06-04T17:44:49      0      0        1          0       1       0   0.388 monitor.heartbeat

pt-table-sync

准备测试脚本：

cat > /tmp/bluck_insert.sql <<EOF
#.创建数据库
CREATE DATABASE IF NOT EXISTS it;

#.创建存储过程，批量插入1000条数据
USE it;
DROP TABLE IF EXISTS tab001;
DROP PROCEDURE IF EXISTS bluck_insert;

DELIMITER $$
CREATE PROCEDURE bluck_insert()
BEGIN
  DECLARE i INT DEFAULT 1;
  CREATE TABLE IF NOT EXISTS tab001(id int, name varchar(10), created datetime,PRIMARY KEY pk_id(id));
  WHILE i <= 100 DO
    INSERT INTO tab001 (id, name, created) VALUES (i, CONCAT('user_', i), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

#.执行存储过程
CALL bluck_insert();
EOF

模拟数据不一致的场景：

#.定义全局变量
work_dir=`pwd`
mysql_m="192.168.31.101"
mysql_s="192.168.31.102"
port="3306"

#.主库：测试表 it.tab001 准备100条数据
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"source /tmp/bluck_insert.sql;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select count(*) from it.tab001;"

#.从库：先取消只读模式
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global read_only = off;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"set global super_read_only = off;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select @@read_only, @@super_read_only;"

#.从库：再修改部分数据
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"update it.tab001 set name='xxx' where id=1;"

#.执行一致性检查, 此时 tab001 的 DIFFS 为 1, 表示有一行数据不一致
root@mysql101:~# pt-table-checksum --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it --tables=tab001 -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-04T18:54:19      0      1      100          0       1       0   0.186 it.tab001

#.查看数据
root@mysql101:~# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select * from it.tab001 where id=1;"
+----+--------+---------------------+
| id | name   | created             |
+----+--------+---------------------+
|  1 | xxx    | 2026-06-04 18:37:49 |
+----+--------+---------------------+

使用 pt-table-sync 对 pt-table-checksum 检查校验出主从不一致的表数据进行修复

#.定义全局变量
work_dir=`pwd`
mysql_m="192.168.31.101"
mysql_s="192.168.31.102"
port="3306"

#.查看帮助
pt-table-sync --help

#.使用 --dry-run 查看执行信息, 注意这里 -h 指向数据不一致的特定从库ip
pt-table-sync h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH --charset=utf8 --databases=it --tables=tab001 --sync-to-master --transaction --dry-run

#.使用 --execute 执行数据修复
pt-table-sync h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH --charset=utf8 --databases=it --tables=tab001 --sync-to-master --transaction --execute

#.执行一致性检查, 此时 tab001 的 DIFFS 为 0, 表示数据已同步
root@mysql101:~# pt-table-checksum --no-check-binlog-format --replicate=it.checksums --create-replicate-table \
 --databases=it --tables=tab001 -h $mysql_m -P $port -u dba_admin -p cf_rB1NKCzbaQuPH \
 --check-slave-lag h=$mysql_s,P=$port,u=dba_admin,p=cf_rB1NKCzbaQuPH
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-04T18:55:58      0      0      100          0       1       0   0.333 it.tab001

#.确认数据
root@mysql101:~# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_s -P $port -e"select * from it.tab001 where id=1;"
+----+--------+---------------------+
| id | name   | created             |
+----+--------+---------------------+
|  1 | user_1 | 2026-06-04 18:37:49 |
+----+--------+---------------------+

实用类

pt-archiver

准备测试数据，注意 pt-archiver 操作的表必须要有主键

cat > /tmp/bluck_insert.sql <<EOF
#.创建数据库
CREATE DATABASE IF NOT EXISTS it;

#.创建存储过程，批量插入1000条数据
USE it;
DROP TABLE IF EXISTS tab001;
DROP PROCEDURE IF EXISTS bluck_insert;

DELIMITER $$
CREATE PROCEDURE bluck_insert()
BEGIN
  DECLARE i INT DEFAULT 1;
  CREATE TABLE IF NOT EXISTS tab001(id int, name varchar(10), created datetime,PRIMARY KEY pk_id(id));
  WHILE i <= 100 DO
    INSERT INTO tab001 (id, name, created) VALUES (i, CONCAT('user_', i), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

#.执行存储过程
CALL bluck_insert();
EOF

执行 pt-archiver 对数据进行归档，归档数据建议指定字符集，若只是删数据则不用指定字符集

注：pt-archiver 不会对源表及目标表执行 analyze 或 optimize 操作，建议在业务低谷执行 analyze table 以回收表空间。

#.定义全局变量
work_dir=`pwd`
mysql1="192.168.31.101"
mysql2="192.168.31.103"
port1="3306"
port2="3306"

#.源实例：测试表 it.tab001 准备100条数据
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"source /tmp/bluck_insert.sql;"

#.目标实例：创建归档表 it.tab001_bak（建议去掉主键）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"CREATE DATABASE IF NOT EXISTS it;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"DROP TABLE IF EXISTS it.tab001_bak;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"CREATE TABLE IF NOT EXISTS it.tab001_bak(id int, name varchar(10), created datetime);"

#.查看帮助
pt-archiver --help


#.场景1：将表中 id <= 5 的数据进行归档,★不删除原表记录(指定字符集)
#.参数 --progress 每n条输出一次进度、--limit 每次最多查询n行、--txn-size 每n行提交一次事务
pt-archiver --source h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001 \
 --dest h=$mysql2,P=$port2,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001_bak --charset=utf8mb4 --where 'id<=5' \
 --progress 5000 --limit=10000 --txn-size=1000 --statistics --no-delete

#.场景1：确认源表100条（ID从1到100）、目标表5条（ID从1到5），然后清空目标表
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"select * from it.tab001;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"select * from it.tab001_bak;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"truncate table it.tab001_bak;"


#.场景2：将表中 id <= 5 的数据进行归档,★删除原表记录(指定字符集)
pt-archiver --source h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001 \
 --dest h=$mysql2,P=$port2,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001_bak --charset=utf8mb4 --where 'id<=5' \
 --progress 5000 --limit=3000 --txn-size=1000 --statistics --bulk-delete

#.场景2：确认源表95条（ID从6到100）、目标表5条（ID从1到5）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"select * from it.tab001;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql2 -P $port2 -e"select * from it.tab001_bak;"


#.场景3：删除老数据purge(单纯的删数据，不用指定字符集)
pt-archiver --source h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001 --no-check-charset --where 'id<=10' --progress 5000 --limit=3000 --txn-size=1000 --statistics --purge

#.场景3：确认源表90条（ID从11到100）即：实际删除5条（ID从6到10）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"select * from it.tab001;"


#.场景4：仅导出数据到文件，★不删除原表记录(pt工具对utf8mb4支持不好,建议改为utf8)
rm -f /tmp/it-tab001-1.dat
pt-archiver --source h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001 --no-check-charset --charset=utf8 --where 'id<=20' --progress 5000 --limit=3000 --txn-size=1000 --statistics --no-delete --file "/tmp/it-tab001-1.dat"

#.场景4：确认源表90条（ID从11到100）、导出文件10条（ID从11到20）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"select * from it.tab001;"
cat /tmp/it-tab001-1.dat


#.场景5：导出数据到文件，★并删除原表记录(不指定字符集)
rm -f /tmp/it-tab001-2.dat
pt-archiver --source h=$mysql1,P=$port1,u=dba_admin,p=cf_rB1NKCzbaQuPH,D=it,t=tab001 --no-check-charset --where 'id<=20' --progress 5000 --limit=3000 --txn-size=1000 --statistics --file "/tmp/it-tab001-2.dat" --purge

#.场景5：确认源表80条（ID从21到100）、导出文件10条（ID从11到20）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql1 -P $port1 -e"select * from it.tab001;"
cat /tmp/it-tab001-2.dat

pt-kill

准备测试会话：

#.登录 192.168.31.102 服务器、使用 dba_repl 登录 192.168.31.103 实例、在 it 库、模拟一个30分钟的查询
mysql --host=192.168.31.103 --port=3306 --user=dba_repl --password=MA6RuouuZZn4x_Hd it -e"select sleep(1800);"

使用 pt-kill 杀掉符合指定条件的会话，参数 --print 打印会话、--kill 杀掉会话

#.定义全局变量
conn="--host=192.168.31.103 --port=3306 --user=dba_admin --password=cf_rB1NKCzbaQuPH"

#.查看帮助
pt-kill --help

#.简单匹配：每2秒检查1次、账号=dba_repl
pt-kill $conn --match-user "dba_repl" --victims all --interval 2 --print

#.匹配会话：command为Sleep、--idle-time空闲超过30s 的空闲会话
pt-kill $conn --match-command="Sleep" --idle-time 30 --victims all --interval 2 --print

#.先查再杀：库名、账号、command为Query、--busy-time执行超过10s
pt-kill $conn --match-db='it' --match-user "dba_repl" --match-command="Query" --busy-time 10 --victims all --interval 2 --print
pt-kill $conn --match-db='it' --match-user "dba_repl" --match-command="Query" --busy-time 10 --victims all --interval 2 --kill

#.先查再杀：匹配 SELECT 语句、--busy-time执行超过10s
pt-kill $conn --match-db='it' --match-info "select|SELECT" --busy-time 10 --victims all --interval 2 --print
pt-kill $conn --match-db='it' --match-info "select|SELECT" --busy-time 10 --victims all --interval 2 --kill

#.先查再杀：匹配 IP 来源、--busy-time执行超过10s
pt-kill $conn --match-db='it' --match-host "192.168.31.102" --busy-time 10 --victims all --interval 2 --print
pt-kill $conn --match-db='it' --match-host "192.168.31.102" --busy-time 10 --victims all --interval 2 --kill

#.先查再杀：正在进行Sorting的会话
pt-kill $conn --match-db='it' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 10 --victims all --interval 2 --print
pt-kill $conn --match-db='it' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 10 --victims all --interval 2 --kill

使用 --daemonize 或 --log-dsn 放到后台持续监控和杀掉会话：

#.定义全局变量
conn="--host=192.168.31.103 --port=3306 --user=dba_admin --password=cf_rB1NKCzbaQuPH"

#.使用 --print --daemonize 放到后台持续监控, 将会话信息写入日志file
pt-kill $conn --match-db='it' --match-user "dba_repl" --match-command="Query" --victims all --interval 2 --print --daemonize --log=/tmp/pt_print.log
#.查看请执行 tail -f /tmp/pt_print.log
#.停掉请执行 ps -ef | grep -v grep | grep pt-kill | awk '{print $2}' | xargs kill -9 >/dev/null 2>&1

#.使用 --kill --daemonize 放到后台持续杀掉会话
pt-kill $conn --match-db='it' --match-user "dba_repl" --match-command="Query" --busy-time 10 --victims all --interval 2 --kill --daemonize

#.使用 --kill --log-dsn 放到后台持续杀掉会话, 并将会话信息写入日志table, 示例表 it.kill_log
pt-kill $conn --match-db='it' --match-user "dba_repl" --victims all --interval 2 --kill --log-dsn D=it,t=kill_log --create-log-table
#.查看请执行 mysql $conn -e"select * from it.kill_log;"
#.停掉请执行 ps -ef | grep -v grep | grep pt-kill | awk '{print $2}' | xargs kill -9 >/dev/null 2>&1

pt工具的安装与使用

pt 工具的安装与使用

准备工作

安装 mysql 环境

搭建 gtid 复制

安装 pt 工具

开发类

pt-duplicate-key-checker

pt-online-schema-change

pt-show-grants

配置类

pt-config-diff

监控类

pt-query-digest

复制类

pt-heartbeat

pt-table-checksum

pt-table-sync

实用类

pt-archiver

pt-kill

results matching ""

No results matching ""