Percona pt-archiver重構(gòu)版--大表數(shù)據(jù)歸檔工具

相信很多小伙伴們，在日常對接開發(fā)時，有很多大表在業(yè)務(wù)上并沒有采取任何形式的切分，數(shù)據(jù)不停地往一張表里灌入，遲早有一天，磁盤空間報警。作為一個DBA，側(cè)重點(diǎn)是對數(shù)據(jù)庫的操作性能（大表增加字段/索引，QPS等）和存儲容量加以考慮，我們會建議開發(fā)對數(shù)據(jù)庫里的大表進(jìn)行數(shù)據(jù)歸檔處理，例如將3個月內(nèi)的訂單表保留在當(dāng)前表，歷史數(shù)據(jù)切分后保存在歸檔表中，之后歸檔表從主庫上移走以便騰出磁盤空間，并將其遷移至備份機(jī)中（有條件的可以將其轉(zhuǎn)換為TokuDB引擎），以便提供大數(shù)據(jù)部門抽取至HDFS上。

一張大表，我們姑且說1億條記錄，原表我要保存近7天的數(shù)據(jù)。Percona pt-archiver工具是這樣做的，逐條把歷史數(shù)據(jù)insert到歸檔表，同時刪除原表數(shù)據(jù)。7天數(shù)據(jù)比如說只有10萬行，那么原表會直接刪除9990萬行記錄，操作成本太高，固需要考慮重構(gòu)。

重構(gòu)版是這樣做的，提取你要保留的7天數(shù)據(jù)至臨時表，然后老表和臨時表交換名字，這樣大大縮減了可用時間。

具體的工作原理：
1、如果表有觸發(fā)器、或者表有外鍵、或者表沒有主鍵或者主鍵字段默認(rèn)不是id、或者binlog_format設(shè)置的值不是ROW格式，工具將直接退出，不予執(zhí)行。

2、創(chuàng)建一個歸檔臨時表和原表一樣的空表結(jié)構(gòu)。

成都創(chuàng)新互聯(lián)公司專注于大武口網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠為您提供大武口營銷型網(wǎng)站建設(shè)，大武口網(wǎng)站制作、大武口網(wǎng)頁設(shè)計、大武口網(wǎng)站官網(wǎng)定制、微信小程序開發(fā)服務(wù)，打造大武口網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供大武口網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

CREATE TABLE IF NOT EXISTS ${MySQL_table}_tmp like ${mysql_table};

3、在原表上創(chuàng)建增，刪，改三個觸發(fā)器將數(shù)據(jù)拷貝的過程中，原表產(chǎn)生的數(shù)據(jù)變更更新到臨時表里。

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_insert;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_insert AFTER INSERT 
    ON ${mysql_table} FOR EACH ROW 
    REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column);
    
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_update;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_update AFTER UPDATE 
    ON ${mysql_table} FOR EACH ROW 
    REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column);
    
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_delete;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_delete AFTER DELETE 
    ON ${mysql_table} FOR EACH ROW 
    DELETE IGNORE FROM ${mysql_database}.${mysql_table}_tmp 
    WHERE ${mysql_database}.${mysql_table}_tmp.id <=> OLD.id;

這三個觸發(fā)器分別對應(yīng)于INSERT、UPDATE、DELETE三種操作：
（1）INSERT操作，所有的INSERT INTO轉(zhuǎn)換為REPLACE INTO，當(dāng)有新的記錄插入到原表時，如果觸發(fā)器還未把該記錄同步到臨時表，而這條記錄之前因某種原因已經(jīng)存在了，那么我們就可以利用REPLACE INTO進(jìn)行覆蓋，這樣數(shù)據(jù)也是一致的；
（3）UPDATE操作，所有的UPDATE也轉(zhuǎn)換為REPLACE INTO，如果臨時表不存在原表更新的該記錄，那么我們就直接插入該條記錄；如果該記錄已經(jīng)同步到臨時表了，那么直接進(jìn)行覆蓋插入即可，所有數(shù)據(jù)與原表也是一致的；
（1）DELETE操作，原表有刪除操作，會觸發(fā)至臨時表執(zhí)行刪除。如果刪除的記錄還未同步到臨時表，那么可以不在臨時表執(zhí)行，因?yàn)樵碇性撔械臄?shù)據(jù)已經(jīng)被刪除了，這樣數(shù)據(jù)也是一致的。

4、拷貝原表數(shù)據(jù)到臨時表（默認(rèn)1000條一批次插入并休眠1秒）

INSERT LOW_PRIORITY IGNORE INTO ${mysql_database}.${mysql_table}_tmp 
SELECT * FROM ${mysql_database}.${mysql_table} WHERE id>=".$begin_Id."
 AND id<".($begin_Id=$begin_Id+$limit_chunk)." LOCK IN SHARE MODE;

通過主鍵id進(jìn)行范圍查找，分批次控制插入行數(shù)，已減少對原表的鎖定時間（讀鎖/共享鎖）---將大事務(wù)拆分成若干塊小事務(wù)，如果臨時表已經(jīng)存在該記錄將會忽略插入，并且在數(shù)據(jù)導(dǎo)入時，我們能通過sleep參數(shù)控制休眠時間，以減少對磁盤IO的沖擊。

5、Rename原表為_bak，臨時表Rename為原表，名字互換。

RENAME TABLE ${mysql_table} to ${mysql_table}_bak, ${mysql_table}_tmp to ${mysql_table};

執(zhí)行表改名字，會加table metadata lock元數(shù)據(jù)表鎖，但基本是瞬間結(jié)束，故對線上影響不大。

6、刪除原表上的三個觸發(fā)器。

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_insert;
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_update;
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_delete;

至此全部過程結(jié)束，類似pt-osc原理。

注：考慮到刪庫跑路等安全性問題，工具沒有對原表進(jìn)行任何刪除歸檔數(shù)據(jù)的操作。
--------------------------------------------------------------------------------------------------------------------------------

使用

# yum install php php-mysql -y

######下面的配置信息修改成你自己的?。?！######

$mysql_server='10.10.159.31';
$mysql_username='admin'; 
$mysql_password='123456';
$mysql_database='test';
$mysql_port='3306';
$mysql_table='sbtest1';
###$where_column="update_time >= DATE_FORMAT(DATE_SUB(now(),interval 30 day),'%Y-%m-%d')";
$where_column="id>=99900000";
$limit_chunk='1000';     ###分批次插入，默認(rèn)一批插入1000行
$insert_sleep='1';        ###每次插完1000行休眠1秒

###############################################

執(zhí)行

# php pt-archiver.php

工具下載地址：

https://pan.baidu.com/s/1uoBGWWh5CCLY5C8uM32DoA

https://github.com/hcymysql/pt-archiver