pt-archiver 数据归档bug

最新推荐文章于 2024-10-29 14:52:08 发布

weixin_34257076

最新推荐文章于 2024-10-29 14:52:08 发布

阅读量248

点赞数

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3023401/blog/1486759

2019独角兽企业重金招聘Python工程师标准>>>

前言：今天在进行线上数据库数据归档的时候发现个问题，ID最大的那一列数据没有归档到其他实例，下面是现场及解决办法

现场：

root@localhost:mysql.sock  10:18:07 [tom]>select count(*) from test;
+----------+
| count(*) |
+----------+
|    88787 |
+----------+
1 row in set (0.09 sec)

[root@redis02 ~]# pt-archiver --source h=10.0.0.6,u=root,p=123456,D=tom,t=test,A=utf8 --dest h=10.0.0.7,P=3306,D=tom,t=test --where "1=1" --share-lock --statistics  --progress 5000 --limit 5000 --txn-size 5000
TIME                ELAPSED   COUNT
2017-07-25T10:36:54       0       0
2017-07-25T10:37:07      13    5000
2017-07-25T10:37:21      26   10000
2017-07-25T10:37:36      42   15000
2017-07-25T10:37:50      55   20000
2017-07-25T10:38:02      67   25000
2017-07-25T10:38:14      80   30000
2017-07-25T10:38:26      92   35000
2017-07-25T10:38:39     105   40000
2017-07-25T10:38:52     117   45000
2017-07-25T10:39:03     129   50000
2017-07-25T10:39:15     141   55000
2017-07-25T10:39:27     152   60000
2017-07-25T10:39:38     164   65000
2017-07-25T10:39:51     177   70000
2017-07-25T10:40:04     189   75000
2017-07-25T10:40:16     202   80000
2017-07-25T10:40:29     214   85000
2017-07-25T10:40:38     224   88786
Started at 2017-07-25T10:36:54, ended at 2017-07-25T10:40:38
Source: A=utf8,D=tom,h=10.0.0.6,p=...,t=test,u=root
Dest:   A=utf8,D=tom,P=3306,h=10.0.0.7,p=...,t=test,u=root
SELECT 88786
INSERT 88786
DELETE 88786
Action         Count       Time        Pct
deleting       88786   122.6957      54.70
inserting      88786    66.5441      29.67
select            19     2.0529       0.92
commit            36     0.4159       0.19
other              0    32.5878      14.53

root@localhost:mysql.sock  10:40:51 [tom]>select * from test;
+-------+------------+------+---------------------+
| id    | name       | age  | createtime          |
+-------+------------+------+---------------------+
| 88787 | hello88787 |   83 | 2017-07-19 17:24:21 |
+-------+------------+------+---------------------+
1 row in set (0.00 sec)

what？居然有一条还在，看下pt-archiver相关代码

6257       $first_sql .= " AND ($col < " . $q->quote_val($val) . ")";

把这个修改一下，让它小于等于就ok了

6257       $first_sql .= " AND ($col <= " . $q->quote_val($val) . ")";

然后重新执行

[root@redis02 ~]# pt-archiver --source h=10.0.0.7,u=root,p=123456,D=tom,t=test,A=utf8 --dest h=10.0.0.6,P=3306,D=tom,t=test --where "1=1" --statistics  --progress 10000 --limit 10000 --txn-size 5000 --no-delete
TIME                ELAPSED   COUNT
2017-07-25T10:58:48       0       0
2017-07-25T10:59:00      12   10000
2017-07-25T10:59:10      22   20000
2017-07-25T10:59:19      30   30000
2017-07-25T10:59:32      44   40000
2017-07-25T10:59:46      58   50000
2017-07-25T11:00:00      72   60000
2017-07-25T11:00:12      84   70000
2017-07-25T11:00:24      96   80000
2017-07-25T11:00:36     108   88786
Started at 2017-07-25T10:58:48, ended at 2017-07-25T11:00:36
Source: A=utf8,D=tom,h=10.0.0.7,p=...,t=test,u=root
Dest:   A=utf8,D=tom,P=3306,h=10.0.0.6,p=...,t=test,u=root
SELECT 88786
INSERT 88786
DELETE 0
Action         Count       Time        Pct
inserting      88786    88.3225      81.45
select            10     0.4881       0.45
commit            36     0.2246       0.21
other              0    19.4023      17.89

ok ，至此问题完美解决