pt-archiver 归档表到文件

原创 huayumicheng 2022-11-29

1075

1、pt-archiver 用途

1、清理线上过期数据；
2、导出线上数据，到线下数据作处理；
3、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器。复制

2、安装pt-archiver

yum install perl-DBD-MySQL perl-DBI perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN perl-IO-Socket-SSL  perl-Time-HiRes  perl-TermReadKey  -y 
rpm -ivh percona-toolkit-3.3.1-1.el7.x86_64.rpm 复制

3、新建测试表，导入数据

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

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `row_create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `row_lastupdate_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8mb4;


mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   667648 |
+----------+
mysql> select round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) as size_MB from information_schema.TABLES where TABLE_SCHEMA='mydb' and table_name='t1';

+---------+
| size_MB |
+---------+
|   26.56 |
+---------+

复制

4、归档数据到文件

--场景1-1：全表归档，不删除原表数据，归档到文件

[root@mysql4 ~]# pt-archiver --source h=192.168.100.23,P=3306,u=test,p='123456',D=mydb,t=t1  --no-check-charset --file=/tmp/archive.save --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-safe-auto-increment --no-delete


--summary
Started at 2022-05-07T21:00:53, ended at 2022-05-07T21:01:05
Source: D=mydb,P=3306,h=192.168.100.23,p=...,t=t1,u=test
SELECT 667648
INSERT 0
DELETE 0
Action          Count       Time        Pct
select             68     0.4924       4.10
commit             67     0.0082       0.07
print_file     667648    -1.7458     -14.53
other               0    13.2583     110.37

[root@mysql4 ~]# ls -lh /tmp/archive.save 
-rw-r--r-- 1 root root 30M May  7 21:01 /tmp/archive.save

--加载文件到数据库
mysql> load data local infile "/tmp/archive.save" into table mydb.t1;
Query OK, 667648 rows affected (3.01 sec)
Records: 667648  Deleted: 0  Skipped: 0  Warnings: 0

--场景1-2：id<1001，不删除原表数据，归档到文件


[root@mysql4 ~]# pt-archiver --source h=192.168.100.23,P=3306,u=test,p='123456',D=mydb,t=t1  --no-check-charset --file=/tmp/archive.save --where 'id<1001' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-safe-auto-increment --no-delete
TIME                ELAPSED   COUNT
2022-05-07T21:22:10       0       0
2022-05-07T21:22:10       0     682
Started at 2022-05-07T21:22:10, ended at 2022-05-07T21:22:10
Source: D=mydb,P=3306,h=192.168.100.23,p=...,t=t1,u=test
SELECT 682
INSERT 0
DELETE 0
Action          Count       Time        Pct
select              2     0.0014      10.80
commit              1     0.0004       2.88
print_file        682    -0.0002      -1.21
other               0     0.0110      87.53

--场景1-3：id>=1 and id<10001，不删除原表数据，归档到文件

[root@mysql4 ~]# pt-archiver --source h=192.168.100.23,P=3306,u=test,p='123456',D=mydb,t=t1  --no-check-charset --file=/tmp/archive.save --where 'id>=1 and id<10001' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-safe-auto-increment --no-delete
TIME                ELAPSED   COUNT
2022-05-07T21:26:24       0       0
2022-05-07T21:26:24       0    7081
Started at 2022-05-07T21:26:24, ended at 2022-05-07T21:26:24
Source: D=mydb,P=3306,h=192.168.100.23,p=...,t=t1,u=test
SELECT 7081
INSERT 0
DELETE 0
Action          Count       Time        Pct
select              2     0.0057       5.10
commit              1     0.0004       0.35
print_file       7081     0.0001       0.07
other               0     0.1061      94.48
复制

5、常用参数


--常用参数

--where 'id<3000'	设置操作条件
--limit 10000	    每次取1000行数据给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用以回收表空间复制