数据库归档工具 pt-archiver 的使用

简介

pt-archiver 属于大名鼎鼎的 percona 工具集的一员，是归档 MySQL 大表数据的最佳轻量级工具之一。

安装

官网下载地址，选择 Percona Toolkit 版本和操作系统平台，具体如下图

解压缩，所有工具都在 bin 目录下

使用

注意：pt-archiver操作的表必须有主键

/root/test_archiver/pt-3.5.7/bin/pt-archiver \
--source h=ip,P=port,u=root,p='',D=testdb,t=tb_archiver \
--dest h=ip,P=3306,u=root,p='',D=testdb,t=tb_archiver \
--charset=UTF8 --where '1=1' --progress 1000 --limit=10000 --txn-size 1000 --bulk-insert --bulk-delete --statistics --purge >> /root/test_archiver/a.log

遇到的问题

问题1

Can't locate Digest/MD5.pm in @INC

解决方法

yum -y install perl-Digest-MD5

问题2

Can't locate DBI.pm in @INC

解决方法

yum -y install perl-DBD-MySQL

问题3

failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /root/test_archiver/pt-3.5.7/bin/pt-archiver line 2545.

我们使用的是 MySQL8.0，修改 root 用户的身份验证器插件为 mysql_native_password

use mysql;
select host,user,plugin from user; -- 查看当前所有用户
UPDATE `mysql`.`user` SET `plugin`='mysql_native_password' WHERE `Host`='%' AND `User`='root';

结果改了之后，其他客户端如 阿里云dms 都连不上服务器了。还需要将 root 用户改回去，创建新的用户并授权，重启 MySQL 服务器。

UPDATE `mysql`.`user` SET `plugin`='caching_sha2_password' WHERE `Host`='%' AND `User`='root';

创建新用户并授权（源数据库和目标数据库都需要）

create USER 'szz'@'%' IDENTIFIED WITH mysql_native_password BY 'szz123';
GRANT ALL ON *.* TO 'szz'@'%';
FLUSH PRIVILEGES;

然后对 pt-archiver 命令 使用新的用户 szz 及密码，a.log 可以查看执行过程中的日志。

/root/test_archiver/pt-3.5.7/bin/pt-archiver \
--source h=ip,P=port,u=szz,p='szz123',D=testdb,t=tb_archiver \
--dest h=ip,P=3306,u=szz,p='szz123',D=testdb,t=tb_archiver \
--charset=UTF8 --where '1=1' --progress 1000 --limit=10000 --txn-size 1000 --bulk-insert --bulk-delete --statistics --purge >> /root/test_archiver/a.log

问题4

DBD::mysql::st execute failed: Loading local data is disabled;

查看是否开启加载本地文件

show variables like 'local_infile'; -- OFF 未开启
set global local_infile=on;  -- 开启此功能

常用参数

参数	描述
--source	源库信息
--dest	目标库信息
--where 'id<3000'	设置操作条件
--limit 10000	每次取10000行数据给pt-archive处理
--txn-size 1000	设置1000行为一个事务提交一次
--progress 5000	每处理5000行输出一次处理信息
--statistics	结束的时候给出统计信息：开始的时间点，结束的时间点，查询的行数，归档的行数，删除的行数，以及各个阶段消耗的总的时间和比例，便于以此进行优化。只要不加上--quiet，默认情况下pt-archive都会输出执行过程的
--charset=UTF8	指定字符集为UTF8
--no-delete	表示不删除原来的数据，注意：如果不指定此参数，所有处理完成后，都会清理原表中的数据
--bulk-delete	批量删除source上的旧数据
--bulk-insert	批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)
--purge	删除source数据库的相关匹配记录
--local	不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大）
--analyze=ds	操作结束后，优化表空间（d表示dest，s表示source）默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后，在业务低谷手动执行analyze table用以回收表空间

参考

优雅地使用pt-archiver进行数据归档
MySQL8.0登录提示caching_sha2_password问题解决方法