mysql 数据归档经验洽谈

归档对于DBA来说是一个非常严肃的话题，但是对于开发来说可能就没有那么的重视，最近我接到开发经理的需求说要归档两个月以前的短信日志；

在开发和开发经理看来，短信下发了就下发了，超过60天的数据已经处于完全无用状态，属于可丢弃数据；

 

需求到我这里，我给了两个方案，1、做一个归档数据库，2、文本形式归档

其中做归档数据库肯定是比较复杂的，原因有：

1、考虑整个平台的通用性，可定要慎重的选型数据库

2、一旦选型好了需要搭建、测试、做集群高可用等等前期大量工作

3、当归档数据库集群架构好了以后，数据转移之后的存储形式，如果不和生产完全一致就需要打tag作为标记

4、一旦开始数据转移，再清掉数据之前，必须要考虑数据一致性校验，确保转移的数据和原始数据完全一致

5、需要考虑万一生产要用，数据快速迁回方法

 

以上几点中4和5是不管以什么形式做归档，都需要考虑的问题

而第5点中，常会出现备份呢的数据当前表结构是A，过半年表结构有修改，再过半年需要把这种原始数据还回去的方法，如果是以文档形式做归档就比较烦了；

 

数据的一致性校验，对于数据库到文档这一块目前也没有完美的方案可以解决，考虑这种场景常见的就是前端会把html代码直接存在字段里面，或有些特殊字符导致还原的时候无法按整格式导入；

所以再导出之后的一致性校验还是很有必要的；否则就可能出现还原数据的时候有数据丢失；

 

我是肯定偏向于做归档库的实现的，不管是同步、数据标记、一致性校验、快速恢复都能非常快速可靠的实现

然而我们的开发经理完全没有这种意识，所以按照他们速度的要求，以文档的形式进行归档，当然其中数据可能有丢失风险和不能快速还原的风险都会明确告知他们；

 

尽管已文档形式，我还是做了两次方法的实施：

1、写python脚本通过csv模块保存为csv

2、通过percona的pt-archiver进行数据导出成文本

 

如果用python3，处理mysql主要注意一点：

    用cursor的流式读取数据，别直接用cursor方法，区别如下：

    常用非流式读取，一次性加载全部

    cursor = connection.cursor()

 

    流式读取，可以限定每次读取长度，游标会记录当前位置循环读取，每次读取完后会自动读取下一批数据

    cursor = conn.cursor(pymysql.cursors.SSDictCursor)

 

    很明显流式读取对于数据归档来说是非常有必要的，常规操作数据库基本都是少数量数据，所以一次游标读取全部没有太大关系，归档经常会遇到加载一整张表的情况，如果遇到大表

    比如我这次备份的表单表40G，一次性读取会崩掉整台机器；

 

如果用pt-archiver就会方便很多，他的参数中可以指定流式读取行数

./pt-archiver --source h=172.16.24.31,P=3310,u=admin,p='asdqzaa!@#',D=sms_db,t=%s --file '/data/backup_dbsms_to_file/%Y-%m-%d-%D.%t' --charset=UTF8 --where '1=1' --nosafe-auto-increment --progress 10000 --limit 10000 --txn-size 10000 --statistics --no-delete

 

#nosafe-auto-increment  该参数取消保留max(id)不迁移的设定

而后面的10000就是流式读取每次执行的行数，一定要加--no-delete,否则迁移了就会给你删数据，数据的清除一定要单独拿出来做，再此之前的数据一致性校验都需要做一遍才能删

 

注意pt-archiver只能导出，需要导入还是要用load data的形式，pt-archiver默认是有做数据一致性校验的，但官网的描述偏向于数据库同步到数据库，而非数据库到文档

所以不管已自写脚本还是工具，备份完一份文件后请打开看下，文档数据行数和原数据库是否一致，最后一行和第一行数据是否有差异；