建站
咨詢
售后
建站咨詢
新聞中心
歸檔，在MySQL中，是一個(gè)相對(duì)高頻的操作。
它通常涉及以下兩個(gè)動(dòng)作：
- 遷移：將數(shù)據(jù)從業(yè)務(wù)實(shí)例遷移到歸檔實(shí)例。
- 刪除：從業(yè)務(wù)實(shí)例中刪除已遷移的數(shù)據(jù)。
在處理類似需求時(shí)，都是開發(fā)童鞋提單給DBA，由DBA來處理。
于是，很多開發(fā)童鞋就好奇，DBA都是怎么執(zhí)行歸檔操作的？歸檔條件沒有索引會(huì)鎖表嗎？安全嗎，會(huì)不會(huì)數(shù)據(jù)刪了，卻又沒歸檔成功？
針對(duì)這些疑問，下面介紹MySQL中的數(shù)據(jù)歸檔神器——pt-archiver。
一、什么是 pt-archiver
pt-archiver是Percona Toolkit中的一個(gè)工具。
Percona Toolkit是Percona公司提供的一個(gè)MySQL工具包。
工具包里提供了很多實(shí)用的MySQL管理工具。
譬如，我們常用的表結(jié)構(gòu)變更工具pt-online-schema-change，主從數(shù)據(jù)一致性校驗(yàn)工具pt-table-checksum。
毫不夸張地說，熟練使用Percona Toolkit是MySQL DBA必備的技能之一。
二、安裝
Percona Toolkit下載地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
官方針對(duì)多個(gè)系統(tǒng)提供了現(xiàn)成的軟件包。
我常用的是Linux - Generic二進(jìn)制包。
下面以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
三、簡單入門
首先，我們看一個(gè)簡單的歸檔Demo。
測試數(shù)據(jù)
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表的數(shù)據(jù)從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"
命令行中指定了三個(gè)參數(shù)。
- --source：源庫（業(yè)務(wù)實(shí)例）的DSN。
DSN在Percona Toolkit中比較常見，可理解為目標(biāo)實(shí)例相關(guān)信息的縮寫。
支持的縮寫及含義如下：
- --dest：目標(biāo)庫（歸檔實(shí)例）的DSN。
- --where：歸檔條件。"1=1"代表歸檔全表。
四、實(shí)現(xiàn)原理
下面結(jié)合General log的輸出看看pt-archiver的實(shí)現(xiàn)原理。
源庫日志
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
...
目標(biāo)庫日志
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
...結(jié)合源庫和目標(biāo)庫的日志，可以看到：
1）pt-archiver首先會(huì)從源庫查詢一條記錄，然后再將該記錄插入到目標(biāo)庫中。
目標(biāo)庫插入成功，才會(huì)從源庫中刪除這條記錄。
這樣就能確保數(shù)據(jù)在刪除之前，一定是歸檔成功的。
2）仔細(xì)觀察這幾個(gè)操作的執(zhí)行時(shí)間，其先后順序如下。
- 源庫查詢記錄。
- 目標(biāo)庫插入記錄。
- 源庫刪除記錄。
- 目標(biāo)庫COMMIT。
- 源庫COMMIT。
這種實(shí)現(xiàn)借鑒了分布式事務(wù)中的兩階段提交算法。
3）--where參數(shù)中的 "1=1" 會(huì)傳遞到SELECT操作中。
"1=1" 代表歸檔全表，也可指定其它條件，如我們常用的時(shí)間。
4）每次查詢都是使用主鍵索引，這樣即使歸檔條件中沒有索引，也不會(huì)產(chǎn)生全表掃描。
5）每次刪除都是基于主鍵，這樣可避免歸檔條件沒有索引導(dǎo)致全表被鎖的風(fēng)險(xiǎn)。
五、批量歸檔
如果使用Demo中的參數(shù)進(jìn)行歸檔，在數(shù)據(jù)量比較大的情況下，效率會(huì)非常低，畢竟COMMIT是一個(gè)昂貴的操作。
所以在線上，我們通常都會(huì)進(jìn)行批量操作。
具體命令如下：
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
相對(duì)于之前的歸檔命令，這條命令額外指定了四個(gè)參數(shù)，其中，
- --bulk-delete：批量刪除。
- --limit：每批歸檔的記錄數(shù)。
- --commit-each：對(duì)于每一批記錄，只會(huì) COMMIT 一次。
- --bulk-insert：歸檔數(shù)據(jù)以 LOAD DATA INFILE 的方式導(dǎo)入到歸檔庫中。
看看上述命令對(duì)應(yīng)的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
目標(biāo)庫
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：
注意：
1）如果要執(zhí)行LOAD DATA LOCAL INFILE操作，需將目標(biāo)庫的local_infile參數(shù)設(shè)置為ON。
2）如果不指定--bulk-insert且沒指定--commit-each，則目標(biāo)庫的插入還是會(huì)像Demo中顯示的那樣，逐行提交。
3）如果不指定--commit-each，即使表中的9條記錄是通過一條DELETE命令刪除的，但因?yàn)樯婕傲?條記錄，pt-archiver會(huì)執(zhí)行COMMIT操作9次。目標(biāo)庫同樣如此。
4）在使用--bulk-insert歸檔時(shí)要注意，如果導(dǎo)入的過程中出現(xiàn)問題，譬如主鍵沖突，pt-archiver是不會(huì)提示任何錯(cuò)誤的。
六、不同歸檔參數(shù)之間的速度對(duì)比
下表是歸檔20w數(shù)據(jù)，不同參數(shù)之間的執(zhí)行時(shí)間對(duì)比。
通過表格中的數(shù)據(jù)，我們可以得出以下幾點(diǎn)：
1）第一種方式是最慢的。
這種情況下，無論是源庫還是歸檔庫，都是逐行操作并提交的。
2）只指定--bulk-delete --limit 1000依然很慢。
這種情況下，源庫是批量刪除，但COMMIT次數(shù)并沒有減少。
歸檔庫依然是逐行插入并提交的。
3）--bulk-delete --limit 1000 --commit-each
相當(dāng)于第二種歸檔方式，源庫和目標(biāo)庫都是批量提交的。
4）--limit 1000 和 --limit 5000歸檔性能相差不大。
5）--bulk-delete --limit 1000 --bulk-insert與--bulk-delete --limit 1000 --commit-each --bulk-insert相比，沒有設(shè)置--commit-each。
雖然都是批量操作，但前者會(huì)執(zhí)行COMMIT操作1000次。
由此來看，空事務(wù)并不是沒有代價(jià)的。
七、其它常見用法
1、刪除數(shù)據(jù)
刪除數(shù)據(jù)是pt-archiver另外一個(gè)常見的使用場景。
具體命令如下：
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 ，這樣，在執(zhí)行 SELECT 操作時(shí)，就只會(huì)查詢主鍵，不會(huì)查詢所有列。
接下來，我們看看刪除命令相關(guān)的General log。
為了直觀地展示pt-archiver刪除數(shù)據(jù)的實(shí)現(xiàn)邏輯，實(shí)際測試時(shí)將--limit設(shè)置為了 3。
# 開啟事務(wù)
set autocommit=0;
# 查看表結(jié)構(gòu)，獲取主鍵
SHOW CREATE TABLE `employees`.`departments`;
# 開始刪除第一批數(shù)據(jù)
# 通過 FORCE INDEX(`PRIMARY`) 強(qiáng)制使用主鍵
# 指定了 --primary-key-only，所以只會(huì)查詢主鍵
# 這里其實(shí)無需獲取所有滿足條件的主鍵值，只取一個(gè)最小值和最大值即可。
SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 3;
# 基于主鍵進(jìn)行刪除，刪除的時(shí)候同時(shí)帶上了 --where 指定的刪除條件，以避免誤刪
DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd001'))) AND (((`dept_no` <= 'd003'))) AND (1=1) LIMIT 3;
# 提交
commit;
# 刪除第二批數(shù)據(jù)
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;
# 刪除第三批數(shù)據(jù)
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;
# 刪除最后一批數(shù)據(jù)
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;
在業(yè)務(wù)代碼中，如果我們有類似的刪除需求，不妨借鑒下pt-archiver的實(shí)現(xiàn)方式。
2、將數(shù)據(jù)歸檔到文件中
數(shù)據(jù)除了能歸檔到數(shù)據(jù)庫，也可歸檔到文件中。
具體命令如下：
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。
文件名使用了日期格式化符號(hào)，支持的符號(hào)及含義如下：
%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格式，后續(xù)可通過LOAD DATA INFILE命令加載到數(shù)據(jù)庫中。
八、如何避免主從延遲
無論是數(shù)據(jù)歸檔還是刪除，對(duì)于源庫，都需要執(zhí)行DELETE操作。
很多人擔(dān)心，如果刪除的記錄數(shù)太多，會(huì)造成主從延遲。
事實(shí)上，pt-archiver本身就具備了基于主從延遲來自動(dòng)調(diào)節(jié)歸檔（刪除）操作的能力。
如果從庫的延遲超過1s（由 --max-lag 指定）或復(fù)制狀態(tài)不正常，則會(huì)暫停歸檔（刪除）操作，直到從庫恢復(fù)。
默認(rèn)情況下，pt-archiver不會(huì)檢查從庫的延遲情況。
如果要檢查，需通過--check-slave-lag顯式設(shè)置從庫的地址，譬如，
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
這里只會(huì)檢查192.168.244.20的延遲情況。
如果有多個(gè)從庫需要檢查，需將--check-slave-lag指定多次，每次對(duì)應(yīng)一個(gè)從庫。
九、常用參數(shù)
- --analyze
在執(zhí)行完歸檔操作后，執(zhí)行ANALYZE TABLE操作。
后面可接任意字符串，如果字符串中含有 s ，則會(huì)在源庫執(zhí)行 ANALYZE 操作。
如果字符串中含有 d ，則會(huì)在目標(biāo)庫執(zhí)行ANALYZE操作。
如果同時(shí)帶有 d 和 s ，則源庫和目標(biāo)庫都會(huì)執(zhí)行ANALYZE操作。如，
--analyze ds
- --optimize
在執(zhí)行完歸檔操作后，執(zhí)行OPTIMIZE TABLE操作。
用法同 --analyze 類似。
- --charset
指定連接（Connection）字符集。
在 MySQL 8.0之前，默認(rèn)是latin1。
在 MySQL 8.0中，默認(rèn)是utf8mb4 。
注意，這里的默認(rèn)值與MySQL服務(wù)端字符集character_set_server無關(guān)。
若顯式設(shè)置了該值，pt-archiver在建立連接后，會(huì)首先執(zhí)行SET NAMES 'charset_name'操作。
- --[no]check-charset
檢查源庫（目標(biāo)庫）連接（Connection）字符集和表的字符集是否一致。
如果不一致，會(huì)提示以下錯(cuò)誤：
Character set mismatch: --source DSN uses latin1, table uses gbk. You can disable this check by specifying --no-check-charset.
這個(gè)時(shí)候，切記不要按照提示指定 --no-check-charset 忽略檢查，否則很容易導(dǎo)致亂碼。
針對(duì)上述報(bào)錯(cuò)，可將--charset指定為表的字符集。
注意，該選項(xiàng)并不是比較源庫和目標(biāo)庫的字符集是否一致。
- --[no]check-columns
檢查源表和目標(biāo)表列名是否一致。
注意，只會(huì)檢查列名，不會(huì)檢查列的順序、列的數(shù)據(jù)類型是否一致。
- --columns
歸檔指定列。
在有自增列的情況下，如果源表和目標(biāo)表的自增列存在交集，可不歸檔自增列，這個(gè)時(shí)候，就需要使用--columns顯式指定歸檔列。
- --dry-run
只打印待執(zhí)行的SQL，不實(shí)際執(zhí)行。
常用于實(shí)際操作之前，校驗(yàn)待執(zhí)行的SQL是否符合自己的預(yù)期。
- --ignore
使用INSERT IGNORE歸檔數(shù)據(jù)。
- --no-delete
不刪除源庫的數(shù)據(jù)。
- --replace
使用REPLACE操作歸檔數(shù)據(jù)。
- --[no]safe-auto-increment
在歸檔有自增主鍵的表時(shí)，默認(rèn)不會(huì)刪除自增主鍵最大的那一行。
這樣做，主要是為了規(guī)避MySQL 8.0之前自增主鍵不能持久化的問題。
在對(duì)全表進(jìn)行歸檔時(shí)，這一點(diǎn)需要注意。
如果需要?jiǎng)h除，需指定--no-safe-auto-increment。
- --source
給出源端實(shí)例的信息。
除了常用的選項(xiàng)，其還支持如下選項(xiàng)：
a：指定連接的默認(rèn)數(shù)據(jù)庫。
b：設(shè)置 SQL_LOG_BIN=0 。
如果是在源庫指定，則DELETE操作不會(huì)寫入到Binlog中。
如果是在目標(biāo)庫指定，則INSERT操作不會(huì)寫入到Binlog中。
i：設(shè)置歸檔操作使用的索引，默認(rèn)是主鍵。
- --progress
顯示進(jìn)度信息，單位行數(shù)。
如 --progress 10000，則每歸檔（刪除）10000行，就打印一次進(jìn)度信息。
TIME ELAPSED COUNT
2022-03-06T18:24:19 0 0
2022-03-06T18:24:20 0 10000
2022-03-06T18:24:21 1 20000
第一列是當(dāng)前時(shí)間，第二列是已經(jīng)消耗的時(shí)間，第三列是已歸檔（刪除）的行數(shù)。
十、總結(jié)
前面，我們對(duì)比了歸檔操作中不同參數(shù)的執(zhí)行時(shí)間。
其中，--bulk-delete --limit 1000 --commit-each --bulk-insert是最快的。不指定任何批量操作參數(shù)是最慢的。
但在使用--bulk-insert時(shí)要注意 ，如果導(dǎo)入的過程中出現(xiàn)問題，pt-archiver是不會(huì)提示任何錯(cuò)誤的。
常見的錯(cuò)誤有主鍵沖突，數(shù)據(jù)和目標(biāo)列的數(shù)據(jù)類型不一致。
如果不使用--bulk-insert，而是通過默認(rèn)的INSERT操作來歸檔，大部分錯(cuò)誤是可以識(shí)別出來的。
譬如，主鍵沖突，會(huì)提示以下錯(cuò)誤。
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.
導(dǎo)入的數(shù)據(jù)和目標(biāo)列的數(shù)據(jù)類型不一致，會(huì)提示以下錯(cuò)誤。
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.
當(dāng)然，數(shù)據(jù)和類型不一致，能被識(shí)別出來的前提是歸檔實(shí)例的SQL_MODE為嚴(yán)格模式。
如果待歸檔的實(shí)例中有MySQL 5.6，我們其實(shí)很難將歸檔實(shí)例的SQL_MODE開啟為嚴(yán)格模式。
因?yàn)镸ySQL 5.6的SQL_MODE默認(rèn)為非嚴(yán)格模式，所以難免會(huì)產(chǎn)生很多無效數(shù)據(jù)，譬如時(shí)間字段中的0000-00-00 00:00:00 。
這種無效數(shù)據(jù)，如果插入到開啟了嚴(yán)格模式的歸檔實(shí)例中，會(huì)直接報(bào)錯(cuò)。
從數(shù)據(jù)安全的角度出發(fā)，最推薦的歸檔方式是：
1）先歸檔，但不刪除源庫的數(shù)據(jù)。
2）比對(duì)源庫和歸檔庫的數(shù)據(jù)是否一致。
3）如果比對(duì)結(jié)果一致，再刪除源庫的歸檔數(shù)據(jù)。
其中，第一步和第三步可通過pt-archiver搞定，第二步可通過pt-table-sync搞定。
相對(duì)于邊歸檔邊刪除的這種方式，雖然麻煩不少，但相對(duì)來說，更安全。
本文題目：怎么還有人問MySQL是如何歸檔數(shù)據(jù)的呢？
鏈接地址：http://m.fisionsoft.com.cn/article/cdpjois.html
