歸檔，在 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 搞定，

相對于邊歸檔邊洗掉的這種方式，雖然麻煩不少，但相對來說，更安全，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/442868.html

標籤：MySQL

上一篇：在3n-?lgn?-3中的二進制矩陣中找到所需的索引

下一篇：MySQL 中如何歸檔資料