如何利用pt-archiver工具归档和清理数据


   数据库管理员经常会遇到需定期对数据进行归档和清除,DBA可利用mysql中percona的pt-archiver工具能完成这一功能,使得数据归档变得方便简单。
下面是对数据归档和清除实例:

查看表的大小、记录数:
[ls]> show table status like 't_od'\G
*************************** 1. row ***************************
           Name: t_od
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6764449
 Avg_row_length: 541
    Data_length: 3661627392
Max_data_length: 0
   Index_length: 1888419840
      Data_free: 319815680
 Auto_increment: 7392306
    Create_time: 2016-07-25 21:14:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

查看表结构:
[ls]> show create table t_od
->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
.......................................
  `excp_reason` varchar(250) DEFAULT NULL COMMENT '异常原因',
  PRIMARY KEY (`ID`))
ENGINE=InnoDB AUTO_INCREMENT=7392306 DEFAULT CHARSET=utf8;


建立归档表:

CREATE TABLE `t_od20151001` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
.......................................
  `excp_reason` varchar(250) DEFAULT NULL COMMENT '异常原因',
  PRIMARY KEY (`ID`))
ENGINE=InnoDB AUTO_INCREMENT=7392306 DEFAULT CHARSET=utf8;


场景一:将表中小于2015-10-01 00:00:00时间的数据进行归档,不删除原表记录
[apps@mvxl0782 bin]$ ./pt-archiver  --source h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od --dest h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od20151001

--no-check-charset  --where 'CREATE_DATE<"2015-10-01 00:00:00"'  --progress 5000  --no-delete --limit=10000 --statistics
TIME                ELAPSED   COUNT
2016-07-27T20:51:20       0       0
2016-07-27T20:51:23       2    5000
2016-07-27T20:51:25       4   10000
2016-07-27T20:51:28       7   15000
2016-07-27T20:51:30       9   20000
2016-07-27T20:51:32      11   25000
2016-07-27T20:51:35      14   30000
2016-07-27T20:51:37      16   35000
2016-07-27T20:51:39      18   40000
2016-07-27T20:51:42      21   45000
2016-07-27T20:51:44      23   50000
2016-07-27T20:51:46      26   55000
2016-07-27T20:51:49      28   60000
2016-07-27T20:51:51      30   65000
2016-07-27T20:51:53      33   70000
2016-07-27T20:51:56      35   75000
.................................
2016-07-27T20:53:39     138  300000
2016-07-27T20:53:41     141  305000
2016-07-27T20:53:44     143  310000
2016-07-27T20:53:46     145  315000
2016-07-27T20:53:48     147  320000
2016-07-27T20:53:51     150  325000
2016-07-27T20:53:53     152  330000
2016-07-27T20:53:55     154  335000
2016-07-27T20:53:57     157  340000
2016-07-27T20:54:00     159  345000
2016-07-27T20:54:02     161  350000
2016-07-27T20:54:44     203  354980
Started at 2016-07-27T20:51:20, ended at 2016-07-27T20:54:54
Source: D=ls,P=3306,h=168.24.65.31,p=...,t=t_od,u=lsprd
Dest:   D=ls,P=3306,h=168.24.65.31,p=...,t=t_od20151001,u=lsprd
SELECT 354980
INSERT 354980
DELETE 0
Action         Count       Time        Pct
inserting     354980    83.2474      38.87
select            37    53.5745      25.02
commit        709962    51.4813      24.04
other              0    25.8578      12.07
上面显示在不带删除原记录时归档354980行数据所花时间为203秒。

[ls]> select count(*) from ls.t_od where CREATE_DATE<'2015-10-01 00:00:00';
+----------+
| count(*) |
+----------+
|   354980 |
+----------+
1 row in set (0.12 sec)


(product)root@localhost [ls]> select count(*) from ls.t_od20151001 where CREATE_DATE<'2015-10-01 00:00:00';
+----------+
| count(*) |
+----------+
|   354980 |
+----------+
1 row in set (0.19 sec)

上面查询显示没有删除原记录。

场景二:将表中小于2015-10-01 00:00:00时间的数据进行归档,删除原表记录
 [ls]> truncate table ls.t_od20151001;
Query OK, 0 rows affected (0.31 sec)

[apps@mvxl0782 bin]$ ./pt-archiver  --source h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od --dest h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od20151001

--no-check-charset  --where 'CREATE_DATE<"2015-10-01 00:00:00"'  --progress 5000  --limit=10000 --statistics
TIME                ELAPSED   COUNT
2016-07-27T21:00:36       0       0
2016-07-27T21:00:41       4    5000
2016-07-27T21:00:44       8   10000
2016-07-27T21:00:48      12   15000
2016-07-27T21:00:52      16   20000
2016-07-27T21:00:56      19   25000
2016-07-27T21:00:59      23   30000
2016-07-27T21:01:03      26   35000
....................................

