背景
- 归档的表在源库和目标库都要存在
- pt-archiver归档表的场景有:不删原表数据,非批量插入目标库;不删原表数据,批量插入目标库;非批量删除原表数据,非批量插入目标库;批量删除原表数据,批量插入目标库
版本
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, @@po