歸檔，在 MySQL 中，是一個相對高頻的操作。
它通常涉及以下兩個動作：
- 遷移。將資料從業務例項遷移到歸檔例項。
- 刪除。從業務例項中刪除已遷移的資料。
在處理類似需求時，都是開發童鞋提單給 DBA，由 DBA 來處理。
於是，很多開發童鞋就好奇，DBA 都是怎麼執行歸檔操作的？歸檔條件沒有索引會鎖表嗎？安全嗎，會不會資料刪了，卻又沒歸檔成功？
針對這些疑問，下面介紹 MySQL 中的資料歸檔神器 - pt-archiver。
本文主要包括以下幾部分：
- 什麼是 pt-archiver
- 安裝
- 簡單入門
- 實現原理
- 批量歸檔
- 不同歸檔引數之間的速度對比
- 常見用法
- 如何避免主從延遲
- 常用引數
什麼是 pt-archiver
pt-archiver 是 Percona Toolkit 中的一個工具。
Percona Toolkit 是 Percona 公司提供的一個 MySQL 工具包。工具包裡提供了很多實用的 MySQL 管理工具。
譬如，我們常用的表結構變更工具 pt-online-schema-change ，主從資料一致性校驗工具 pt-table-checksum 。
毫不誇張地說，熟練使用 Percona Toolkit 是 MySQL DBA 必備的技能之一。
安裝
Percona Toolkit 下載地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
官方針對多個系統提供了現成的軟體包。
我常用的是 Linux - Generic 二進位制包。
下面以 Linux - Generic 版本為例，看看它的安裝方法。
# cd /usr/local/ # wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz --no-check-certificate # tar xvf percona-toolkit-3.3.1_x86_64.tar.gz # cd percona-toolkit-3.3.1 # yum install perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-Digest-MD5 # perl Makefile.PL # make # make install
簡單入門
首先，我們看一個簡單的歸檔 Demo。
測試資料
mysql> show create table employees.departments\G *************************** 1. row *************************** Table: departments Create Table: CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from employees.departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 9 rows in set (0.00 sec)
下面，我們將 employees.departments 表的資料從 192.168.244.10 歸檔到 192.168.244.128。
具體命令如下：
pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --dest h=192.168.244.128,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1"
命令列中指定了三個引數。
-
--source：源庫（業務例項）的 DSN。
DSN 在 Percona Toolkit 中比較常見，可理解為目標例項相關資訊的縮寫。
支援的縮寫及含義如下：
縮寫 含義 === ============================================= A 預設的字符集 D 庫名 F 只從給定檔案中讀取配置資訊，類似於MySQL中的--defaults-file P 埠 S 用於連線的socket檔案 h 主機名 p 密碼 t 表名 u 使用者名稱
-
--dest：目標庫（歸檔例項）的 DSN。
-
--where：歸檔條件。"1=1"代表歸檔全表。
實現原理
下面結合 General log 的輸出看看 pt-archiver 的實現原理。
源庫日誌
2022-03-06T10:58:20.612857+08:00 10 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 1 2022-03-06T10:58:20.613451+08:00 10 Query DELETE FROM `employees`.`departments` WHERE (`dept_no` = 'd001') 2022-03-06T10:58:20.620327+08:00 10 Query commit 2022-03-06T10:58:20.628409+08:00 10 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`dept_no` >= 'd001')) ORDER BY `dept_no` LIMIT 1 2022-03-06T10:58:20.629279+08:00 10 Query DELETE FROM `employees`.`departments` WHERE (`dept_no` = 'd002') 2022-03-06T10:58:20.636154+08:00 10 Query commit ...
目標庫日誌
2022-03-06T10:58:20.613144+08:00 18 Query INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES ('d001','Marketing') 2022-03-06T10:58:20.613813+08:00 18 Query commit 2022-03-06T10:58:20.628843+08:00 18 Query INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES ('d002','Finance') 2022-03-06T10:58:20.629784+08:00 18 Query commit ...
結合源庫和目標庫的日誌，可以看到，
-
pt-archiver 首先會從源庫查詢一條記錄，然後再將該記錄插入到目標庫中。
目標庫插入成功，才會從源庫中刪除這條記錄。
這樣就能確保資料在刪除之前，一定是歸檔成功的。
-
仔細觀察這幾個操作的執行時間，其先後順序如下。
（1）源庫查詢記錄。
（2）目標庫插入記錄。
（3）源庫刪除記錄。
（4）目標庫 COMMIT。
（5）源庫 COMMIT。
這種實現借鑑了分散式事務中的兩階段提交演算法。
-
--where 引數中的 "1=1" 會傳遞到 SELECT 操作中。
"1=1" 代表歸檔全表，也可指定其它條件，如我們常用的時間。
-
每次查詢都是使用主鍵索引，這樣即使歸檔條件中沒有索引，也不會產生全表掃描。
-
每次刪除都是基於主鍵，這樣可避免歸檔條件沒有索引導致全表被鎖的風險。
批量歸檔
如果使用 Demo 中的引數進行歸檔，在資料量比較大的情況下，效率會非常低，畢竟 COMMIT 是一個昂貴的操作。
所以線上上，我們通常都會進行批量操作。
具體命令如下：
pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --dest h=192.168.244.128,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --commit-each --bulk-insert
相對於之前的歸檔命令，這條命令額外指定了四個引數，其中，
-
--bulk-delete：批量刪除。
-
--limit：每批歸檔的記錄數。
-
--commit-each：對於每一批記錄，只會 COMMIT 一次。
-
--bulk-insert：歸檔資料以 LOAD DATA INFILE 的方式匯入到歸檔庫中。
看看上述命令對應的 General log 。
源庫
2022-03-06T12:13:56.117984+08:00 53 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 1000 ... 2022-03-06T12:13:56.125129+08:00 53 Query DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd001'))) AND (((`dept_no` <= 'd009'))) AND (1=1) LIMIT 1000 2022-03-06T12:13:56.130055+08:00 53 Query commit
目標庫
2022-03-06T12:13:56.124596+08:00 51 Query LOAD DATA LOCAL INFILE '/tmp/hitKctpQTipt-archiver' INTO TABLE `employees`.`departments`(`dept_no`,`dept_name`) 2022-03-06T12:13:56.125616+08:00 51 Query commit：
注意：
-
如果要執行 LOAD DATA LOCAL INFILE 操作，需將目標庫的 local_infile 引數設定為 ON。
-
如果不指定 --bulk-insert 且沒指定 --commit-each，則目標庫的插入還是會像 Demo 中顯示的那樣，逐行提交。
-
如果不指定 --commit-each，即使表中的 9 條記錄是通過一條 DELETE 命令刪除的，但因為涉及了 9 條記錄，pt-archiver 會執行 COMMIT 操作 9 次。目標庫同樣如此。
-
在使用 --bulk-insert 歸檔時要注意，如果匯入的過程中出現問題，譬如主鍵衝突，pt-archiver 是不會提示任何錯誤的。
不同歸檔引數之間的速度對比
下表是歸檔 20w 資料，不同引數之間的執行時間對比。
| 歸檔引數 | 執行時間（s） |
|---|---|
| 不指定任何批量相關引數 | 850.040 |
| --bulk-delete --limit 1000 | 422.352 |
| --bulk-delete --limit 1000 --commit-each | 46.646 |
| --bulk-delete --limit 5000 --commit-each | 46.111 |
| --bulk-delete --limit 1000 --commit-each --bulk-insert | 7.650 |
| --bulk-delete --limit 5000 --commit-each --bulk-insert | 6.540 |
| --bulk-delete --limit 1000 --bulk-insert | 47.273 |
通過表格中的資料，我們可以得出以下幾點：
-
第一種方式是最慢的。
這種情況下，無論是源庫還是歸檔庫，都是逐行操作並提交的。
-
只指定 --bulk-delete --limit 1000 依然很慢。
這種情況下，源庫是批量刪除，但 COMMIT 次數並沒有減少。
歸檔庫依然是逐行插入並提交的。
-
--bulk-delete --limit 1000 --commit-each
相當於第二種歸檔方式，源庫和目標庫都是批量提交的。
-
--limit 1000 和 --limit 5000 歸檔效能相差不大。
-
--bulk-delete --limit 1000 --bulk-insert 與 --bulk-delete --limit 1000 --commit-each --bulk-insert 相比，沒有設定 --commit-each。
雖然都是批量操作，但前者會執行 COMMIT 操作 1000 次。
由此來看，空事務並不是沒有代價的。
其它常見用法
（1）刪除資料
刪除資料是 pt-archiver 另外一個常見的使用場景。
具體命令如下：
pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --commit-each --purge --primary-key-only
命令列中的 --purge 代表只刪除，不歸檔。
指定了 --primary-key-only ，這樣，在執行 SELECT 操作時，就只會查詢主鍵，不會查詢所有列。
接下來，我們看看刪除命令相關的 General log 。
為了直觀地展示 pt-archiver 刪除資料的實現邏輯，實際測試時將 --limit 設定為了 3。
# 開啟事務 set autocommit=0; # 檢視錶結構，獲取主鍵 SHOW CREATE TABLE `employees`.`departments`; # 開始刪除第一批資料 # 通過 FORCE INDEX(`PRIMARY`) 強制使用主鍵 # 指定了 --primary-key-only，所以只會查詢主鍵 # 這裡其實無需獲取所有滿足條件的主鍵值，只取一個最小值和最大值即可。 SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 3; # 基於主鍵進行刪除，刪除的時候同時帶上了 --where 指定的刪除條件，以避免誤刪 DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd001'))) AND (((`dept_no` <= 'd003'))) AND (1=1) LIMIT 3; # 提交 commit; # 刪除第二批資料 SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`dept_no` >= 'd003')) ORDER BY `dept_no` LIMIT 3; DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd004'))) AND (((`dept_no` <= 'd006'))) AND (1=1); LIMIT 3 commit; # 刪除第三批資料 SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`dept_no` >= 'd006')) ORDER BY `dept_no` LIMIT 3; DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd007'))) AND (((`dept_no` <= 'd009'))) AND (1=1) LIMIT 3; commit; # 刪除最後一批資料 SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`dept_no` >= 'd009')) ORDER BY `dept_no` LIMIT 3; commit;
在業務程式碼中，如果我們有類似的刪除需求，不妨借鑑下 pt-archiver 的實現方式。
（2）將資料歸檔到檔案中
資料除了能歸檔到資料庫，也可歸檔到檔案中。
具體命令如下：
pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --file '/tmp/%Y-%m-%d-%D.%t'
指定的是 --file ，而不是 --dest。
檔名使用了日期格式化符號，支援的符號及含義如下：
%d Day of the month, numeric (01..31) %H Hour (00..23) %i Minutes, numeric (00..59) %m Month, numeric (01..12) %s Seconds (00..59) %Y Year, numeric, four digits %D Database name %t Table name
生成的檔案是 CSV 格式，後續可通過 LOAD DATA INFILE 命令載入到資料庫中。
如何避免主從延遲
無論是資料歸檔還是刪除，對於源庫，都需要執行 DELETE 操作。
很多人擔心，如果刪除的記錄數太多，會造成主從延遲。
事實上，pt-archiver 本身就具備了基於主從延遲來自動調節歸檔（刪除）操作的能力。
如果從庫的延遲超過 1s（由 --max-lag 指定）或複製狀態不正常，則會暫停歸檔（刪除）操作，直到從庫恢復。
預設情況下，pt-archiver 不會檢查從庫的延遲情況。
如果要檢查，需通過 --check-slave-lag 顯式設定從庫的地址，譬如，
pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --commit-each --primary-key-only --purge --check-slave-lag h=192.168.244.20,P=3306,u=pt_user,p=pt_pass
這裡只會檢查 192.168.244.20 的延遲情況。
如果有多個從庫需要檢查，需將 --check-slave-lag 指定多次，每次對應一個從庫。
常用引數
--analyze
在執行完歸檔操作後，執行 ANALYZE TABLE 操作。
後面可接任意字串，如果字串中含有 s ，則會在源庫執行 ANALYZE 操作。
如果字串中含有 d ，則會在目標庫執行 ANALYZE 操作。
如果同時帶有 d 和 s ，則源庫和目標庫都會執行 ANALYZE 操作。如，
--analyze ds
--optimize
在執行完歸檔操作後，執行 OPTIMIZE TABLE 操作。
用法同 --analyze 類似。
--charset
指定連線（Connection）字符集。
在 MySQL 8.0 之前，預設是 latin1。
在 MySQL 8.0 中，預設是 utf8mb4 。
注意，這裡的預設值與 MySQL 服務端字符集 character_set_server 無關。
若顯式設定了該值，pt-archiver 在建立連線後，會首先執行 SET NAMES 'charset_name' 操作。
--[no]check-charset
檢查源庫（目標庫）連線（Connection）字符集和表的字符集是否一致。
如果不一致，會提示以下錯誤：
Character set mismatch: --source DSN uses latin1, table uses gbk. You can disable this check by specifying --no-check-charset.
這個時候，切記不要按照提示指定 --no-check-charset 忽略檢查，否則很容易導致亂碼。
針對上述報錯，可將 --charset 指定為表的字符集。
注意，該選項並不是比較源庫和目標庫的字符集是否一致。
--[no]check-columns
檢查源表和目標表列名是否一致。
注意，只會檢查列名，不會檢查列的順序、列的資料型別是否一致。
--columns
歸檔指定列。
在有自增列的情況下，如果源表和目標表的自增列存在交集，可不歸檔自增列，這個時候，就需要使用 --columns 顯式指定歸檔列。
--dry-run
只列印待執行的 SQL，不實際執行。
常用於實際操作之前，校驗待執行的 SQL 是否符合自己的預期。
--ignore
使用 INSERT IGNORE 歸檔資料。
--no-delete
不刪除源庫的資料。
--replace
使用 REPLACE 操作歸檔資料。
--[no]safe-auto-increment
在歸檔有自增主鍵的表時，預設不會刪除自增主鍵最大的那一行。
這樣做，主要是為了規避 MySQL 8.0 之前自增主鍵不能持久化的問題。
在對全表進行歸檔時，這一點需要注意。
如果需要刪除，需指定 --no-safe-auto-increment 。
--source
給出源端例項的資訊。
除了常用的選項，其還支援如下選項：
-
a：指定連線的預設資料庫。
-
b：設定 SQL_LOG_BIN=0 。
如果是在源庫指定，則 DELETE 操作不會寫入到 Binlog 中。
如果是在目標庫指定，則 INSERT 操作不會寫入到 Binlog 中。
-
i：設定歸檔操作使用的索引，預設是主鍵。
--progress
顯示進度資訊，單位行數。
如 --progress 10000，則每歸檔（刪除）10000 行，就列印一次進度資訊。
TIME ELAPSED COUNT 2022-03-06T18:24:19 0 0 2022-03-06T18:24:20 0 10000 2022-03-06T18:24:21 1 20000
第一列是當前時間，第二列是已經消耗的時間，第三列是已歸檔（刪除）的行數。
總結
前面，我們對比了歸檔操作中不同引數的執行時間。
其中，--bulk-delete --limit 1000 --commit-each --bulk-insert 是最快的。不指定任何批量操作引數是最慢的。
但在使用 --bulk-insert 時要注意 ，如果匯入的過程中出現問題，pt-archiver 是不會提示任何錯誤的。
常見的錯誤有主鍵衝突，資料和目標列的資料型別不一致。
如果不使用 --bulk-insert，而是通過預設的 INSERT 操作來歸檔，大部分錯誤是可以識別出來的。
譬如，主鍵衝突，會提示以下錯誤。
DBD::mysql::st execute failed: Duplicate entry 'd001' for key 'PRIMARY' [for Statement "INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES (?,?)" with ParamValues: 0='d001', 1='Marketing'] at /usr/local/bin/pt-archiver line 6772.
匯入的資料和目標列的資料型別不一致，會提示以下錯誤。
DBD::mysql::st execute failed: Incorrect integer value: 'Marketing' for column 'dept_name' at row 1 [for Statement "INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES (?,?)" with ParamValues: 0='d001', 1='Marketing'] at /usr/local/bin/pt-archiver line 6772.
當然，資料和型別不一致，能被識別出來的前提是歸檔例項的 SQL_MODE 為嚴格模式。
如果待歸檔的例項中有 MySQL 5.6 ，我們其實很難將歸檔例項的 SQL_MODE 開啟為嚴格模式。
因為 MySQL 5.6 的 SQL_MODE 預設為非嚴格模式，所以難免會產生很多無效資料，譬如時間欄位中的 0000-00-00 00:00:00 。
這種無效資料，如果插入到開啟了嚴格模式的歸檔例項中，會直接報錯。
從資料安全的角度出發，最推薦的歸檔方式是：
- 先歸檔，但不刪除源庫的資料。
- 比對源庫和歸檔庫的資料是否一致。
- 如果比對結果一致，再刪除源庫的歸檔資料。
其中，第一步和第三步可通過 pt-archiver 搞定，第二步可通過 pt-table-sync 搞定。
相對於邊歸檔邊刪除的這種方式，雖然麻煩不少，但相對來說，更安全。