pt-archiver重构版--大表数据归档工具 —— 筑梦之路

开源项目地址：GitHub - hcymysql/pt-archiver: Percona pt-archiver重构版--大表数据归档工具

一张大表，我们姑且说1亿条记录，原表我要保存近7天的数据。Percona pt-archiver工具是这样做的，逐条把历史数据insert到归档表，同时删除原表数据。7天数据比如说只有10万行，那么原表会直接删除9990万行记录，操作成本太高，固需要考虑重构。

重构版是这样做的，提取你要保留的7天数据至临时表，然后老表和临时表交换名字，这样大大缩减了可用时间。

工作原理阐述

具体的工作原理：

1、如果表有触发器、或者表有外键、或者表没有主键、或者binlog_format设置的值不是ROW格式，工具将直接退出，不予执行。

2、创建一个归档临时表和原表一样的空表结构。
CREATE TABLE IF NOT EXISTS ${mysql_table}_tmp like ${mysql_table};

3、在原表上创建增，删，改三个触发器将数据拷贝的过程中，原表产生的数据变更更新到临时表里。
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;



这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作：
（1）INSERT操作，所有的INSERT INTO转换为REPLACE INTO，当有新的记录插入到原表时，如果触发器还未把该记录同步到临时表，而这条记录之前因某种原因已经存在了，那么我们就可以利用REPLACE INTO进行覆盖，这样数据也是一致的；
（2）UPDATE操作，所有的UPDATE也转换为REPLACE INTO，如果临时表不存在原表更新的该记录，那么我们就直接插入该条记录；如果该记录已经同步到临时表了，那么直接进行覆盖插入即可，所有数据与原表也是一致的；
（3）DELETE操作，原表有删除操作，会触发至临时表执行删除。如果删除的记录还未同步到临时表，那么可以不在临时表执行，因为原表中该行的数据已经被删除了，这样数据也是一致的。
4、拷贝原表数据到临时表（默认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（主键名字可以是非id）进行范围查找，分批次控制插入行数，已减少对原表的锁定时间（读锁/共享锁）---将大事务拆分成若干块小事务，如果临时表已经存在该记录将会忽略插入，并且在数据导入时，我们能通过sleep参数控制休眠时间，以减少对磁盘IO的冲击。
5、Rename原表为_bak，临时表Rename为原表，名字互换。
RENAME TABLE ${mysql_table} to ${mysql_table}_bak, ${mysql_table}_tmp to ${mysql_table};
执行表改名字，会加table metadata lock元数据表锁，但基本是瞬间结束，故对线上影响不大。
6、删除原表上的三个触发器。
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;


至此全部过程结束，类似pt-osc原理。
注：考虑到删库跑路等安全性问题，工具没有对原表进行任何删除归档数据的操作。

 安装使用

yum install php php-mysqlnd -y

wget https://github.com/hcymysql/pt-archiver/archive/refs/heads/master.zip

unzip pt-archiver-master.zip


# 执行数据归档按id

php pt-archiver.php -h 10.10.10.7 -P 3306 -u dbaadmin -p'nf**nEFGF' -d test -t role_permission -w "id>=2863030" --limit 10000 --sleep 60

注：
    -w 过滤条件，例如"update_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d')"

    --limit 分批次插入，默认一批插入10000行
    
    --sleep 每次插完1000行休眠1秒

# 数据库归档按天：10天前的（ create_time>=2024-03-09）

php pt-archiver.php -h 10.10.10.7 -P 3306 -u dbaadmin -p'nfgh**EFGF' -d test -t company_license -w "create_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d')" --limit 10000 --sleep 60

如果对原表进行删除归档数据，可以借助原生工具 pt-archiver 进行分批缓慢删除。

shell> pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='hechunyang',D=test,t=sbtest1 --purge --charset=utf8 --where "id <= 500000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics

解释：删除test库，sbtest1表数据，字符集为utf8，删除条件是 id <= 5000000，每次取出200行进行处理，每处理200行则进行一次提交，每完成一次处理休眠1秒。

针对mysql 5.7特别说明

mysql> set global show_compatibility_56=on; 
mysql> set global sql_mode=''; 

 学下一下，做个记录