数据库归档实例处理方案

背景

自12月5日以来，某在线实例每天都出现了数据库延迟报警。经过初步排查，延迟时段的TPS并未显著高峰，主要问题集中在matomo_log_link_visit_action表的DML操作上。该表目前已膨胀至60GB，远超单表最大容量10G的规范限制。经与开发团队沟通，决定对该表进行历史数据归档操作，保留最近一个月的数据。

操作步骤

确认数据归档条件

开发团队决定采用非主键列server_time按时间顺序进行删除，并将条件转换为主键列处理。

show create table matomo_log_link_visit_action;select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-04-30 23:59:59';select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-05-31 23:59:59';-- 以此类推，直到2018-10-31

数据归档方案

由于历史表文件较大，采用按月归档的方式进行数据删除，同时在目标库中创建每月份的duplicate表进行数据存储，便于后期查询。

create table visit_action_4 like matomo_log_link_visit_action;create table visit_action_5 like matomo_log_link_visit_action;create table visit_action_6 like matomo_log_link_visit_action;-- 以此类推，直到visit_action_10

数据归档命令准备

参考Percona Toolkit的pt-archiver工具参数及使用示例，预先编辑归档命令。

# 4月归档pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_4 \--no-version-check \--where "idlink_va <= 4383363" \--statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 \>> xxx--matomo_log_link_visit_action--visit_action_4.log# 5月归档pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_5 \--no-version-check \--where "idlink_va <= 26473975 and idlink_va > 4383363" \--statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 \>> xxx--matomo_log_link_visit_action--visit_action_5.log

数据归档执行

按照上述命令逐月归档历史数据。

数据一致性检查

归档完成后，验证源数据表与归档表的数据是否一致。

-- 4月验证select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;select count(*) from visit_action_4 where idlink_va <= 4383363;-- 5月验证select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;select count(*) from visit_action_4 where idlink_va <= 4383363;

数据归档备份

对归档表进行备份操作，确保数据安全可恢复。

mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 \--set-gtid-purged=OFF --no-create-info xxx visit_action_4 \>> /data/backup/xxx_history/xxx--visit_action_4.sql.bak# 以此类推，直到visit_action_10

历史数据删除

完成数据归档后，执行历史数据的删除操作。

pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--where "idlink_va <= 206555065" \--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \>> xxx--matomo_log_link_visit_action--archive_10.log

或者使用脚本批量删除历史数据：

#!/bin/bashUSER=xxxPWD="xxx"SOCK=xxx/mysql.sockDB=xxxTB=matomo_log_link_visit_actionMAX_ID=206555065NUM=1000PK=idlink_vaMY_CLI="/usr/local/bin/mysql -u${USER} -p${PWD} -S${SOCK} ${DB}"for ((i=1; i<=206556; i++)); do    ${MY_CLI} -e "delete from ${TB} where ${PK} <= ${MAX_ID} limit ${NUM};"     sleep 0.2;    echo $i;done

工具介绍

本次操作使用了Percona Toolkit中的pt-archiver工具。该工具属于Percona Toolkit的一部分，主要用于数据归档、删除和迁移等场景，是处理大规模数据库操作的高效解决方案。

工具参数

连接数据库的参数

参数	说明
--source	指定源数据库的DSN
--dest	指定目标数据库的DSN
-h	连接的主机名
-P	端口号
-S	数据库的套接字
-u	用户名
-p	密码
-D	数据库名称
t	要操作的表名
-A	字符集
F	读取默认选项文件
L	允许本地加载数据文件
a	执行查询时使用的数据库
b	禁用二进制日志
i	指定索引
m	插件模块名称
--[no]version-check	检查工具版本（默认启用）

常用参数

参数	说明	示例
--[no]version-check	检查工具版本，默认启用	--no-version-check
--where	限制数据范围的WHERE子句	--where "idlink_va <= 4383363"
--statistics	显示执行统计信息	--statistics
--no-delete	不删除归档数据	--no-delete
--bulk-insert	使用LOAD DATA INFILE批量插入	--bulk-insert
--progress	显示进度信息	--progress=5000
--limit	每次处理的行数	--limit=500
--txn-size	每次事务处理的行数	--txn-size=100
--sleep	处理完成后的休眠时间	--sleep=1
--bulk-delete	批量删除源数据	--bulk-delete
--purge	删除源数据而非归档	--purge
--header	输出列名到文件首行	--header
--[no]check-columns	检查源、目标表列一致性	默认检查
--analyze	数据分析优化表空间	--analyze=s

场景示例

A. 复制数据到其他实例，保留源数据

pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_4 \--where "idlink_va <= 4383363" \--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 \>> xxx--matomo_log_link_visit_action--visit_action_4.log

B. 删除旧数据

pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--where "idlink_va <= 4383363" \--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \>> xxx--matomo_log_link_visit_action--archive_4.log

C. 导出数据到文件

pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \--where "idlink_va <= 4383363" \--file '/root/2018-04.txt' \--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 \>> xxx--matomo_log_link_visit_action--visit_action_4.log

D. 导出并删除数据

pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \--charset 'UTF8' \--where "idlink_va <= 4383363" \--file '/root/2018-04.txt' \--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \>> xxx--matomo_log_link_visit_action--archive_4.log

通过以上步骤和工具，我们可以高效地处理大规模数据库的归档和删除任务，确保数据安全并遵守规范要求。

转载地址：http://jwafk.baihongyu.com/

你可能感兴趣的文章