备份校验两不误，MySQL自动备份还原校验设计详解

作者介绍

庞阔，优朋普乐传媒运维基础部经理。负责数据库运营管理及平台设计开发，监控设计改进，问题跟踪处理，机房网络维护管理，目前四个专利已在专利局申请中。擅长数据库运维管理及Shell、Perl、PHP编写。

 

背景 

 

最近关于数据库故障出现的问题较多，不论大小公司对数据的备份要求都很高，但对校验数据备份的有效性要求更为迫切，很多公司对于自动备份和还原都已经形成体系，但对于还原后的备份有效性校验可能都不太完善，而且目前网上也没有较为完善的检验机制（可能我没找到）。

 

对数据库备份的有效性校验的方法或样例选择，直接关系到备份数据的质量指标。本文将分享我做的一个设计，此设计是直接采用线上执行的SQL提取出select，包括复杂join类型的SQL加上当前存在的库及表信息，提高了备份校验的准确性。

 

这是我在申请数据库相关专利时推演出来的方案，在寻找一个好的校验备份还原后的数据衡量指标，偶然地和备份还原进行结合时出现了这个设计。当数据库实例越来越多时，这个有效性校验的需求会越来越强。

 

下面将简单介绍一下我的校验数据的设计方案，或许它能给你一个思路或想法，当然我也希望能有其他好的方案出来，共同学习。（注：部分信息做了脱敏处理）

 

系统处理流程 

 

程序处理流程如下：

 

 

根据上面的流程图，大致分为5个步骤，有6个脚本程序来完成这个流程，每个步骤其实不是很难，实际中可根据自己的业务特定进行完善，下面我简单介绍此流程中主要的几个功能。

 

功能介绍 

 

SQL及库表自动上报功能

1）上报本机数据库的库表信息，主要用来比对还原后库表信息是否一一对应，如果对应正常，否则异常，进行报警处理。

2）汇报SQL，为保证SQL的真实性，此方法是监听general_log，分析后获取Select 类型SQL，并执行此SQL 降获取到的sql 及查到的值 汇报到数据中心作为样例SQL使用。

还原后库表及SQL自动比对功能

1）还原后自动调用数据库中心获取库表信息，进行一一比对。

2）获取SQL信息进行原来和还原后数据值的匹配校验，如果对应则正常，否则为异常。

NFS：由一块设备进行网络远程挂载，只需安装NFS服务即可，操作简单。但是有个问题就是当NFS服务出现问题或网络中断时你去使用磁盘会出现挂起的现象。

FTP：也用过FTP来做备份服务，但有时会出现登录失败的现象，对于不同目录权限设置较为复杂，不方便维护；上传下载编写脚本也不是太方便。

Rsync：改为Rsync，主要是配置简单，上传下载也简单的多，一条命令即可；对于增量的传输很有用。

 

mydumper第三方开用于对MySQL数据库进行多线程备份和恢复的开源工具。开发人员主要来自MySQL、Facebook和SkySQL公司，目前由Percona公司开发和维护，是Percona Remote DBA项目的重要组成部分；不同于官方的mysqldump、mysqlpump的是对库表备份和还原采用多线程，对于快速备份和恢复是不错的选择；当然还有percona的xtrabackup相当于物理备份的工具，但是耗费空间较大。

 

 

3、数据库上执行备份脚本

 

脚本如下：


 

4、数据中心表结构设计

 

在数据中心创建下面的表，这些表主要用来存储备份时上报的库表信息和SQL信息，用后续步骤还原校验时做提供样例值。

 

库表汇报的表结构

 

 

SQL 表结构

 

 

5、数据库机器上汇报

 

1）库表汇报程序地址：自行下载和修改

https://github.com/kevin6386/db_table_report/blob/master/db_table_report

运行即可。

 

2）SQL汇报程序

程序地址：https://github.com/kevin6386/db_sql_report/blob/master/db_sql_report

运行即可。

 

6、数据库备份还原

 

下载备份并还原(简单分解介绍)：

 

用 rsync 下载备份到本地，并解压

rsync -zrtoapg --progress  root@172.16.20.6::back5/备份文件名 ./

 

恢复命令：

/usr/local/bin/myloader  -u user -p pass -o  -d 备份地址 -t 8

 

7、校验

 

此时才是整个流程设计的重点，针对还原后的数据，怎么做校验才是重要的，而且校验的样例或方法直接关系数据备份有效性的指标。

 

1）还原后数据库表的校验

 

程序地址：https://github.com/kevin6386/db_table_diff/blob/master/db_table_diff

 

比较结果如下：

 

邮件截图

 

2）还原后数据SQL的校验

 

程序地址：https://github.com/kevin6386/db_sql_diff

 

比较结果如下：

 

邮件截图：如果正常则附件会有SQL，否则为空。

 

 

异常截图

 

出现异常有如下几种情况：

备份时和general_log提取有时间的差异；当获取SQL出现在备份前或备份后有数据修改的情况下会出现。（可采用低峰时或很少修改的字段进行提取样例）

 

 

8、关于备份的汇报   

 

我是汇报每天的备份大小及文件名，然后写SQL比对今天的备份和前2天的信息。

 

如下：

 

 

 

总结 

 

设计完这个方案后开始编写分程序花了一段时间，同时感谢我的同事帮我重复测试这个设计方案，发现之前备份还原过程中出现的问题改善了很多，重要的是不用人工去抽取还原后的数据结果。当这个方案固定后基本上很少有人工的参与，减少了人工还原备份和校验备份重复的工作；并且可以准确地知道哪部分有问题，减少了对数据库备份是否正常的担忧。当然还有很多要完善的方面，欢迎有兴趣的朋友在留言区提出建议，一起交流。

原文发布时间为：2017-03-28

本文来自云栖社区合作伙伴DBAplus

mysql中校验一条数据中所有字段是否完整 现在有一个简历查询场景,用户填写的简历信息中,除了个别内容是必填的,其余内容都是非必填,现在规定简历中除了必填项之外只要有一项没有填写的就提示待完善。现在需要查询用户的简历信息是否需要完善。
头大了，Mysql写入数据十几秒后被自动删除了 事情是这样的，在公司内部新开发了一个功能还没有上线，目前部署在测试环境，Node服务会开启一个定时任务，每5分钟会处理好一部分数据写入到mysql数据库中。
zabbix低自动发现--监控mysql多实例（二十九） zabbix低级自动发现监控mysql多实例 1.低级自动发现概念 低级自动发现本身就是一个key，在这个自动发现规则中包含有很多个监控原型，这些的监控原型都是依靠于自动发现规则根据各种过滤器最后匹配到的结果进行监控 低级自动发现经常适用于在同一台机器上有很多重复的监控项，人工操作非常繁重，这时候就可以配置一个低级自动发现，将自动发现做成模板，让对应的主机进行链接，经过一段时间的扫描后会自动的加入到监控中
MySQL毫秒数自动四舍五入了解一下 最近在项目中看到一个问题，就是某个时间通过代码赋值毫秒数，然后再入库MySQL，查询MySQL发现竟然时间的日期变了，究竟是什么原因导致的？让我们来一探究竟。