背景
- 歸檔的表在源庫和目標庫都要存在
- 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 [email protected] 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 [email protected] 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 [email protected] 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 [email protected] 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
- 操作的相關代碼可見是在目標庫完成commit 操作后，源庫才進行commit操作的
- 當事務中操作的資料量很大時，源庫delete的commit操作耗時也會比較長，pt-archiver發生例外終止后（源庫的commit還沒完成，delete操作會回滾），會出現目標庫已存在資料，源庫還未洗掉資料不一致的情況
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 }
結論
- 在pt-archiver歸檔非commit期間，pt-archiver例外終止，源庫和目標庫都會rollback，不會出現不一致情況
- 在commit的時刻pt-archiver例外終止，可能出現不一致情況：目標庫已經insert ，源庫還沒有delete的情況
- pt-archiver例外終止后（沒按時歸檔完，手動kill pt行程等），需手動校驗目標庫和源庫的主鍵情況，否則再次歸檔會出現主鍵沖突的錯誤
pt-archiver歸檔工具的使用詳解
pt-archiver
pt-archiver資料歸檔
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/125654.html
標籤：MySQL
下一篇：MySQL索引