tidb归档历史数据删除

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】7.5.1
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】目前数据库有几张大表已经严重核心主库的业务读取写入，需要只保留三个月的最新数据，历史数据归档到别的地方，涉及到归档后删除历史数据问题，看看小伙伴们有没有完整的数据归档以及主库大量删除历史数据的方案。
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

2

三个月之前的数据量有多少呢

3

针对需要清理历史数据这种

  1. 如果在前期设计表结构可以控制的话，最好使用分区表，根据时间分区，在归档后可以快速清理历史数据
  2. 如果设计表阶段不能控制的话，这种想要快速删除数据，可以考虑查出需要删除数据的最大最小值，然后拼接sql，进行结转和批量删除
1 个赞
4

使用分区表，归档后直接清理分区

5

用分区表，或者可以用 ttl

删分区的话删除的压力比较小，是直接在 tikv 侧 gc 掉的。

ttl 的话可以按行级别在后台删除，不需要人为的干预，而且可以直接在原来的表上改。

1 个赞
6

按月份分区

7

历史数据没做分区，有五六亿条数据量吧，想归档到databend，目前归档方式可以用dumpling吗？归档后删除历史数据怎么操作合适。

8

因为是核心业务库，怕影响业务，这个是需要写个脚本吗？脚本大概逻辑是什么，或者你这边有脚本可以参考的没？

9

ttl是新建表的时候就加上啊 ，还是已经上线的表可以加呢？

10

TTL 可以 alter table 加的，https://docs.pingcap.com/zh/tidb/stable/time-to-live

也可以 ALTER TABLE t PARTITION BY ... 直接改分区表，但我觉得有风险，慎重使用

12

用dumpling当然可以，可以写个脚本将三个月之前的数据按天或者按月进行备份，之后脚本再进行分批次删除，不影响线上业务就行

13

你写过类似的脚步没，用python写脚步？有的话可以让我参考下。

14

mysql我一般有3种方案：
一、使用percona的工具包之pt-archiver命令：
pt-archiver --source h=10.11.1.1,P=3306,u=root,p=‘xxx’,D=dbname,t=x_trx_log --purge --charset=latin1 --where “create_time <= ‘2018-12-31’” --progress 1000 --limit 1000 --txn-size=1000 --bulk-delete --statistics --dry-run

二、分批删除数据，大事务拆分成N个小事务
set innodb_lock_wait_timeout=60;
/* define procedure to run loop to delte 10000 rows at a time /
DELIMITER $$
CREATE PROCEDURE DeleteActions()
BEGIN
DECLARE counter INT DEFAULT 1;
REPEAT
delete from actions_action where action NOT IN (‘like’, ‘subscribe’) limit 10000; commit;
SET counter = counter + 1;
SELECT SLEEP(2);
UNTIL counter >= 200
END REPEAT;
END$$
DELIMITER ;
/ call the procedure */
CALL DeleteActions();

三、等价SQL改写，创建一个临时表，将要保留的数据写入临时表，然后临时表与原表兑换名字，删除临时表。
1、检查数据量，每月多少记录
select count() from next_eip.t__exec_log where fcreated_time >= DATE_FORMAT(DATE_SUB(now(),interval 3 month),‘%Y-%m-%d’);
select date_format(fcreated_time,‘%Y-%m’),count() from next_eip.t__exec_log group by date_format(fcreated_time,‘%Y-%m’) order by 1 desc;

2、创建临时表
CREATE TABLE IF NOT EXISTS t__exec_log_tmp like t__exec_log;

3、给表加独占写锁，冻结数据变更
lock table t__exec_log write, t__exec_log_tmp write;

3、往临时表写入想保留的最近1月数据
inert into t__exec_log_tmp select * from t__exec_log where fcreated_time >= date_format(date_sub(now(),interval 1 month),‘%y-%m-%d’);

4、交换表名字，原表改名为备份表，临时表改名为原表
rename table t__exec_log to t__exec_log_bak, t__exec_log_tmp to t__exec_log;

5、释放表锁
unlock tables;

15

用she’ll或者python都可以

16

并不建议用分区表 ，删除用delete from xXX where 主键 between方式删除
拆分成多条sql执行，每次控制在10万以下

17

打算写python脚本，我想带系统性能检测方面的一些东西，都需要监控哪些指标，在服务器性能压力小的时候去做，或者你有这方面的脚步没，让我参考下，生产环境我需要谨慎。

18

感觉很妙。除了可以清理3个月的数据，还可以做其他应用。譬如一段时间不活跃的需要重置密码这类应用。
这个功能是哪个版本开始引入的？

19

6.5 开始有的

20

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。