版本
pt-archiver --version
pt-archiver 3.0.12
select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.12 |
+-----------+
是否会出现不一致情况
- 源库已经delete，目标库还没有insert
- 目标库已经insert ，源库还没有delete
--bulk-insert
采用load data infile的方式，相比一行一行的插入，通过为每批数据创建临时文件，先行写入数据到临时文件，当一批数据获取完毕后，再进行导入操作，加速了目标库插入的速度--bulk-delete
批量删除，一批数据行用一个delete语句完成
生成100000条记录
sysbench /usr/local/share/^csbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
源库
192.168.137.133:test_archiver
目标库
192.168.137.1:test_archiver
开启gerneral日志
set global general_log=on;
每5000条记录进行一次commit，每次取10000 条记录进行处理
nohup pt-archiver --source h=127.0.0.1,u=admin,p=admin,p=8013,d=test_archiver,t=sbtest1 --dest h=192.168.137.1,u=admin,p=admin,p=3306,d=test_archiver --progress 1000 --where "id<100000" --statistics --limit 10000 --sleep 10 --no-check-charset --txn-size 5000 --bulk-delete --bulk-insert &
中途kill掉 pt-archiver归档进程，源库和目标库没有出现不一致的情况
ps -ef | grep pt-archiver | awk '{print $2}' | xargs kill -9
目标库
select id from sbtest1 order by id desc limit 1; +-------+ | id | +-------+ | 10000 | +-------+ 1 row in set (0.00 sec)
源库
select id from sbtest1 order by id limit 1; +-------+ | id | +-------+ | 10001 | +-------+ 1 row in set (0.00 sec)
源库执行语句
2019-08-21t07:02:58.600832z 56 connect admin@127.0.0.1 on test_archiver using tcp/ip 2019-08-21t07:02:58.601186z 56 query set autocommit=0 ... 2019-08-21t07:02:58.966036z 56 query select max(`id`) from `test_archiver`.`sbtest1` 2019-08-21t07:02:58.967807z 56 query select concat(@@hostname, @@port) 2019-08-21t07:02:58.989394z 56 query select /*!40001 sql_no_cache */ `id`,`k`,`c`,`pad` from `test_archiver`.`sbtest1` force index(`primary`) where (id<100000) and (`id` < '100000') order by `id` limit 10000 ... 2019-08-21t07:02:59.275620z 56 query commit ... 019-08-21t07:02:59.532682z 56 query commit 2019-08-21t07:02:59.834194z 56 query select 'pt-archiver keepalive' 2019-08-21t07:02:59.834835z 56 query delete from `test_archiver`.`sbtest1` where (((`id` >= '1'))) and (((`id` <= '10000'))) and (id<100000) limit 10000 2019-08-21t07:03:09.958289z 56 query select /*!40001 sql_no_cache */ `id`,`k`,`c`,`pad` from `test_archiver`.`sbtest1` force index(`primary`) where (id<100000) and (`id` < '100000') and ((`id` >= '10000')) order by `id` limit 10000 ... 2019-08-21t07:03:10.215958z 56 query commit ... 2019-08-21t07:03:10.670937z 56 query commit 2019-08-21t07:03:10.904398z 56 query select 'pt-archiver keepalive' 2019-08-21t07:03:10.904715z 56 query delete from `test_archiver`.`sbtest1` where (((`id` >= '10001'))) and (((`id` <= '20000'))) and (id<100000) limit 10000 ====》( 该语句由于没有commit 语句会rollback )
目标库执行语句
2019-08-21t07:03:00.317343z 33 connect admin@192.168.137.133 on test_archiver using tcp/ip 2019-08-21t07:03:00.338390z 33 query set autocommit=0 ... 2019-08-21t07:03:00.633938z 33 query select concat(@@hostname, @@port) 2019-08-21t07:03:00.920655z 33 query commit 2019-08-21t07:03:01.177267z 33 query commit 2019-08-21t07:03:01.199046z 33 query load data local infile '/tmp/jaguzzfjsept-archiver' into table `test_archiver`.`sbtest1`(`id`,`k`,`c`,`pad`) （插入了 1=<id <=10000的记录） 2019-08-21t07:03:11.850618z 33 query commit 2019-08-21t07:03:12.315829z 33 query commit 2019-08-21t07:03:12.337323z 33 query load data local infile '/tmp/gq2ybc3kczpt-archiver' into table `test_archiver`.`sbtest1`(`id`,`k`,`c`,`pad`) ====》( 该语句由于没有commit 该语句会rollback ，并在 机器/tmp 目录下留下临时文件) ll /tmp/gq2ybc3kczpt-archiver -rw------- 1 root root 1920000 aug 21 15:03 /tmp/gq2ybc3kczpt-archiver
- 从日志可见，源库的delete 操作的commit时间（07:03:10.215958z） 是在目标库insert操作的commit时间（07:03:11.850618z）之前，这可能出现归档时源库已delete，目标库还没有insert的情况
- 这次源库和目标库在不同的实例上，不同的实例时钟会出现不一致 影响general_log中commit出现的时间
源库
192.168.137.133:test_archiver
目标库
192.168.137.133:test_archiver2
删除测试数据重新生成100000 条记录
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql cleanup
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
每100000条记录 进行commit一次，每次取100000条记录进行处理
pt-archiver --source h=127.0.0.1,u=admin,p=admin,p=8013,d=test_archiver,t=sbtest1 --dest h=127.0.0.1,u=admin,p=admin,p=8013,d=test_archiver2 --progress 1000 --where "id<100000" --statistics --sleep 10 --limit 100000 --no-check-charset --txn-size 100000 --bulk-delete --bulk-insert
源库和目标库执行语句
2019-08-22t01:50:35.672490z 9 connect admin@127.0.0.1 on test_archiver using tcp/ip 2019-08-22t01:50:35.673125z 9 query set autocommit=0 ... 2019-08-22t01:50:35.685987z 10 connect admin@127.0.0.1 on test_archiver2 using tcp/ip 2019-08-22t01:50:35.686278z 10 query set autocommit=0 ... 2019-08-22t01:50:35.708866z 9 query select /*!40001 sql_no_cache */ `id`,`k`,`c`,`pad` from `test_archiver`.`sbtest1` force index(`primary`) where (id<100000) and (`id` < '100000') order by `id` limit 100000 ... 2019-08-22t01:50:40.242371z 10 query load data local infile '/tmp/x5w2uempgdpt-archiver' into table `test_archiver2`.`sbtest1`(`id`,`k`,`c`,`pad`) 2019-08-22t01:50:43.692914z 9 query select 'pt-archiver keepalive' 2019-08-22t01:50:43.693411z 9 query delete from `test_archiver`.`sbtest1` where (((`id` >= '1'))) and (((`id` <= '99999'))) and (id<100000) limit 100000 2019-08-22t01:50:58.603351z 9 query select /*!40001 sql_no_cache */ `id`,`k`,`c`,`pad` from `test_archiver`.`sbtest1` force index(`primary`) where (id<100000) and (`id` < '100000') and ((`id` >= '99999')) order by `id` limit 100000 2019-08-22t01:50:58.606390z 10 query commit 2019-08-22t01:50:58.717251z 9 query commit 2019-08-22t01:50:58.780614z 10 quit 2019-08-22t01:50:58.781480z 9 quit
- 从general日志看起来，目标库的批量插入是在源库的批量删除之前，目标库insert 操作的commit（01:50:58.606390z） 也是在源库delete 操作的commit（01:50:58.717251z）之前
- ***在目标库的commit执行后0.11s 期间，pt-archiver发生异常终止(这概率是很小的#_#), 源库的commit没有执行，delete操作就会回滚，出现源库的数据和目标库的数据不一致的问题***
注意
mysql8.0 执行load data infile 命令除了设置secure_file_priv 外，还需要在[client] 和[mysqld] 中设置local-infile=1，不然会出现错误
dbd::mysql::st execute failed: the used command is not allowed with this mysql version
pt-archiver commit
我要评论 7068 if ( $dst ) {
7069 trace('commit', sub {
7070 $dst->{dbh}->commit;
7071 });
7072 }
7073 trace('commit', sub {
7074 $src->{dbh}->commit;
7075 });
7076 $txn_cnt = 0;
7077 }
7078 }
如对本文有疑问， 点击进行留言回复！！
网友评论