MySQL · 2016-10-09 0

Percona-Toolkit系列之pt-archiver数据归档利器

1.工具简介
percona-toolkit版本:2.2.19-1
pt-archiver版本: 2.2.18

pt-archiver可以很轻松的将生产环境的历史数据归档到文件或者直接删除,还可以不同主机间同步数据,而不用将数据落盘(不用导出导入),类似oracle的impdp+dblink一样方便。

pt

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。