Percona-toolkit工具包系列文章
DBA经常会遇到需定期对数据进行归档和清除，可利percona的pt-archiver工具能完成这一功能，使得数据归档变得方便简单。pt-archiver可以很轻松的将生产环境的历史数据归档到文件或者直接删除，还可以不同主机间同步数据，而不用将数据落盘，实现的功能有点类似Oracle的数据泵和dblink；pt-archiver一款非常好用的数据归档及清理历史数据的工具，工作中可以起到事半功倍的效果；
pt-archiver使用的场景:
1、清理线上过期数据
2、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器
3、两张表之间的数据不完全相同，希望合并。此时加上–ignore或–replace选项，可以轻松实现
4、导出线上数据，到线下数据作处理
测试环境
MYSQL 5.7多实例环境，端口分别是3306和3307
一、将历史数据归档到文件中
由于在使用pt-archiver做数据归档后要依赖于perl-DBI和perl-DBD-MySQL包，否则就会出现如下的报错信息，所以在工具使用前，做好将相关的依赖包安装好；
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI. If DBI is not installed, try: Debian/Ubuntu apt-get install libdbi-perl RHEL/CentOS yum install perl-DBI OpenSolaris pkg install pkg:/SUNWpmdbi
解决方法：
[root@VM_54_118_centos ~]# yum install perl-DBI [root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found. Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql. If DBD::mysql is not installed, try: Debian/Ubuntu apt-get install libdbd-mysql-perl RHEL/CentOS yum install perl-DBD-MySQL OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql
[root@VM_54_118_centos ~]# yum install perl-DBD-MySQL
示例：
将端口为3306MYSQL实例中wjq数据库中wjq_innodb_count1表的数据归档到/tmp/arch目录下
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete TIME ELAPSED COUNT 2019-03-09T16:26:49 0 0 2019-03-09T16:26:49 0 5000 2019-03-09T16:26:49 0 10000 2019-03-09T16:26:49 0 15000 2019-03-09T16:26:49 0 20000 2019-03-09T16:26:49 0 25000 2019-03-09T16:26:50 0 30000 2019-03-09T16:26:50 0 35000 2019-03-09T16:26:50 1 40000 2019-03-09T16:26:50 1 45000 2019-03-09T16:26:50 1 50000 2019-03-09T16:26:50 1 55000 2019-03-09T16:26:50 1 60000 2019-03-09T16:26:50 1 65000 2019-03-09T16:26:51 1 70000 2019-03-09T16:26:51 1 75000 2019-03-09T16:26:51 2 80000 2019-03-09T16:26:51 2 85000 2019-03-09T16:26:51 2 90000 2019-03-09T16:26:51 2 95000 2019-03-09T16:26:51 2 100000 2019-03-09T16:26:51 2 100001 Started at 2019-03-09T16:26:49, ended at 2019-03-09T16:26:51 Source: A=utf8mb4,D=wjq,S=/tmp/mysql3306.sock,h=localhost,p=...,t=wjq_innodb_count1,u=root SELECT 100001 INSERT 0 DELETE 0 Action Count Time Pct select 12 0.1744 6.59 commit 101 0.0048 0.18 print_file 100001 -0.0452 -1.71 other 0 2.5118 94.93
[root@VM_54_118_centos ~]# ll /tmp/arch/ total 2340 -rw-r--r-- 1 root root 2388903 Mar 9 16:26 2019-03-09-wjq.wjq_innodb_count1.arch
通过上面结果可以看出，我们将数据对上到了2019-03-09-wjq.wjq_innodb_count1.arch文件中；
针对上述pt-archiver的相关参数简单解释一下：
–source：指定目标库相关的信息，如果只是将表备份到文件，只需指定source即可，如果是不同主机之间同步数据的话，还需要制定–dest
–share-lock：给表加上LOCK IN SHARE MODE,提供读一致性
h：主机IP
S：数据库的sock文件
u：数据库用户
p：密码
D：数据库名
t：表名
A:字符集
–no-delete：归档数据后，不删除源表数据(如果需要删除源表数据,–no-delete改为–purge即可)
–file：数据存放的文件，最好指定绝对路径，文件名可以灵活地组合。
–where：删除表中指定的数据，根据自己的需求限定，全部删除就给1=1即可
–statistics：打印出整个归档过程的统计信息
–limit：每次fecth多少行数据，类似游标获取，默认为1。增改该值，有助于加速归档
–progress：打印导出过程中的信息，当前时间，当前一共耗费多少时间，当前fetch数据行数
–txn-size：每个事物提交的数据行数，批量提交。增加该值可以提升归档性能。
二、不同实例之间同步数据
使用pt-archiver可以将历史数据搬到备份库，或者在两个库之间在线同步数据。
示例
将端口3306的employees的employees表中的数据同步到端口3307的wjq库下的employees表中，不删除源表数据。
1、查看表的大小、记录数
root@localhost [3306][employees]>show table status like 'employees'\G;
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 299157
Avg_row_length: 50
Data_length: 15220736
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2018-11-24 20:39:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2、查看源实例的表结构
root@localhost [3306][employees]>show create table employees\G;
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
3、在目标实例上创建相同的表结构，此时可以看到目标表中的数据条数为0
root@localhost [3307][wjq]>select count(*) from employees; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
4、将数据从源实例表中导入到目标实例表中
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=employees,t=employees,A=utf8mb4 --dest h=localhost,S=/tmp/mysql3307.sock,D=wjq,t=employees --where "1=1" --progress 10000 --statistics --limit=20000 --txn-size 1000 --no-delete TIME ELAPSED COUNT 2019-03-11T13:41:09 0 0 2019-03-11T13:41:11 1 10000 2019-03-11T13:41:12 3 20000 2019-03-11T13:41:13 4 30000 2019-03-11T13:41:15 5 40000 2019-03-11T13:41:16 7 50000 2019-03-11T13:41:18 8 60000 2019-03-11T13:41:19 10 70000 2019-03-11T13:41:21 11 80000 2019-03-11T13:41:22 13 90000 2019-03-11T13:41:24 14 100000 2019-03-11T13:41:25 16 110000 2019-03-11T13:41:27 17 120000 2019-03-11T13:41:28 19 130000 2019-03-11T13:41:29 20 140000 2019-03-11T13:41:31 21 150000 2019-03-11T13:41:32 23 160000 2019-03-11T13:41:34 24 170000 2019-03-11T13:41:35 26 180000 2019-03-11T13:41:36 27 190000 2019-03-11T13:41:38 28 200000 2019-03-11T13:41:39 30 210000 2019-03-11T13:41:41 31 220000 2019-03-11T13:41:42 33 230000 2019-03-11T13:41:43 34 240000 2019-03-11T13:41:45 35 250000 2019-03-11T13:41:46 37 260000 2019-03-11T13:41:48 38 270000 2019-03-11T13:41:49 40 280000 2019-03-11T13:41:51 41 290000 2019-03-11T13:41:52 43 300000 2019-03-11T13:41:52 43 300024 Started at 2019-03-11T13:41:09, ended at 2019-03-11T13:41:52 Source: A=utf8mb4,D=employees,S=/tmp/mysql3306.sock,h=localhost,p=...,t=employees,u=root Dest: A=utf8mb4,D=wjq,S=/tmp/mysql3307.sock,h=localhost,p=...,t=employees,u=root SELECT 300024 INSERT 300024 DELETE 0 Action Count Time Pct inserting 300024 29.7888 69.23 commit 602 1.2850 2.99 select 17 0.2557 0.59 other 0 11.6980 27.19
5、同步成功后，可以看到，目标实例表中的数据条数和源实例的条数相同
root@localhost [3307][wjq]>select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.05 sec)
三、清除表中历史数据
在生产环境中，历史数据需要定期清理，否则可能会导致磁盘占用非常的大，此时pt-archiver就派上用场啦。对于OLTP事务性应用，如果一次性删除大量数据，可能会产生较大性能影响，可以选择分批删除。
1、删除之前，表中的数据条数如下
root@localhost [3307][wjq]>select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.06 sec)
2、删除emp_no<100000的数据
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3307.sock,D=wjq,t=employees,A=utf8mb4 --where "emp_no<100000" --progress 5000 --statistics --limit=10000 --txn-size 1000 --purge TIME ELAPSED COUNT 2019-03-11T13:53:56 0 0 2019-03-11T13:53:57 0 5000 2019-03-11T13:53:57 1 10000 2019-03-11T13:53:58 2 15000 2019-03-11T13:53:59 3 20000 2019-03-11T13:54:00 3 25000 2019-03-11T13:54:01 4 30000 2019-03-11T13:54:01 5 35000 2019-03-11T13:54:02 6 40000 2019-03-11T13:54:03 7 45000 2019-03-11T13:54:04 7 50000 2019-03-11T13:54:05 8 55000 2019-03-11T13:54:05 9 60000 2019-03-11T13:54:06 10 65000 2019-03-11T13:54:07 10 70000 2019-03-11T13:54:08 11 75000 2019-03-11T13:54:08 12 80000 2019-03-11T13:54:09 13 85000 2019-03-11T13:54:10 14 89999 Started at 2019-03-11T13:53:56, ended at 2019-03-11T13:54:10 Source: A=utf8mb4,D=wjq,S=/tmp/mysql3307.sock,h=localhost,p=...,t=employees,u=root SELECT 89999 INSERT 0 DELETE 89999 Action Count Time Pct deleting 89999 9.9367 70.82 commit 90 0.5391 3.84 select 10 0.0795 0.57 other 0 3.4764 24.78
3、查看删除后的数据条数
root@localhost [3307][wjq]>select count(*) from employees; +----------+ | count(*) | +----------+ | 210025 | +----------+ 1 row in set (0.03 sec)
pt-archiver使用详细参考官方文档说明：
https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html
如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：【Percona-toolkit系列】Percona Toolkit工具之pt-archiver
转载请注明原文链接：【Percona-toolkit系列】Percona Toolkit工具之pt-archiver
