MySQL數(shù)據(jù)歸檔

IT那活兒發(fā)布于2023-01-11 13:19 / 2906人閱讀

MySQL數(shù)據(jù)歸檔

點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！

隨著MySQL數(shù)據(jù)表越來越大，需要對(duì)歷史數(shù)據(jù)按時(shí)間點(diǎn)做一次歸檔，重建歸檔表，再清除舊表中已經(jīng)歸檔的數(shù)據(jù)，提高數(shù)據(jù)庫(kù)的性能。

下面介紹一款比較好的工具：pt-archiver

方案描述

適用范圍：

MySQL
平臺(tái)：Linux

常用參數(shù)：

limit 1000 每次取1000行數(shù)據(jù)用pt-archive處理；
SELECT /*!40001 SQL_NO_CACHE */ FORCE INDEX(`PRIMARY`) FROM WHERE ORDER BY `id` LIMIT 1000
txn-size 2000 2000行是一個(gè)事務(wù)；
where ‘id<1000‘ 設(shè)置操作條件；
progress 5000 每處理5000行輸出一次處理信息；
statistics 輸出執(zhí)行過程及最后的操作統(tǒng)計(jì)。（只要不加上--quiet，默認(rèn)情況下pt-archive都會(huì)輸出執(zhí)行過程的）
charset=UTF8 指定字符集為UTF8；
bulk-delete 批量刪除source上的舊數(shù)據(jù)；
DELETE FROM WHERE LIMIT 1000
bulk-insert 批量插入數(shù)據(jù)到dest主機(jī)；
LOAD DATA LOCAL INFILE INTO TABLE
replace 將insert into 語(yǔ)句改成replace寫入到dest庫(kù)；
sleep 1 每次歸檔了limit個(gè)行記錄后的休眠1秒；
purge 刪除source數(shù)據(jù)庫(kù)的相關(guān)匹配記錄；
header 輸入列名稱到首行（和--file一起使用）；
no-check-charset 不指定字符集；
check-columns 檢驗(yàn)dest和source的表結(jié)構(gòu)是否一致，不一致自動(dòng)拒絕執(zhí)行（不加這個(gè)參數(shù)也行。默認(rèn)就是執(zhí)行檢查的）；
no-check-columns 不檢驗(yàn)dest和source的表結(jié)構(gòu)是否一致，不一致也執(zhí)行（會(huì)導(dǎo)致dest上的無法與source匹配的列值被置為null或者0）；
chekc-interval 默認(rèn)1s檢查一次；
local 不把optimize或analyze操作寫入到binlog里面（防止造成主從延遲巨大）；
retries 超時(shí)或者出現(xiàn)死鎖的話，pt-archiver進(jìn)行重試的間隔（默認(rèn)1s）；
no-version-check 目前為止，發(fā)現(xiàn)部分pt工具對(duì)阿里云RDS操作必須加這個(gè)參數(shù)；
analyze=ds 操作結(jié)束后，優(yōu)化表空間（d表示dest，s表示source）。

前提：

所使用用戶擁有足夠的權(quán)限（以下使用root），并且用戶允許連接到數(shù)據(jù)庫(kù)；
pt-archiver操作的表必須有主鍵。

清理數(shù)據(jù)：

直接清理原表中的歷史數(shù)據(jù)。

pt-archiver 
--source h=192.168.56.51,D=test,t=user_basic_info,u=root,
p=xxxxxxxxxx   --where "VALID_PERIOD <2021-01-01"  --purge 
--limit=2000  --no-check-charset 
--txn-size=2000  
--bulk-delete   --statistics 
--primary-key-only  --progress 2000

歸檔不清理數(shù)據(jù)：

歸檔表結(jié)構(gòu)需和原表結(jié)構(gòu)保持一致（為提升歸檔速度，可暫時(shí)不創(chuàng)建索引，待數(shù)據(jù)歸檔完成后進(jìn)行索引的創(chuàng)建。）將192.168.56.51上test.user_basic_info歸檔到192.168.56.52上的user_basic_info_bak。

pt-archiver --source 
h=192.168.56.51,D=test,t=user_basic_info,u=root,p=xxxxxxxxx
x --dest 
h=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxx
xxxxxx  --where 1=1  --limit=1000 --no-check-charset 
--txn-size=1000 --bulk-insert --no-delete --statistics -
-primary-key-only --progress 1000 --local

歸檔并清理數(shù)據(jù)：

pt-archiver --source 
h=192.168.56.51,D=test,t=user_basic_info,u=root,p=xxxxxxxxxx --dest 
h=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxx
xxxxxx --where 1=1  --limit=1000 --no-check-charset --
sleep 1 --txn-size=1000 --bulk-delete --statistics --
primary-key-only --progress 1000 --local

報(bào)錯(cuò)：

DBD::mysql::st execute failed: Loading local data is 
disabled; this must be enabled on both the client and server 
sides [for Statement "LOAD DATA LOCAL INFILE ? INTO TABLE 
`test`.`user_basic_info`(`id`)" with ParamValues: 
0=/tmp/WJZ3qiQ3ISpt-archiver] at /bin/pt-archiver line 6876.

處理方式：

root@localhost [test]>show variables like %INFILE%;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
root@localhost [test]>set global local_infile=1;
root@localhost [test]>show variables like %INFILE%;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+

總結(jié)：

pt-archiver是一個(gè)十分高效的表數(shù)據(jù)歸檔工具，歸檔數(shù)據(jù)可以分批進(jìn)行事務(wù)處理，減少性能消耗；
對(duì)于跨實(shí)例或者跨服務(wù)器的表數(shù)據(jù)歸檔，pt-archiver可以運(yùn)行在目標(biāo)端服務(wù)器，因?yàn)樯傻呐R時(shí)文件是在工具執(zhí)行所在的服務(wù)器；
對(duì)于大表的過期數(shù)據(jù)的批量刪除也可以通過pt-archiver指定選項(xiàng)--purge進(jìn)行處理。