加强版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原理。
注:考虑到删库跑路等安全性问题,工具没有对原表进行任何删除归档数据的操作。


安装

[root@test19-server07 jesong]# yum install php php-mysqlnd -y
[root@test19-server07 jesong]#  wget https://github.com/hcymysql/pt-archiver/archive/refs/heads/master.zip
[root@test19-server07 jesong]# unzip pt-archiver-master.zip

执行数据归档按id:

[root@test19-server07 pt-archiver-master]# php pt-archiver.php -h 10.10.10.7 -P 3306 -u dbaadmin -p'nf**nEFGF' -d wushouyang -t role_permission -w "id>=2863030" --limit 10000 --sleep 60
role_permission_tmp临时表创建成功

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_role_permission_insert;
CREATE TRIGGER pt_archiver_wushouyang_role_permission_insert AFTER INSERT 
	ON role_permission FOR EACH ROW 
	REPLACE INTO wushouyang.role_permission_tmp (id,role_id,role_uid,menu_id,menu_uid,menu_code,data_code,data_value,custom_data_codes,data_show_type,product_id,company_id) VALUES (NEW.id,NEW.role_id,NEW.role_uid,NEW.menu_id,NEW.menu_uid,NEW.menu_code,NEW.data_code,NEW.data_value,NEW.custom_data_codes,NEW.data_show_type,NEW.product_id,NEW.company_id);

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_role_permission_update;
CREATE TRIGGER pt_archiver_wushouyang_role_permission_update AFTER UPDATE 
	ON role_permission FOR EACH ROW 
	REPLACE INTO wushouyang.role_permission_tmp (id,role_id,role_uid,menu_id,menu_uid,menu_code,data_code,data_value,custom_data_codes,data_show_type,product_id,company_id) VALUES (NEW.id,NEW.role_id,NEW.role_uid,NEW.menu_id,NEW.menu_uid,NEW.menu_code,NEW.data_code,NEW.data_value,NEW.custom_data_codes,NEW.data_show_type,NEW.product_id,NEW.company_id);

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_role_permission_delete;
CREATE TRIGGER pt_archiver_wushouyang_role_permission_delete AFTER DELETE 
	ON role_permission FOR EACH ROW 
	DELETE IGNORE FROM wushouyang.role_permission_tmp WHERE wushouyang.role_permission_tmp._rowid <=> OLD._rowid;

role_permission表触发器创建成功
SELECT _rowid,(SELECT max(_rowid) FROM wushouyang.role_permission) AS max_rowid FROM  wushouyang.role_permission WHERE id>=2863030 order by _rowid asc LIMIT 1
INSERT LOW_PRIORITY IGNORE INTO wushouyang.role_permission_tmp SELECT * FROM wushouyang.role_permission WHERE id>=2863030 AND (_rowid>=2863030 AND _rowid<2873030) LOCK IN SHARE MODE 

插入行数是: 5
role_permission_tmp临时表插入成功
INSERT LOW_PRIORITY IGNORE INTO wushouyang.role_permission_tmp SELECT * FROM wushouyang.role_permission WHERE id>=2863030 AND (_rowid>=2873030 AND _rowid<2883030) LOCK IN SHARE MODE 

插入行数是: 0
role_permission表归档成功

数据库归档按天:10天前的( create_time>=2024-03-09)

[root@test19-server07 pt-archiver-master]# php pt-archiver.php -h 10.10.10.7 -P 3306 -u dbaadmin -p'nfgh**EFGF' -d wushouyang -t company_license -w "create_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d')" --limit 10000 --sleep 60
company_license_tmp临时表创建成功

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_company_license_insert;
CREATE TRIGGER pt_archiver_wushouyang_company_license_insert AFTER INSERT 
	ON company_license FOR EACH ROW 
	REPLACE INTO wushouyang.company_license_tmp (id,company_id,license_code,item_no,license_type,product_id,item_code,item_value,status,start_time,end_time,create_time,create_user_id) VALUES (NEW.id,NEW.company_id,NEW.license_code,NEW.item_no,NEW.license_type,NEW.product_id,NEW.item_code,NEW.item_value,NEW.status,NEW.start_time,NEW.end_time,NEW.create_time,NEW.create_user_id);

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_company_license_update;
CREATE TRIGGER pt_archiver_wushouyang_company_license_update AFTER UPDATE 
	ON company_license FOR EACH ROW 
	REPLACE INTO wushouyang.company_license_tmp (id,company_id,license_code,item_no,license_type,product_id,item_code,item_value,status,start_time,end_time,create_time,create_user_id) VALUES (NEW.id,NEW.company_id,NEW.license_code,NEW.item_no,NEW.license_type,NEW.product_id,NEW.item_code,NEW.item_value,NEW.status,NEW.start_time,NEW.end_time,NEW.create_time,NEW.create_user_id);

DROP TRIGGER IF EXISTS pt_archiver_wushouyang_company_license_delete;
CREATE TRIGGER pt_archiver_wushouyang_company_license_delete AFTER DELETE 
	ON company_license FOR EACH ROW 
	DELETE IGNORE FROM wushouyang.company_license_tmp WHERE wushouyang.company_license_tmp._rowid <=> OLD._rowid;

company_license表触发器创建成功
SELECT _rowid,(SELECT max(_rowid) FROM wushouyang.company_license) AS max_rowid FROM  wushouyang.company_license WHERE create_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d') order by _rowid asc LIMIT 1
INSERT LOW_PRIORITY IGNORE INTO wushouyang.company_license_tmp SELECT * FROM wushouyang.company_license WHERE create_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d') AND (_rowid>=130321 AND _rowid<140321) LOCK IN SHARE MODE 

插入行数是: 361
company_license_tmp临时表插入成功
INSERT LOW_PRIORITY IGNORE INTO wushouyang.company_license_tmp SELECT * FROM wushouyang.company_license WHERE create_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d') AND (_rowid>=140321 AND _rowid<150321) LOCK IN SHARE MODE 

插入行数是: 0
company_license表归档成功


注:-w 过滤条件,例如"update_time >= DATE_FORMAT(DATE_SUB(now(),interval 10 day),'%Y-%m-%d')"
    --limit 分批次插入,默认一批插入10000行    
    --sleep 每次插完1000行休眠1秒

问题

5.7环境有问题,请执行下面的2条语句重跑即可
mysql> set global show_compatibility_56=on; 
mysql> set global sql_mode=''; 

原生工具 pt-archiver 进行分批缓慢删除:

如果对原表进行删除归档数据,可以借助原生工具 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秒。

数据库技术交流群:
在这里插入图片描述

  • 20
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值