1.工具简介
percona-toolkit版本:2.2.19-1
pt-archiver版本: 2.2.18
pt-archiver可以很轻松的将生产环境的历史数据归档到文件或者直接删除,还可以不同主机间同步数据,而不用将数据落盘(不用导出导入),类似oracle的impdp+dblink一样方便。
2.归档数据到文件
示例环境:
数据库服务器:192.168.56.103:3306/sakila
归档服务器:192.168.56.101:/home/mysql/arch
需求:将sakila数据库中city表的数据归档到192.168.56.101:/home/mysql/arch下
ps:表一定要有主键啊,否则很多功能不能用,innodb表没有主键也是够了。
在192.168.56.101上安装pt-archiver,然后归档:
[mysql@hpc01 arch]$ pt-archiver --source h=192.168.56.103,u=root,p=111111,D=sakila,t=city,A=utf8\ > --no-delete --file '/home/mysql/arch/%Y-%m-%d-%D.%t'\ > --where "1=1" --share-lock --statistics --progress 500 --limit 500 --txn-size 500 TIME ELAPSED COUNT 2016-10-09T21:01:16 0 0 2016-10-09T21:01:16 0 500 2016-10-09T21:01:16 0 599 Started at 2016-10-09T21:01:16, ended at 2016-10-09T21:01:16 Source: A=utf8,D=sakila,h=192.168.56.103,p=...,t=city,u=root SELECT 599 INSERT 0 DELETE 0 Action Count Time Pct select 3 0.0520 60.44 commit 2 0.0012 1.36 print_file 599 0.0009 1.02 other 0 0.0320 37.18 [mysql@hpc01 arch]$ ls -l total 24 -rw-rw-r-- 1 mysql mysql 21719 Oct 9 21:01 2016-10-09-sakila.city
参数解释:
--source:指定目标表信息,我们只是将表备份到文件,只需指定source即可
--share-lock:给表加上LOCK IN SHARE MODE,提供读一致性
h:主机IP
u:数据库用户
p:密码
D:数据库名
t:表名
A:字符集
--no-delete:归档数据后,不删除源表数据(如果需要删除源表数据,--no-delete改为--purge即可)
--file:数据存放的文件,最好指定绝对路径,文件名可以灵活地组合。
%d Day of the month, numeric (01..31) %H Hour (00..23) %i Minutes, numeric (00..59) %m Month, numeric (01..12) %s Seconds (00..59) %Y Year, numeric, four digits %D Database name %t Table name
--where:删除表中指定的数据,根据自己的需求限定,全部删除就给1=1即可
--statistics:打印出整个归档过程的统计信息
--limit:每次fecth多少行数据,类似游标获取,默认为1。增改该值,有助于加速归档
--progress:打印导出过程中的信息,当前时间,当前一共耗费多少时间,当前fetch数据行数
--txn-size:每个事物提交的数据行数,批量提交。增加该值可以提升归档性能。
3.清除表中的历史数据
对于某些应用,历史数据需要定期清理,pt-archiver就是一个不错的选择。对于某些heavy oltp系统,一次性删除大量数据,会有较大性能影响,可以分批删除。
示例:
root@localhost [test] 09:39:39>>>show create table example \G *************************** 1. row *************************** Table: example Create Table: CREATE TABLE `example` ( `id` bigint(20) NOT NULL, `name` varchar(32) DEFAULT NULL, `value` varchar(32) DEFAULT NULL, `other` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`)/*一定要有主键呀*/ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost [test] 09:39:47>>>select * from example limit 5; +-----+--------+---------+---------+ | id | name | value | other | +-----+--------+---------+---------+ | 100 | Name 1 | Value 1 | Other 1 | | 200 | Name 2 | Value 2 | Other 2 | | 300 | Name 3 | Value 3 | Other 3 | | 400 | Name 4 | Value 4 | Other 4 | | 500 | Name 5 | Value 5 | Other 5 | +-----+--------+---------+---------+ 5 rows in set (0.00 sec) root@localhost [test] 09:39:58>>>select count(*) from example; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.01 sec)
我们删除id<1000000的所有数据
[mysql@hpc01 arch]$ pt-archiver --source h=192.168.56.103,u=root,p=111111,\ D=test,t=example,A=utf8 --purge --where "id<1000000" --statistics \ --progress 5000 --limit 5000 --txn-size 5000 TIME ELAPSED COUNT 2016-10-09T21:44:48 0 0 2016-10-09T21:44:51 2 5000 2016-10-09T21:44:54 5 9999 Started at 2016-10-09T21:44:48, ended at 2016-10-09T21:44:54 Source: A=utf8,D=test,h=192.168.56.103,p=...,t=example,u=root SELECT 9999 INSERT 0 DELETE 9999 Action Count Time Pct deleting 9999 4.6887 82.56 commit 2 0.0611 1.08 select 3 0.0221 0.39 other 0 0.9071 15.97
4.不同库之间同步数据
使用pt-archiver可以将历史数据搬到备份库,或者在两个库之间在线同步数据。
需求:将192.168.56.103:3306/TEST的example表中的数据同步到192.168.56.101:3306/TEST的example表中,删源表数据。
[mysql@hpc01 home]$ pt-archiver --source h=192.168.56.103,u=root,p=111111 \ ,D=test,t=example,A=utf8 --purge --dest h=192.168.56.101,P=3306,D=test,t=example \ --where "1=1" --share-lock --statistics --progress 5000 --limit 5000 --txn-size 5000 TIME ELAPSED COUNT 2016-10-09T22:09:34 0 0 2016-10-09T22:09:42 8 5000 2016-10-09T22:09:51 16 10000 2016-10-09T22:10:00 26 15000 2016-10-09T22:10:09 34 20000 2016-10-09T22:10:17 42 25000 2016-10-09T22:10:26 51 30000 2016-10-09T22:10:34 60 35000 2016-10-09T22:10:42 67 40000 2016-10-09T22:10:50 75 45000 2016-10-09T22:10:58 83 50000 2016-10-09T22:10:58 83 50000 Started at 2016-10-09T22:09:34, ended at 2016-10-09T22:10:58 Source: A=utf8,D=test,h=192.168.56.103,p=...,t=example,u=root Dest: A=utf8,D=test,P=3306,h=192.168.56.101,p=...,t=example,u=root SELECT 50000 INSERT 50000 DELETE 50000 Action Count Time Pct deleting 50000 52.6223 62.77 inserting 50000 19.5000 23.26 commit 22 0.5376 0.64 select 11 0.2329 0.28 other 0 10.9463 13.06
参数解释:
--dest:目标库的信息,包括主机地址,port,库名等。
如果不删除源表数据,将--purge改为--delete。