MySQL数据自动清理系统

MySQL数据自动清理系统

一、 目标

1.   以时间字段为条件，自动清理一定时间之前的数据

2.   支持每次小批量分批清理

3.   支持自定义执行清理的时间窗

4.   支持简单的主从表的关系数据删除

 二、清理数据方法

1.   通过分区表

适用于用于删除的条件的时段字段不会修改的日志表类型

1)  支持的分区方法

a)  分区单位：天 / 月

b)   分区字段类型: datetime / timestamp

2)   实现方法

       通过存储过程实现清理逻辑，删除旧的分区，生成新分区

2. 直接按时间字段删除

适用于用于删除的条件的时间字段有修改情况的表

1)  支持的用于删除的条件字段类型

a)  datetime / timestamp

b)  unix_timestamp(支持到毫秒级)

2)  实现方法

在脚本中实现清理逻辑，小批量删除数据

二、设计

1.   相关流程

(图1)

 

 

（图2）

2.  程序逻辑

1)   使用两个线程循环处理，线程 HostInfo 用来取要清理的表列表，线程autoClear用来处理清理表的动作

2)   线程 HostInfo

a)    循环取要处理的服务器列表，放到公共变量 arrHost 中，如果已经存在就不再放入

b)    取完后，休息 30 秒后，再取服务器列表

3)    线程 autoClear

a)    从公共变量 arrHost 中取一个需要清理的表，并将该表的信息从 arrHost 中删除，然后判断该表所在的服务器上是否已经有清理任务在执行，如果有并且任务数超过限制就退出，否则进行数据清理动作并记录下被删除的数据条数。

清理时判断是否在清理的时间窗口，如果不在了，就退出该表的清理过程，合计被删除的记录数，并写入日志。

b)   一个表清理完成后，再从 arrHost 中取下一个表的信息，如果 arrHost 中没有记录就停止 10 秒，再尝试取需要清理的表

 

3.   相关表

1)   主表配置表

CREATE TABLE `autoclear_setting` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `dns` varchar(512) DEFAULT NULL,   `port` int(11) DEFAULT '55944',   `priority` int(11) DEFAULT '0',   `type` int(11) DEFAULT NULL COMMENT '清理类型:1:分区表; 2:直接按时间删除',   `dbname` varchar(64) DEFAULT NULL,   `tbname` varchar(64) DEFAULT NULL,   `colname` varchar(64) DEFAULT NULL COMMENT '清理条件的时间字段: 不支持到毫秒的 unix_timestamp',   `keep_days` int(11) DEFAULT NULL COMMENT '保留多少天的数据',   `del_time_step` int(11) DEFAULT NULL COMMENT '删除的时间步长(每次删除多长时间的数据,单位: 秒)',   `tstep_type` char(1) DEFAULT NULL COMMENT '时间条件字段类型:D/I: date 或者 int 类型(unix_timestamp)',   `del_row_step` int(11) DEFAULT NULL COMMENT '单次删除记录的条数 limit',   `punit` char(1) DEFAULT NULL COMMENT '分区表单位: M:月 D:天',   `pkeepcnt` int(11) DEFAULT NULL COMMENT '保留多少天/月的数据,0表示不删除旧分区',   `pprecnt` int(11) DEFAULT NULL COMMENT '预分配分区数量',   `pperunit` char(1) DEFAULT NULL COMMENT '分区使用的单位:D: 天(to_days)/S:秒(unix_timeStamp)',   `clearwindow_start` time DEFAULT NULL COMMENT '清理窗口开始时间',   `clearwindow_end` time DEFAULT NULL COMMENT '清理窗口结束时间',   `run_sleep` int(11) DEFAULT NULL COMMENT '单次删除后 sleep 的时间(秒)',   `status` tinyint(4) DEFAULT NULL COMMENT '状态: 0：正常; 1: 清理中; 2: 不清理',   `lastsuccess_time` datetime DEFAULT NULL COMMENT '最后完成时间',   `mintime` varchar(64) DEFAULT NULL COMMENT '清理后最小时间',   `join_key` varchar(64) DEFAULT NULL COMMENT '关联副表的key',   `timeunit` char(1) DEFAULT 'S' COMMENT '清理字段为timestamp时, S: 秒; M: 毫秒',   PRIMARY KEY (`id`),   KEY `idx_hostname` (`dns`(128)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2)    从表配置表

CREATE TABLE `autoclear_attach_setting` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `dbname` varchar(64) DEFAULT NULL,   `tbname` varchar(64) DEFAULT NULL,   `pid` int(11) DEFAULT NULL COMMENT '对应主表autoclear_setting的id',   `join_key` varchar(64) DEFAULT NULL COMMENT '关联副表的key',   PRIMARY KEY (`id`),   KEY `pid` (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

3)   日志表

CREATE TABLE `autoclear_log` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `dns` varchar(512) DEFAULT NULL,   `dbname` varchar(64) DEFAULT NULL,   `tbname` varchar(64) DEFAULT NULL,   `starttime` datetime DEFAULT NULL,   `endtime` datetime DEFAULT NULL COMMENT '完成时间',   `rowcount` int(11) DEFAULT '0' COMMENT '删除的行数',   `isattach` tinyint(4) DEFAULT '0' COMMENT '0/1: 是否副表',   `info` text COMMENT '返回信息',   PRIMARY KEY (`id`),   KEY `idx_hostname` (`dns`(128)),   KEY `idx_starttime` (`starttime`),   KEY `idx_tbname` (`tbname`),   KEY `idx_rowcount` (`rowcount`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8