表复制工具pt-archiver基本使用
1 percona-toolkit安装
1.1 下载地址：
源码下载地址：
https://www.percona.com/downloads/percona-toolkit/3.0.9/source/tarball/percona-toolkit-3.0.9.tar.gz
1.2 安装依赖包
yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes perl-devel -y
1.3 安装percona-toolkit工具包
[root@node1 software]# tar -zxvf percona-toolkit-3.0.9_x86_64.tar.gz
[root@node1 software]# cd percona-toolkit-3.0.9/
[root@node1 percona-toolkit-3.0.9]# perl Makefile.PL
[root@node1 percona-toolkit-3.0.9]# make && make install
1.4 验证是否安装成功
[root@node1 ~]# pt-summary --version
pt-summary 3.0.9
1.5 工具命令及作用
工具类别 工具命令 工具作用 备注
开发类 pt-duplicate-key-checker 列出并删除重复的索引和外键 -
pt-online-schema-change 在线修改表结构 -
pt-query-advisor 分析查询语句，并给出建议，有bug 已废弃
pt-show-grants 规范化和打印权限 -
pt-upgrade 在多个服务器上执行查询，并比较不同 -
性能类 pt-index-usage 分析日志中索引使用情况，并出报告 -
pt-pmp 为查询结果跟踪，并汇总跟踪结果 -
pt-visual-explain 格式化执行计划 -
pt-table-usage 分析日志中查询并分析表使用情况 pt 2.2新增命令
配置类 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-trend 按照时间段读取slow日志信息 已废弃
复制类 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-stalk 出现问题时，收集诊断数据 -
pt-sift 浏览由pt-stalk创建的文件 pt 2.2新增命令
pt-ioprofile 查询进程IO并打印一个IO活动表 pt 2.2新增命令
实用类 pt-archiver 将表数据归档到另一个表或文件中 -
pt-find 查找表并执行命令 -
pt-kill Kill掉符合条件的sql 常用命令
pt-align 对齐其他工具的输出 pt 2.2新增命令
pt-fingerprint 将查询转成密文 pt 2.2新增命令
2.工具用法说明及举例
2.1 pt-align
pt-align - Align output from other tools to columns.(按行对齐其他工具的输出)
Usage：
pt-align [FILES]
[root@node1 ~]# cat a.txt
id name age
1 ‘aaaa’ 11
2 ‘bbbbb’ 23
3 ‘cccccc’ 5
[root@node1 ~]# pt-align a.txt
id name age
1 ‘aaaa’ 11
2 ‘bbbbb’ 23
3 ‘cccccc’ 5
2.2 pt-archiver
（1）pt-archiver - Archive rows from a MySQL table into another table or a file.
（将MySQL表中的行存档到另一个表或文件中，可以理解为归档）
Usage：
pt-archiver [OPTIONS] --source DSN --where WHERE
eg：
(2) 基础环境准备
源数据库：
set global innodb_flush_log_at_trx_commit=0;
create database db1 charset=utf8;
use db1;
create table t(id int primary key, a int, b int, index(a)) engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000) do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
目标数据库：
create database db1 charset=utf8;
use db1;
create table t_bak(id int primary key, a int, b int, index(a))engine=innodb;
**(3)参数详解** ```html --limit 10000 每次取1000行数据用pt-archive处理，Number of rows to fetch and archive per statement. --txn-size 1000 设置1000行为一个事务提交一次，Number of rows pertransaction. --where‘id<3000‘ 设置操作条件 --progress5000 每处理5000行输出一次处理信息 --statistics 输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt-archive都会输出执行过程的） --charset=UTF8 指定字符集为UTF8 --bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作) --bulk-insert 批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的) --replace 将insert into 语句改成replace写入到dest库 --sleep120 每次归档了limit个行记录后的休眠120秒（单位为秒） --file‘/root/test.txt‘ --purge 删除source数据库的相关匹配记录 --header 输入列名称到首行（和--file一起使用） --no-check-charset 不指定字符集 --check-columns 检验dest和source的表结构是否一致，不一致自动拒绝执行（不加这个参数也行。默认就是执行检查的） --no-check-columns 不检验dest和source的表结构是否一致，不一致也执行（会导致dest上的无法与source匹配的列值被置为null或者0） --chekc-interval 默认1s检查一次 --local 不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大） --retries 超时或者出现死锁的话，pt-archiver进行重试的间隔（默认1s） --no-version-check 目前为止，发现部分pt工具对阿里云RDS操作必须加这个参数 --analyze=ds 操作结束后，优化表空间（d表示dest，s表示source） 默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后，在业务低谷手动执行analyze table用以回收表空间。复制
（3）常用操作
1）删除老数据(单独的删数据操作不用指定字符集)
192.168.100.33：
pt-archiver --source h=localhost,u=pt_user,p=pt_pass,P=3306,D=db1,t=t --progress 5000 --where ‘id<=10000’ --no-check-charset --where ‘id<=10000’ --limit=10000 --txn-size 1000 --purge
[root@node1 ~]# pt-archiver --source h=localhost,u=pt_user,p=pt_pass,P=3306,D=db1,t=t --progress 5000 --where ‘id<=10000’ --no-check-charset --where ‘id<=10000’ --limit=10000 --txn-size 1000 --purge
TIME ELAPSED COUNT
2024-07-01T01:51:41 0 0
2024-07-01T01:51:42 1 5000
2024-07-01T01:51:43 2 10000
2024-07-01T01:51:43 2 10000
2）复制数据到其他mysql实例，且不删除source的数据(指定字符集)：
192.168.100.33：
[root@node1 ~]# mysql -uroot -p123456
root@db 23:37: [test]> create user ‘pt_user’@’%’ identified with mysql_native_password by ‘pt_pass’;
GRANT all privileges ON . TO ‘pt_user’@’%’;
flush privileges;
验证远程登陆是否成功:
[root@node1 ~]# mysql -upt_user -ppt_pass -h192.168.100.55 -P3306
192.168.100.55:
[root@node2 ~]# mysql -uroot -p123456
[root@node2 ~]# mysql -upt_user -ppt_pass -h192.168.100.55 -P3306
root@db 23:37: [test]> create user ‘pt_user’@’%’ identified with mysql_native_password by ‘pt_pass’;
验证远程登陆是否成功:
[root@node2 ~]# mysql -upt_user -ppt_pass -h192.168.100.33 -P3306
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
pt-archiver --source h=localhost,u=pt_user,p=pt_pass,P=3306,D=db1,t=t --dest h=192.168.100.55,P=3306,u=pt_user,p=pt_pass,D=db1,t=t_bak --progress 5000 --where 'id<=100000' --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete
复制A software update is available:
TIME ELAPSED COUNT
2024-07-01T01:38:30 0 0
2024-07-01T01:38:34 4 5000
2024-07-01T01:38:38 8 10000
2024-07-01T01:38:44 13 15000
2024-07-01T01:38:47 17 20000
2024-07-01T01:38:51 21 25000
2024-07-01T01:38:55 25 30000
2024-07-01T01:38:58 28 35000
2024-07-01T01:39:02 32 40000
2024-07-01T01:39:07 36 45000
2024-07-01T01:39:10 40 50000
2024-07-01T01:39:14 44 55000
2024-07-01T01:39:20 50 60000
2024-07-01T01:39:24 54 65000
2024-07-01T01:39:28 58 70000
2024-07-01T01:39:32 62 75000
2024-07-01T01:39:36 65 80000
2024-07-01T01:39:39 69 85000
2024-07-01T01:39:43 73 90000
2024-07-01T01:39:48 77 95000
2024-07-01T01:39:51 81 100000
2024-07-01T01:39:51 81 100000
Started at 2024-07-01T01:38:30, ended at 2024-07-01T01:39:51
Source: A=UTF8,D=db1,P=3306,h=localhost,p=…,t=t,u=pt_user
Dest: A=UTF8,D=db1,P=3306,h=192.168.100.55,p=…,t=t_bak,u=pt_user
SELECT 100000
INSERT 100000
DELETE 0
Action Count Time Pct
inserting 100000 71.3730 87.68
commit 202 0.3057 0.38
select 11 0.2292 0.28
other 0 9.4937 11.66
[root@node1 ~]#
3）复制数据到其他mysql实例，并删source上的旧数据(指定字符集)：
pt-archiver --source h=localhost,u=pt_user,p=pt_pass,P=3306,D=db1,t=t --dest h=192.168.100.55,P=3306,u=pt_user,p=pt_pass,D=db1,t=t_bak --progress 5000 --where ‘id<=100000’ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete
[root@node1 ~]# pt-archiver --source h=localhost,u=pt_user,p=pt_pass,P=3306,D=db1,t=t --dest h=192.168.100.55,P=3306,u=pt_user,p=pt_pass,D=db1,t=t_bak --progress 5000 --where ‘id<=100000’ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete
TIME ELAPSED COUNT
2024-07-01T01:57:48 0 0
2024-07-01T01:57:51 3 5000
2024-07-01T01:57:56 8 10000
2024-07-01T01:58:02 14 15000
2024-07-01T01:58:06 18 20000
2024-07-01T01:58:12 24 25000
2024-07-01T01:58:16 27 30000
2024-07-01T01:58:22 34 35000
2024-07-01T01:58:26 38 40000
2024-07-01T01:58:33 44 45000
2024-07-01T01:58:36 48 50000
2024-07-01T01:58:42 53 55000
2024-07-01T01:58:45 57 60000
2024-07-01T01:58:52 63 65000
2024-07-01T01:58:55 67 70000
2024-07-01T01:59:02 73 75000
2024-07-01T01:59:05 77 80000
2024-07-01T01:59:11 83 85000
2024-07-01T01:59:14 86 90000
2024-07-01T01:59:14 86 90000
Started at 2024-07-01T01:57:48, ended at 2024-07-01T01:59:15
Source: A=UTF8,D=db1,P=3306,h=localhost,p=…,t=t,u=pt_user
Dest: A=UTF8,D=db1,P=3306,h=192.168.100.55,p=…,t=t_bak,u=pt_user
SELECT 90000
INSERT 90000
DELETE 90000
Action Count Time Pct
inserting 90000 76.0220 87.69
bulk_deleting 9 2.0191 2.33
commit 182 0.3646 0.42
select 10 0.0813 0.09
other 0 8.2112 9.47
源端：192.168.100.33:
root@db 01:35: [db1]> select count(*) from t;
Current database: db1
±---------+
| count(*) |
±---------+
| 0 |
±---------+
1 row in set (0.01 sec)
目标库：192.168.100.55
root@db 01:57: [db1]> select count() from t_bak;
±---------+
| count() |
±---------+
| 90000 |
±---------+
1 row in set (0.07 sec)
4）复制数据到其他mysql实例，不删除source数据，但是使用批量插入dest上新的数据(指定字符集)：效率更高,使用bulk-insert用时7秒钟。而常规insert用时40秒。
pt-archiver --source h=192.168.100.55,u=pt_user,p=pt_pass,P=3306,D=db1,t=t_bak --dest h=192.168.100.33,P=3306,u=pt_user,p=pt_pass,D=db1,t=t --progress 5000 --where ‘id<=100000’ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete --bulk-insert
