pt table sync Synchronize MySQL table data efficiently. pt table sync synchronizes data efficiently between MySQL tables. pt table sync 是Percona Toolk ...
pt-table-sync - Synchronize MySQL table data efficiently.
pt-table-sync synchronizes data efficiently between MySQL tables.
pt-table-sync是Percona-Toolkit工具集中的一個組件，主要用於表數據的高效同步。
因為涉及到數據的修改，所以為了安全，建議在工具修改數據前先進行備份。在主從環境下，它所做的變更都是基於主庫，而不是直接修改從庫，因為這是保持主從複製數據一致性最佳方式。一般情況下出現主從數據不一致都是由於從庫發生了變化，因此，工具進行同步數據時並不會影響主庫當前的數據，而是影響從庫的數據以達到一致性。
pt-table-sync工具可以用來修複由另一個工具pt-table-checksum檢查校驗出主從不一致的表數據進行修複。關於工具pt-table-checksum的使用可以參考我另一篇博文：Percona-Toolkit 之 pt-table-checksum 總結。
本文是關於之前有關pt-table-checksum工具使用的學習筆記進行重新整理，使用最新版本的工具同時也進行原理知識的梳理。
關於獲取和安裝Percona-Toolkit工具集可以參考我另一篇博文：Percona-Toolkit 之 pt-online-schema-change 總結中的安裝部分。
基本說明
pt-table-sync [OPTIONS] DSN [DSN]
常用選項(OPTIONS)
--ask-pass
命令行提示密碼輸入，保護密碼安全，前提需安裝模塊perl-TermReadKey。
--[no]bin-log
預設值：yes
指定同步操作記錄二進位日誌，相當於執行SET SQL_LOG_BIN=1。如果指定'--no-bin-log'，則對應執行SET SQL_LOG_BIN=0。
--channel
指定當主從複製環境是多源複製時需要進行同步哪個主庫的數據，適用於多源複製中多個主庫對應一個從庫的情形。
--charset，-A
指定連接字元集。
--[no]check-child-tables
預設值：yes
指定檢查當同步指定選項'--execute'時是否會對同步的表的子表(如有存在)有影響。當指定選項'--replace'、'--replicate'或'--sync-to-master'時，同步使用的REPLACE語句，如果被同步的表存在子表，並且子表有'ON DELETE CASCADE'、'ON UPDATE CASCADE'或'ON UPDATE SET NULL'選項時，工具會列印報錯信息並跳過該表的同步，因為REPLACE語句會先進行DELETE然後再進行INSERT操作，這會導致子表刪除數據。
可以指定選項'--no-check-child-tables'不進行檢查，如果同時不影響子表還需要指定選項'--no-foreign-key-checks'，這樣被同步表的操作不會級聯影響到它的子表。
該選項對應的檢查只當指定選項'--execute'和選項('--replace'、'--replicate'和'--sync-to-master')其中之一同時使用時才會進行。指定選項'--print'並不會進行檢查。
--[no]check-master
預設值：yes
指定當選項'--sync-to-master'使用時，嘗試驗證工具連接到的主庫是否是真正的主庫。
--[no]check-slave
預設值：yes
指定檢查目標伺服器是否是從庫所在伺服器。
如果目標伺服器是從庫，則對其進行變更是不安全的，但某些情況卻必須這麼做，比如當主庫需要同步的表上沒有唯一索引時，指定選項'--replace'是不會進行工作的，所以在這種情況下無法對主庫進行變更。預設情況下如果需要對從庫上進行變更，則工具會有提示，可以指定選項'--no-check-slave'禁止檢查。
--[no]check-triggers
預設值：yes
指定檢查目標表是否未定義觸發器。
--chunk-index
指定使用哪個索引對錶進行chunk分塊操作。預設情況下會選擇最優的索引，工具會在SQL語句中添加FORCE INDEX子句。
--chunk-size
預設值：1000
指定表分塊的chunk大小，每個chunk對應的表行數，也可以是數據塊大小，當指定大小時允許的尾碼單位為k、M、G。
--chunk-column
指定根據表中欄位對錶進行chunk操作。
--host，-h
指定連接的資料庫IP地址。
--port，-P
指定連接的資料庫Port埠。
--user，-u
指定連接的資料庫用戶。
--password，-p
指定連接的資料庫用戶密碼。
--socket，-S
指定使用SOCKET文件連接。
--databases，-d
指定需要進行同步的資料庫，如有多個則用','(逗號)隔開。
--tables，-t
指定僅需要進行同步的表，如有多個則用','(逗號)隔開。表名稱可以使用資料庫名加以限定。
--columns，-c
指定進行比較的表欄位，如有多個則用','(逗號)隔開。
--where
通過where語句條件限製表的同步內容。
--dry-run
分析、選擇同步的演算法，並列印信息和退出。
意味著指定選項'--verbose'可以得出工具分析的結果。分析結果的輸出格式與工具實際執行時的輸出一致，但是並不會有數據被影響。
--engines，-e
指定僅同步對應存儲引擎的表。
--execute
指定工具執行同步操作使表數據達成一致狀態。
工具使用此選項意味著將同步指定出現數據不一致的表，因此表的數據將被更改，除非指定了選項'--verbose'，否則表數據的更改將以靜默的方式進行。
--explain-hosts
指定列印工具的連接信息和選項信息並退出執行。
--[no]foreign-key-checks
預設值：yes
指定進行外鍵約束檢查，相當於執行SET FOREIGN_KEY_CHECKS=1。如果指定'--on-foreign-key-checks'，則對應執行SET FOREIGN_KEY_CHECKS=0。
--[no]unique-checks
預設值：yes
指定唯一鍵約束檢查，相當於執行SET UNIQUE_CHECKS=1。如果指定'--on-unique-check'，則對就執行SET UNIQUE_CHECKS=0。
--function
預設值：CRC32
指定校驗操作使用的哈希函數。可選函數有SHA1、MD5等。
--ignore-columns
指定需要忽略比較的欄位，如有多個則用','(逗號)隔開。
--ignore-databases
指定需要忽略比較的資料庫，如有多個則用','(逗號)隔開，系統資料庫information_schema和performance_schema預設被忽略。
--ignore-engines
預設值：FEDERATED，MRG_MyISAM
指定需要忽略同步的存儲引擎類型的表，如有多個則用','(逗號)隔開。
--ignore-tables
指定需要忽略同步的表，如有多個則用','(逗號)隔開。表名稱可以使用資料庫名加以限定。
--ignore-tables-regex
指定採用正則表達式匹配忽略同步的表。
--[no]index-hint
預設值：yes
指定為查詢表chunk對應的行添加索引提示(FORCE/USE INDEX)。
--lock
指定哪個過程中進行鎖表(LOCK TABLES)操作，主要有如下取值：
'
VALUE MEANING
===== ===========================================================================
0 永遠不進行鎖表操作；
1 每個同步周期進行鎖表操作，例如鎖定每次同步chunk對就的表行，這是最細粒度的鎖定級別；
2 在表執行操作的時候進行鎖表操作；
3 為連接的每個DSN連接的伺服器進行鎖表操作，指定語句FLUSH TABLES WITH READ LOCK。
'
當指定選項'--replicate'或'--sync-to-master'時，從庫的表是不會被鎖定的。如果指定了選項'--wait'，則主庫對應的表被鎖定，工具暫定執行操作直到從庫追上主庫才繼續執行。
如果指定選項'--transaction'，則鎖表操作(LOCK TABLES)不會執行，取代的方式是通過事務的開始和提交來進行鎖定操作，例外情況是'--lock=3'，如果指定選項'--no-transaction'，則鎖表操作(LOCK TABLES)適應所有'--lock'取值情況。
--lock-and-rename
指定鎖定源表和目標表，執行同步操作，然後進行表名交換。這類似於一次輕量型的ALTER TABLE操作，它需要兩個DSN來連接並假設都在同一伺服器上，選項使用的是LOCK TABLES語句對錶進行鎖定。
--print
指定列印工具需要執行哪些查詢語句來同步表，解決數據不一致，只是列印輸出，並不會真正執行。
--recursion-method
預設值：processlist，hosts
指定獲取從庫的方式。
'
METHOD USES
=========== =============================================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
none Do not find slaves
==========================================================
'
processlist：通過SHOW PROCESSLIST方式找到slave，為預設方式，當SHOW SLAVE HOSTS不可用時。一旦實例運行在非3306埠上時，hosts方式就會變為預設方式；
hosts：通過SHOW SLAVE HOSTS方式找到slave，hosts方式要求從庫配置'--report_host'和'--report_port'這兩個參數。
--replace
指定所有的INSERT和UPDATE語句改寫為REPLACE語句。如果數據有違反唯一性約束，則工具會自動進行改寫。
--replicate
指定參照該選項中列出的表進行表同步操作。
工具將在該選項指定的表中去查詢數據不一致表的信息併進行同步操作，這個選項跟工具pt-table-checksum當中的同名選項是相同意義的。該選項會自動設置選項'--wait=60'並確保在主庫進行變更。
如果指定選項'--sync-to-master'，則工具會假設指定的連接是從庫，並會尋找主庫連接進行表數據同步操作，如果指定的連接不是從庫，工具將使用選項'--recursion-method'中查找從庫方法進行查找操作，然後再找到需要進行同步操作的表。
--set-vars
預設：
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
運行檢查時指定參數值，如有多個用','(逗號)分隔。如'--set-vars=wait_timeout=5000'。
--sync-to-master
指定將DSN連接信息確認為從庫，並同步信息到主庫。
該選項將指定的伺服器當作是從庫，並且檢查從庫的主庫，連接主庫。將主庫作為數據同步的源端，從庫作為數據同步的目標端進行同步。選項會預設設置選項'--wait=60'和'--lock=1'，並且設置選項'--no-transaction'。
--timeout-ok
指定當選項'--wait'導致工具執行失敗時跳過失敗繼續執行。
如果指定了選項'--wait'，但是從庫在指定時間內還是沒能追上主庫且依然存在主從延遲，則工具將中止操作並退出。指定選項'--timeout-ok'則會繼續執行操作。
--[no]transaction
指定工具操作使用事務代替LOCK TABLES語句進行鎖表。
事務開始和提交的鎖粒度由選項'--lock'控制，這是預設的狀態，但是選項'--lock'預設是關閉的，所以並不會生效。大多數選項如果指定鎖定則預設也會禁止使用事務方式，如果想使用基於事務的鎖定(如通過語句LOCK IN SHARE MODE或FOR UPDATE)，則需要顯式指定選項'--transaction'。如果選項'--transaction'沒有顯式指定，則工具根據表類型來決定是基於事務鎖定還是使用語句LOCK TABLES進行鎖定，預設情況是InnoDB表使用事務，而其他類型表使用LOCK TABLES。如果指定了選項'--no-transaction'，則工具在執行過程中都不會開啟事務(即使操作的InnoDB引擎的表)，表的鎖定由選項'--lock'控制。
無論是顯式指定該選項或者是隱式觸發該選項，則都會開啟隔離級別為REPEATABLE READ的一致性快照事務(WITH CONSISTENT SNAPSHOT)。
--verbose，-v
指定列印更詳細的操作信息。
--version
顯示工具的版本並退出。
--[no]version-check
預設值：yes
檢查Percona Toolkit、MySQL和其他程式的最新版本。
--wait，-w
指定存在主從複製延遲時從庫可以等待多長時間追上主庫，如果超過時間依然存在延遲就中止退出(除非指定選項'--timeout-ok')。DSN選項(DSN)
可以使用DSN方式來連接資料庫，DSN選項為key=value方式，在等號的兩側不能有空格出現，並且區分大小寫，多個選項之前以','(逗號)隔開，主要選項如下：
- A
指定字元集 - D
指定需要同步資料庫 - t
指定需要同步的表 - h
指定要連接的HOST - P
指定要連接的PORT - S
指定連接所使用的SOCKET文件(Unix systems) - u
指定連接的用戶名 - p
指定連接的用戶名密碼
示例：
h=192.168.58.3,P=3306,D=employees,t=employees
--replicate選項說明
pt-table-sync工具使用起來有點複雜，它可以通過很多不同方式起作用，其中選項--replicate的使用對於工具至關重要，以下關於該選項的使用作進一步的說明。
該選項的使用邏輯如下：
- 如果不指定
--replicate選項，DSN選項中有涉及到表選項t，則只同步指定的表：- 如果DSN只有1個主機信息，並指定選項
--sync-to-master：- 如果DSN代表的是從庫，工具也會連接它的主庫並且同步；
- 如果DSN代表的是主庫，工具會報錯找不到主庫。
- 如果DSN里大於1個主機信息：
- 第1個DSN主機是源端資料庫(並不區分主庫和從庫)，按順序向之後DSN主機同步。如果第一個是DSN主機是從庫的話會將從庫的數據同步到主庫；
- 如果第1個DSN主機是主庫，確保同步的表有唯一索引以便在主庫執行REPLACE變更操作，並指定選項
--no-check-slave。
- 如果DSN只有1個主機信息，並指定選項
- 如果指定
--replicate選項，證明已經存在保存數據差異結果的表(可以先使用工具pt-table-checksum進行校驗)：- 指定
--sync-to-master選項：- 當這兩個選項一起使用時，只允許有1個DSN主機，否則工具會報錯退出。DSN代表的是從庫。工具會連接它的主庫，找出差異數據併進行同步修複。
- 不指定
--sync-to-master選項：- DSN代表的是主庫。工具會找出所有從庫並連接，找出差異數據併進行同步修複。
- 指定
- 其他情況，不指定選項
--replicate和--sync-to-master：- 有多個DSN主機：
- 通過
--databases或者--ignore-databases等過濾選項找出指定資料庫所有表數據的差異，以DSN第1個主機為主，並同步差異到DSN其餘所有主機。
- 通過
- 有多個DSN主機：
總結：
- 如果DSN只有1個主機信息，則必須指定選項
--sync-to-master或者--replicate其中之一，否則報錯； - 如果有指定選項
--replicate和--sync-to-master，或者兩者其中之一，參考以上使用邏輯； - 如果DSN不只1個主機信息，並且都沒有指定選項
--replicate和--sync-to-master，則以第1個主機為主，同時指定選項--no-check-slave，在其餘主機上同步差異數據。
複製安全
對於主從資料庫的複製環境來說，當出現數據不一致的情形，有時候需要判斷哪一個庫的數據才是最終確定的數據。但大部分情況都希望在主庫執行想應的數據同步變更，這樣可以同時將變更同步到從庫，既修複了從庫數據的不一致性，也保證了變更在從庫上得到執行。這樣的情況只有在主庫上的表有唯一鍵(主鍵)時並執行REPLACE語句時才會生效，否則REPLACE語句就像普通的INSERT語句，會導致主庫插入的數據重覆。
如果表上具有唯一鍵(主鍵)時，對於主從複製架構來說，最理想的做法是指定選項--replicate或--sync-to-master將同步需要執行的變更語句放在主庫上執行，並將變更的操作通過主從複製傳遞給從庫來執行。如果表上沒有唯一鍵，則變更只好在從庫進行，但前提需指定選項--no-check-slave。
對於是主主複製架構來說，變更操作必須在目標端資料庫進行，在變更的同時需要指定選項--no-bin-log，即變更的操作不寫入binlog中，否則變更操作會反向複製到另一臺主庫中執行變更操作，造成數據的不一致。
使用限制
當pt-table-sync工具指定選項--replicate和--sync-to-master進行數據同步操作時會使用基於語句(binlog_format=STATEMENT)的方式進行，所以需確保執行操作的用戶擁有SUPER許可權。
用法示例
環境與數據準備
MySQL：5.7.24
MASTER：192.168.58.3:3306
SLAVE：192.168.58.5:3306
本文基於MySQL官方示例資料庫employee：Example Databases進行測試。
創建測試表employees_ptsync：
-- 表employees_ptsync信息
mysql [email protected]:employees> create table employees_ptsync as select * from employees;
Query OK, 300024 rows affected
Time: 2.244s
mysql [email protected]:employees> show create table employees_ptsync;
+------------------+--------------------------------------+
| Table | Create Table |
+------------------+--------------------------------------+
| employees_ptsync | CREATE TABLE `employees_ptsync` ( |
| | `emp_no` int(11) NOT NULL, |
| | `birth_date` date NOT NULL, |
| | `first_name` varchar(14) NOT NULL, |
| | `last_name` varchar(16) NOT NULL, |
| | `gender` enum('M','F') NOT NULL, |
| | `hire_date` date NOT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+--------------------------------------+
1 row in set
Time: 0.025s
-- 從庫更新部分數據，使得主從出現數據不一致
mysql [email protected]:employees> set sql_log_bin = 0;
Query OK, 0 rows affected
Time: 0.002s
mysql [email protected]:employees> update employees_ptsync set first_name = 'Georgi_ptsync' where first_name = 'Georgi';
Query OK, 252 rows affected
Time: 0.248s
mysql [email protected]:employees> set sql_log_bin = 1;
Query OK, 0 rows affected
Time: 0.002s只有1個DSN主機
- 只指定選項
--sync-to-master
因為只有1個DSN主機並且指定了選項--sync-to-master，則DSN主機對應為從庫的連接串，先使用選項--dry-run查看執行信息。
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 192.168.58.5:
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10857. while doing employees.employees_ptsync on 192.168.58.5
# 0 0 0 0 0 14:41:09 14:41:09 1 employees.employees_ptsync因為表employees_ptsync沒有任何索引的信息，以上報錯提示表沒有唯一鍵，先為表添加主鍵並再次執行。
-- 添加主鍵
mysql [email protected]:employees> alter table employees_ptsync add primary key(emp_no);
Query OK, 0 rows affected
Time: 2.537s
-- 再次執行
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 192.168.58.5:
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 0 Chunk 14:42:55 14:42:55 0 employees.employees_ptsync從以上輸出信息可得知並無報錯信息，但因為使用了選項--dry-run，所以工具並不會進行校驗和同步操作。
將選項--dry-run換成選項--execute再次執行。
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 252 0 0 Chunk 14:44:18 14:44:28 2 employees.employees_ptsync可以看出在REPLACE欄位變更了252行記錄，跟之前構建從庫的數據行數一致，可以查詢從庫剛纔表更新數據的情況進行驗證：
-- 查詢之前變更表的數據量
mysql [email protected]:employees> select count(*) from employees_ptsync where first_name = 'Georgi_ptsync';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set
Time: 0.197s
-- 可以再使用pt-table-checksum進行驗證
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
04-09T14:47:09 0 0 300024 0 5 0 0.807 employees.employees_ptsync- 只指定選項
--replicate
如果是只指定選項--replicate，則DSN主機對應的為主庫的連接串，在這之前選項--replicate指定的表有保存之前數據不一致的校驗結果，可以先通過工具pt-table-checksum進行校驗，否則並不會進行同步變更修複。
-- 查詢校驗表percona.checksums信息
mysql [email protected]:employees> select count(*) from percona.checksums;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set
Time: 0.022s
-- 執行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
-- 先使用pt-table-checksum校驗
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
04-09T15:04:04 0 3 300024 0 5 0 0.766 employees.employees_ptsync
-- 再次執行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 1 0 0 Chunk 15:05:51 15:05:52 2 employees.employees_ptsync
# 0 93 0 0 Chunk 15:05:52 15:05:55 2 employees.employees_ptsync
# 0 158 0 0 Chunk 15:05:55 15:06:01 2 employees.employees_ptsync- 同時指定選項
--sync-to-master和--replicate
因為選項--sync-to-master出現，所以DSN主機對應的為從庫的連接串，預設之前還是沒有進行校驗。
-- 執行同步操作
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
-- 還是需要進行pt-table-checksum進行校驗
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
04-09T15:08:00 0 4 300024 0 6 0 0.926 employees.employees_ptsync
-- 再次執行同步操作
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 1 0 0 Chunk 15:10:29 15:10:29 2 employees.employees_ptsync
# 0 28 0 0 Chunk 15:10:29 15:10:30 2 employees.employees_ptsync
# 0 114 0 0 Chunk 15:10:30 15:10:35 2 employees.employees_ptsync
# 0 109 0 0 Chunk 15:10:35 15:10:40 2 employees.employees_ptsync- 不指定選項
--sync-to-master和--replicate
當DSN只有1個主機的時候，必須指定至少其中一個選項，否則工具報錯退出。
# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --transaction --verbose --execute
Usage: pt-table-sync [OPTIONS] DSN [DSN]
Errors in command-line arguments:
* At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified
pt-table-sync synchronizes data efficiently between MySQL tables. For more
details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
for complete documentation.有多個DSN主機
- 只指定選項
--sync-to-master
因為有多個DSN主機，必須確保所列出DSN主機均為從庫，否則工具報錯退出。
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.3:
Can't determine master of A=utf8,P=3306,h=192.168.58.3,p=...,u=admin at /usr/bin/pt-table-sync line 10020, <STDIN> line 1.- 只指定選項
--replicate
如果只指定選項--replicate，也必須之前做過校驗操作。
-- 執行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
-- 還是需要進行pt-table-checksum進行校驗
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
04-09T15:15:46 0 3 300024 0 5 0 0.805 employees.employees_ptsync
-- 再次執行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:
# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 1 0 0 Chunk 15:15:53 15:15:53 2 employees.employees_ptsync
# 0 86 0 0 Chunk 15:15:53 15:15:57 2 employees.employees_ptsync
# 0 165 0 0 Chunk 15:15:57 15:16:03 2 employees.employees_ptsync- 同時指定選項
--sync-to-master和--replicate
當同時指定這兩個選項時，DSN主機只允許有一個，否則工具報錯退出。
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Usage: pt-table-sync [OPTIONS] DSN [DSN]
Errors in command-line arguments:
* --sync-to-master and --replicate require only one DSN
pt-table-sync synchronizes data efficiently between MySQL tables. For more
details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
for complete documentation.- 不指定選項
--sync-to-master和--replicate
因為都不指定這兩個選項，所以DSN主機的順序必須格外註意，最好是先寫主庫再寫從庫，或者根據同步的方向來確定。同時如果需要做同步變更修複的表上沒有唯一鍵(主鍵)，最好都指定選項--no-check-slave直接在從庫進行變更修複。
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
Can't make changes on A=utf8,P=3306,h=192.168.58.5,p=...,u=admin because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /usr/bin/pt-table-sync line 10878. while doing employees.employees_ptsync on 192.168.58.5
# 0 0 0 0 0 15:19:19 15:19:19 1 employees.employees_ptsync
-- 加上選項--no-check-slave
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --no-check-slave --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 252 Chunk 15:20:45 15:20:52 2 employees.employees_ptsync可以看出如果在從庫進行變更修複，執行的是UPDATE操作。
輸出說明
工具同步執行操作的輸出解析如下：
| 欄位名 | 說明 |
|---|---|
| DELETE | 刪除的行數 |
| REPLACE | 替換的行數 |
| INSERT | 插入的行數 |
| UPDATE | 更新的行數 |
| ALGORITHM | 校驗使用的演算法 |
| START | 執行開始的時間 |
| END | 執行結束的時間 |
| EXIT | 退出狀態碼 |
| DATABASE.TABLE | 涉及的資料庫.表 |
關於退出狀態碼的說明如下：
STATUS MEANING
====== ========================================================================================
0 工具沒有執行同步變更操作併成功退出。(Success)
1 工具出現內部錯誤。(Internal error)
2 至少有一張表校驗有不一致併進行同步變更修複。(At least one table differed on the destination)
3 狀態1和狀態2共同出現的情況。(Combination of 1 and 2)註意事項
- 只使用1個DSN主機信息，連接的主庫，加上選項
--replicate，需先進行校驗，與pt-table-checksum工具配合使用； - 只使用1個DSN主機信息，連接的從庫，加上選項
--sync-to-master，無需先進行校驗； - 使用多個DSN主機信息，按照數據同步方向，順序寫好主機信息，根據實際需求加上選項
--no-check-slave； - 推薦使用多個DSN主機信息的方式，同時避免使用選項
--replicate和--sync-to-master，無需先進行校驗，只需考慮數據同步的方向。
工作流程
因為DSN主機提供了不同的方式進行校驗和同步修複方法，所以工作流程分為兩種情況進行分析：
- 只使用1個DSN主機連接從庫，只使用選項
--sync-to-master。- 使用多個DSN主機同時連接主庫和從庫，只使用選項
--replicate，需先使用工具pt-table-checksum校驗。
還是通過general log記錄操作來瞭解。
- 第1種情形：
為了減少general log的篇幅，加上選項--chunk-size=50000。
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --chunk-size=50000 --verbose --execute-- 開啟會話線程167進行初始的一些檢查資料庫參數、負載信息這裡不再細說
167 Connect admin@dbabd1 on using TCP/IP
167 Query set autocommit=0
167 Query /*!40101 SET NAMES "utf8"*/
167 Query SHOW VARIABLES LIKE 'wait\_timeout'
167 Query SET SESSION wait_timeout=10000
167 Query SELECT @@SQL_MODE
167 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
167 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
167 Query SHOW VARIABLES LIKE 'version%'
167 Query SHOW ENGINES
167 Query SHOW VARIABLES LIKE 'innodb_version'
-- 設置binlog_format=STATEMENT
167 Query SELECT @@binlog_format
167 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
-- 設置會話級隔離級別為REPEATABLE READ
167 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
167 Query SHOW MASTER STATUS
-- 查詢當前連接用戶的許可權
167 Query SHOW GRANTS FOR CURRENT_USER()
167 Query SHOW FULL PROCESSLIST
167 Query SHOW VARIABLES LIKE 'port'
167 Query SELECT CONCAT(@@hostname, @@port)
-- 開啟別一個會話線程168持續檢查狀態
168 Connect admin@dbabd1 on using TCP/IP
168 Query set autocommit=0
168 Query /*!40101 SET NAMES "utf8"*/
168 Query SHOW VARIABLES LIKE 'wait\_timeout'
168 Query SET SESSION wait_timeout=10000
168 Query SELECT @@SQL_MODE
168 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
168 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
168 Query SHOW VARIABLES LIKE 'version%'
168 Query SHOW ENGINES
168 Query SHOW VARIABLES LIKE 'innodb_version'
168 Query SELECT @@binlog_format
168 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
168 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- 會話線程167查詢當前資料庫和需要操作表的狀態信息，表結構信息等，記錄開始時間
167 Query SHOW DATABASES
167 Query SHOW /*!50002 FULL*/ TABLES FROM `employees`
167 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
167 Query USE `employees`
167 Query SHOW CREATE TABLE `employees`.`employees_ptsync`
167 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
167 Query SELECT NOW()
-- 查詢需要操作表是否被外鍵約束
167 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
167 Query SET NAMES 'utf8'
-- 確定需要操作表下邊界和上邊界值，依賴主鍵(唯一鍵)
167 Query SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`)
167 Query EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`)
167 Query SELECT CRC32('test-string')
167 Query SELECT CRC32('a')
167 Query SELECT CRC32('a')
-- 開啟執行校驗，並同步進行不一致的同步修複，每執行一次chunk校驗和變更都開啟了一致性快照事務
167 Query USE `employees`
167 Query SET @crc := '', @cnt := 0
167 Query commit
167 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query SELECT /*employees.employees_ptsync:1/7*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` = 0) FOR UPDATE
168 Query SHOW MASTER STATUS
167 Query SET @crc := '', @cnt := 0
167 Query commit
167 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query SELECT /*employees.employees_ptsync:2/7*/ 1 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '91801') FOR UPDATE
168 Query SHOW MASTER STATUS
167 Query SET @crc := '', @cnt := 0
167 Query SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '91801') ORDER BY `emp_no` FOR UPDATE
167 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='10909' LIMIT 1
167 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('10909', '1954-11-11', 'Georgi', 'Atchley', 'M', '1985-04-21') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/
167 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11029' LIMIT 1
167 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11029', '1962-07-12', 'Georgi', 'Itzfeldt', 'M', '1992-12-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/
……省略……
-- 直到最後一個chunk校驗完併進行修複，可以看出在校驗過程中為這些加了行鎖，使用語句FOR UPDATE
167 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query SELECT /*employees.employees_ptsync:7/7*/ 6 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` >= '419001') FOR UPDATE
168 Query SHOW MASTER STATUS
167 Query SET @crc := '', @cnt := 0
167 Query SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` >= '419001') ORDER BY `emp_no` FOR UPDATE
167 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='420266' LIMIT 1
167 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('420266', '1955-03-30', 'Georgi', 'Armand', 'M', '1998-06-13') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/
167 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='420295' LIMIT 1
167 Query commit
-- 記錄結束時間，並退出
167 Query SELECT NOW()
167 Query commit
167 Quit
168 Query commit
168 Quit流程總結：
- 開啟兩個會話連接，一個會話負責校驗同步，一個會話負責持續檢查伺服器狀態信息；
- 連接DSN主機對應的主庫，檢查當前伺服器負載信息，參數設置信息，關閉自動提交功能；
- 設置二進位日誌格式為
STATEMENT，設置會話級別隔離級別為REPEATABLE READ； - 檢查當前連接用戶的許可權，檢查操作表是否被外鍵約束；
- 通過主鍵或唯一鍵(如有)對錶進行chunk上邊界和下邊界的確定，以便更好進行chunk分塊操作；
- 表被分成多個chunk進行校驗和同步修複，chunk大小由選項
--chunk-size控制； - 完成所有的chunk校驗和同步修複，退出。
- 第2種情形
需要先進行pt-table-checksum校驗操作，這裡不再說明。
pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --chunk-size=50000 --verbose --execute-- 開啟會話線程186進行初始的一些檢查資料庫參數、負載信息這裡不再細說
186 Connect admin@dbabd1 on using TCP/IP
186 Query set autocommit=0
186 Query /*!40101 SET NAMES "utf8"*/
186 Query SHOW VARIABLES LIKE 'wait\_timeout'
186 Query SET SESSION wait_timeout=10000
186 Query SELECT @@SQL_MODE
186 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
186 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
186 Query SHOW VARIABLES LIKE 'version%'
186 Query SHOW ENGINES
186 Query SHOW VARIABLES LIKE 'innodb_version'
-- 設置binlog_format=STATEMENT
186 Query SELECT @@binlog_format
186 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
-- 設置會話級隔離級別為REPEATABLE READ
186 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- 開啟別一個會話線程187持續檢查狀態
187 Connect admin@dbabd1 on using TCP/IP
187 Query set autocommit=0
187 Query /*!40101 SET NAMES "utf8"*/
187 Query SHOW VARIABLES LIKE 'wait\_timeout'
187 Query SET SESSION wait_timeout=10000
187 Query SELECT @@SQL_MODE
187 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
187 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
187 Query SHOW VARIABLES LIKE 'version%'
187 Query SHOW ENGINES
187 Query SHOW VARIABLES LIKE 'innodb_version'
187 Query SELECT @@binlog_format
187 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
187 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- 會話線程186查詢選項--replicate指定表確定不一致的信息，包括表chunk信息等
186 Query SELECT @@SERVER_ID
186 Query SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
-- 查詢當前連接用戶的許可權，記錄開始時間，表結構信息等，檢查是否存在外鍵約束
186 Query SHOW GRANTS FOR CURRENT_USER()
186 Query SHOW FULL PROCESSLIST
186 Query SELECT NOW()
186 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
186 Query USE `employees`
186 Query SHOW CREATE TABLE `employees`.`employees_ptsync`
186 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
186 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
186 Query SET NAMES 'utf8'
187 Query SET NAMES 'utf8'
-- 確定首個chunk需要操作表下邊界和上邊界值，依賴主鍵(唯一鍵)
186 Query SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')))
186 Query EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000'))
186 Query SELECT CRC32('test-string')
186 Query SELECT CRC32('a')
186 Query SELECT CRC32('a')
-- 獲取chunk中表行數據進行校驗，並同步進行不一致的同步修複，每執行一次chunk校驗和變更都開啟了一致性快照事務
186 Query USE `employees`
186 Query SET @crc := '', @cnt := 0
186 Query commit
186 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
186 Query SELECT /*employees.employees_ptsync:1/4*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` = 0) AND ((((`emp_no` >= '11001')) AND ((`emp_no` <= '242216')))) FOR UPDATE
187 Query SHOW MASTER STATUS
186 Query SET @crc := '', @cnt := 0
186 Query commit
186 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
186 Query SELECT /*employees.employees_ptsync:2/4*/ 1 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '88199') AND ((((`emp_no` >= '11001')) AND ((`emp_no` <= '242216')))) FOR UPDATE
187 Query SHOW MASTER STATUS
186 Query SET @crc := '', @cnt := 0
186 Query SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '88199') AND (((`emp_no` >= '11001')) AND ((`emp_no` <= '242216'))) ORDER BY `emp_no` FOR UPDATE
186 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11029' LIMIT 1
186 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11029', '1962-07-12', 'Georgi', 'Itzfeldt', 'M', '1992-12-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11430' LIMIT 1
186 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11430', '1957-01-23', 'Georgi', 'Klassen', 'M', '1996-02-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
……省略……
-- 首個chunk執行結束，記錄結束時間
186 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='239542' LIMIT 1
186 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('239542', '1957-06-20', 'Georgi', 'Barriga', 'F', '1994-04-28') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query commit
186 Query SELECT NOW()
-- 確定接下去chunk需要操作表下邊界和上邊界值，依賴主鍵(唯一鍵)
186 Query SELECT NOW()
186 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
186 Query SET NAMES 'utf8'
187 Query SET NAMES 'utf8'
186 Query SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (((`emp_no` >= '242217')) AND ((`emp_no` <= '499999')))
186 Query EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE ((`emp_no` >= '242217')) AND ((`emp_no` <= '499999'))
186 Query SELECT CRC32('test-string')
186 Query SELECT CRC32('a')
186 Query SELECT CRC32('a')
……省略……
-- 完成最後chunk的校驗併進行不一致的同步修複
186 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='498809' LIMIT 1
186 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('498809', '1961-10-24', 'Georgi', 'Ferriere', 'M', '1993-02-21') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='499814' LIMIT 1
186 Query REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('499814', '1960-12-28', 'Georgi', 'Wielonsky', 'M', '1989-01-31') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query commit
-- 記錄結束時間，並退出
186 Query SELECT NOW()
186 Query commit
186 Quit
187 Query commit
187 Quit 工作流程與第1種情形類似，只不過在處理chunk時使用的是選項--replicate指定表中的記錄情況，而不是實時進行chunk處理，其他操作流程與第1種情形一致。
總結
pt-table-sync工具作為一款高效的數據同步工具，通常與pt-table-checksum工具一起使用，但是它涉及到數據的更改，在使用時最好先進行測試之後再對線上伺服器進行操作，雖然對生產伺服器的性能影響服務較小，但是在進行校驗分析的同時會對操作的表行執行FOR UPDATE語句進行鎖定，所以儘量選擇在業務低峰期進行操作，同時避免在高併發場景下進行操作數據以免造成阻塞。
本文只說明瞭pt-table-sync工具的單向同步方式，其實它也支持雙向同步，只是有許多限制條件並且也處在一個測試功能的階段，更多關於pt-table-sync的說明可以參考官方說明：pt-table-sync
參考
https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html
☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆
