关于冷热数据备份和恢复的思考

数据库：Mysql InnoDB

目标：备份指定数据表指定时间段的全字段数据到数据文件，并删除掉数据库中已备份数据

说明：这里说的数据备份不是灾备，而且通过备份基本不会使用的数据来缓解数据库压力，要求备份的数据是可以恢复的

持久化存储：文本格式的数据文件，存储于oss中

思考：不同表中可能含有多个时间字段，可能的组合如下

• update_time
• create_time & update_time
• create_time & update_time & 业务时间（由程序插入，可能是datetime可能是date可能是timestamp）

一般数据表设计时都会带上创建时间和修改时间。

• 数据一般都是快速增长，较少或者几乎不更新，属于记录型的数据

以下数据表都是示例表

探索replace导致的时间变化

由于之前的数据库数据插入时，不同项目可能都使用了replace，因此需要看下replace的影响

创建测试表tb_user

insert插入数据

INSERT INTO `tb_user`(`mail`, `username`, `password`) VALUES('572924509@qq.com', 'suncle', '123456');

replace更新数据

REPLACE INTO `tb_user`(`mail`, `username`, `password`) VALUES('572924509@qq.com', 'suncle', 'abcdef');

replace操作是先删除已存在的数据然后插入新的数据，因此id，create_time，update_time都变化了。

另外，当插入数据失败时（比如遇到唯一键冲突），主键id也会自动增大，此处不验证。

探索insert时自动更新字段的变化

INSERT INTO `tb_user`(`mail`, `username`, `password`, `create_time`, `update_time`) VALUES('5729245012@qq.com', 'suncle1', '123456', '2018-02-02 00:00:00', '2018-02-02 00:00:01');

create_time和update_time都会自动更新，默认值分别为：

• create_time： CURRENT_TIMESTAMP
• update_time： CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

指定两个自动更新字段后，该字段的结果为指定值，而不是自动更新的值，即手动插入什么就是什么。

备份方案列举比较

从备份的简洁性和恢复的便利性来比较

关于时间的获取：程序插入时间，update_time两者时间取最大值表示这条数据的最后修改时间

• create_time，update_time字段都没有索引

时间维度

基于主键id

备份

查询1000条数据，如果要从指定id开始，则绑定min_id

SELECT * FROM tb_stream_computer_concurrency WHERE id > :min_id LIMIT 1000;

然后用获取到的最大的id去查询所有时间字段，并通过程序取最大值

SELECT `time`, `create_time`, `update_time` AS max_time FROM tb_stream_computer_concurrency WHERE id = :max_id

具体步骤：每次1000条数据，没有处理到指定时间则继续循环处理后面1000条，如果最后一条数据最大时间超过指定时间，则基于主键id进行二分查找找到小于指定时间并且最接近指定时间的数据，处理完成。

清理

当次备份结束，需要开始clean数据库，可以根据id直接批量删除，直至删除结束

DELETE FROM tb_stream_computer_concurrency WHERE id < :max_id LIMIT 1000

为了保证数据一致性，之前处理的数据不能立马删除，因此采用下面的处理方式不合理，速度慢

SELECT * FROM tb_stream_computer_concurrency WHERE id < :max_id AND `time` < '2017-08-01 15:30:00' AND update_time < '2017-08-01 15:30:00'

假设’2017-08-01 15:30:00’为指定的时间，max_id为最后一次处理的max_id

优缺点

优点：

1. 主键有索引，所有基于id的sql都不会进行全表扫描，速度较快
2. 使用二分查找的方法毕竟限制时间点能最大程度的满足时间限制的要求

缺点：

1. 二分查找时查找到的最终的id对应的时间有多条数据，需要排除掉这几条数据，从尾部开始做一个filter
2. 此方案适合少更新的数据，对于id小，但是时间大的数据可能会出现误备份

基于指定时刻

比如60天，找到指定时刻之前的所有数据中时间最小的数据：

SELECT MIN(`time`), MIN(`update_time`) FROM `tb_stream_computer_concurrency`

然后从最小的时间开始按照基于时间段的方法批次处理数据（查找sql速度太慢），直到处理到指定时刻

基于指定时间段

寻找位于时间段内的数据进行批次备份

SELECT * FROM `tb_stream_computer_concurrency` WHERE `time` > :min_time AND `time` < :max_time AND `id` > :min_id ORDER BY `date_time`,`id` LIMIT 1000

所有数据备份完成按照时间段进行删除，删除方式如下

DELETE FROM `tb_stream_computer_concurrency` WHERE `time` < :max_time LIMIT 100

基于指定空间阈值