2016-07-27T21:04:38     242  335000
2016-07-27T21:04:42     245  340000
2016-07-27T21:04:45     249  345000
2016-07-27T21:04:49     252  350000
2016-07-27T21:05:13     276  354980
Started at 2016-07-27T21:00:36, ended at 2016-07-27T21:05:23
Source: D=ls,P=3306,h=168.24.65.31,p=...,t=t_od,u=lsprd
Dest:   D=ls,P=3306,h=168.24.65.31,p=...,t=t_od20151001,u=lsprd
SELECT 354980
INSERT 354980
DELETE 354980
Action         Count       Time        Pct
inserting     354980    86.4632      30.10
commit        709962    75.4800      26.27
deleting      354980    60.8275      21.17
select            37    34.0255      11.84
other              0    30.4814      10.61
上面显示在带有删除原记录时归档354980行数据所花时间为276秒。

 [ls]> select count(*) from ls.t_od where CREATE_DATE<'2015-10-01 00:00:00';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

 

 [ls]> select count(*) from ls.t_od20151001 where CREATE_DATE<'2015-10-01 00:00:00';
+----------+
| count(*) |
+----------+
|   354980 |
+----------+
1 row in set (0.19 sec)
上面查询显示有删除原记录。

场景三:将表中小于2015-10-10 00:00:00时间的数据进行归档,删除原表记录
[apps@mvxl0782 bin]$ ./pt-archiver  --source h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od --dest h=168.24.65.31,P=3306,u=lsprd,p=safe2016,D=ls,t=t_od20151001

--no-check-charset  --where 'CREATE_DATE<"2015-10-10 00:00:00"'  --progress 5000  --limit=10000 --statistics
TIME                ELAPSED   COUNT
2016-07-27T21:27:32       0       0
2016-07-27T21:27:36       4    5000
2016-07-27T21:27:40       7   10000
2016-07-27T21:27:43      11   15000
.................................
2016-07-27T21:28:45      72  100000
2016-07-27T21:29:09      96  105000
2016-07-27T21:29:12     100  109139
Started at 2016-07-27T21:27:32, ended at 2016-07-27T21:29:32
Source: D=ls,P=3306,h=168.24.65.31,p=...,t=t_od,u=lsprd
Dest:   D=ls,P=3306,h=168.24.65.31,p=...,t=t_od20151001,u=lsprd
SELECT 109139
INSERT 109139
DELETE 109139
Action         Count       Time        Pct
select            12    41.3343      34.43
inserting     109139    26.8019      22.33
commit        218280    23.4712      19.55
deleting      109139    18.5813      15.48
other              0     9.8514       8.21

[ls]> select count(*) from ls.t_od where CREATE_DATE<'2015-10-10 00:00:00';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

 [ls]> select count(*) from ls.t_od20151001 where CREATE_DATE<'2015-10-10 00:00:00';
+----------+
| count(*) |
+----------+
|  464119  |
+----------+
1 row in set (0.24 sec)
从上面查询的结果来看,第二次归档是将记录追加到归档表中。

实际上我们可以将数据归档到其它历史库中,操作方法一样。


pt-archiver使用的场景:
1、清理线上过期数据
2、清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器
3、两张表之间的数据不完全相同,希望合并。此时加上–ignore或–replace选项,可以轻松实现
4、导出线上数据,到线下数据作处理


其它作用:
1、用于清理过期数据purge:
执行命令:pt-archiver  --source h=127.0.0.1,P=58886,D=test,t=t_archive --no-check-charset  --where 'insertdate<"2013-08-10"'  --purge --limit=2
1)注意--source后的DSN之间不能空格出现,否则会出错。 --where条件的值,有字符串的,要用引号括起来。
2) --limit表示,每组一次删除多少条数据(注意:如果数据比较多时,也可以设置大一些,减少循环次数),最终的清理操作,还是通过Where pK=xx来处理的;

2、用于把数据导出文件,不用删除原表中数据
命令使用:
pt-archiver  --source h=127.0.0.1,P=58886,D=test,t=t_info_refresh  --no-check-charset  --where 'AddTime>"2013-05-02"'  --progress 4000  --no-delete --file "/tmp/pt-archiver.dat"  --limit=10000
参数说明:
      --statistics  结束的时候给出统计信息:开始的时间点,结束的时间点,查询的行数,归档的行数,删除的行数,以及各个阶段消耗的总的时间和比例,便于以此进行优化。
      --progress  每处理progress指定的行数后,就打印一次信息
      --no-delete :表示不删除原来的数据,注意:如果不指定此参数,所有处理完成后,都会清理原表中的数据。 


 

没有更多推荐了,返回首页