比如备份mysql数据空间占用的大小指定空间最高阈值之下

对于阿里云rds可以调用接口判断当前数据库已占用空间大小，也可以使用sql查询数据库表的占用空间

SELECT DATA_LENGTH+INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='qk_real_time_data' AND TABLE_NAME='tb_stream_computer_concurrency'

查询结果单位为字节Byte，转化为M需要除以2个1024。

然后按照基于主键id的方式进行备份删除，直至数据表占用空间降到指定阈值以下即可停止当次处理。

业务层唯一标识维度

即基于唯一标识live_id，取得最老的一个live_id，然后根据live_id查询所有的指定时间之前的记录，进行备份和清理

SELECT live_id FROM tb_stream_computer_concurrency ORDER BY id LIMIT 1;
SELECT * FROM tb_stream_computer_concurrency WHERE live_id=:live_id

对查询出来的数据进行备份，如果担心数据量过大，可以对查询出来的数据进行limit分批备份清理。此方案的速度较快。

混合维度

基于一定时间段内的唯一标识对应的所有记录进行备份（查询时有索引的在前）

SELECT * FROM tb_stream_computer_concurrency WHERE live_id=:live_id AND GREATEST(`time`, `create_time`, `update_time`) >= :datetime1 AND GREATEST(`time`, `create_time`, `update_time`) < :datetime2

如果去掉偏移区间，速度会更快

备份方案结论

1. 在各个时间字段没有加索引的情况下使用基于主键id的方式备份数据库数据是最合适的
2. 在各个时间字段加了索引的情况下可以使用基于时间点和时间段的方式处理，这样程序更简洁，逻辑更清楚
3. 基于指定空间阈值只有在确实需要此种处理时采取执行，具体底层选择基于什么处理按第一点和第二点分析
4. 不考虑oss文件数量较多，最推荐的方案是基于业务层的唯一标识，便于精准恢复

恢复

oss存储时的文件名，备份时的文件名决定恢复时的策略

主要两种：

• 基于时间段恢复数据，可以是按天，周，月为单位
• 基于唯一标识信息恢复数据，此处为live_id

时间维度

oss文件名称：OssPrefix/EnvName/Datetime

获取指定前缀的所有文件解析即可，如果要做到精确恢复，需要逐个文件下载再分析。

业务层唯一标识维度

在直播业务场景中，直播id即live_id是可以唯一标识一场活动的，因此基于live_id恢复是能达到精准恢复的。当然对于不同的表，如果数据量快速增长，可以肯定都是有业务层的唯一标识的。因此对于不同的表是可以基于唯一标识进行备份恢复的。

需要确认唯一标识的数据量，因为一个唯一标识就会对应一个oss文件。

oss文件名称：OssPrefix/EnvName/UniqueKey/UUID

具体的uuid可以随意指定，具有唯一标示性即可，建议使用当前datetime和32位的uuid组成即可

如果业务层唯一标识在不同的环境中也是全局唯一的，那么就可以省掉EnvName这个前缀

如果需要恢复指定时间段时的数据则需要遍历所有的UniqueKey并匹配UUID，但是由于恢复操作较少，对于oss和数据库的操作都不会太多，因此恢复指定时间段的数据也是可以实现的

混合维度

oss文件命名时同时考虑时间段和唯一标识，时间段量小，因此时间段筛选在前，唯一标识在后

oss文件名称：OssPrefix/EnvName/Datetime/UniqueKey/UUID

恢复指定id时需要遍历所有的时间前缀，然后找对应的UniqueKey

总结

兼顾到数据备份和基于唯一标识恢复基于时间恢复的精确度，因此本次采用业务层唯一标识维度和时间维度（按天为段）混合的方式，主要优势在于基于时间恢复可以很快，基于唯一标识的恢复虽然复杂一点但是也较快

（即需要在oss文件名中体现时间段和唯一标识，时间段长度固定，用结束时间表示）

最终的执行方式：

• 备份自动执行
• 恢复提供按业务唯一标识和时间段，分别提供接口

后续如果恢复要求较高，改用外部表的方式重写

其他

1. 对于mysql千万级别的表，如果冷热数据明显，则可以直接使用本篇文章中讨论的备份方法备份到oss上
2. 除了直接全部持久化冷数据的方式之外，可以采用外部表的方案，比如冷数据转存RDS PostgreSQL版 + OSS， 此方案在恢复时优于全冷备的方式，但结构也较为复杂，但在恢复时较为迅速
3. 除分库分表，分区表，外部表外还有历史拉链表和闪回数据归档FDA等方式（饮水大神介绍）

参考：

1. 云栖社区-云上如何做冷热数据分离