数据API 案例 开发者 关于
掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务

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

背景

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

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

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

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

系统处理流程

程序处理流程如下:

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

功能介绍

  1. 自动备份功能

    (可自行设置,我是配置的定时任务,平台在对接中)

  2. 自动还原功能

    自动下载备份并还原。

  3. SQL及库表自动上报功能

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

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

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

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

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


注:在下面演示过程中以手动形式,可根据公司具体情况设置为自动。

环境介绍

  • 数据库机器:172.16.20.5
  • 备份机器:172.16.20.6
  • 还原机器:172.16.20.7
  • 备份工具:mydumper
  • 编程语言:Shell+Perl
  • 备份传输工具:rsync

部署

1、备份机器rsync部署

对于数据中心做备份之前采取过如下几个方案。我简单概括一下:

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

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

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

重要部分如下:

[back5]

path = /opt/mysql_bak/172.16.20.5

comment = www file

ignore errors

read only = false

list = false

uid = root

gid = root

2、数据库机器和还原机器安装mydumper

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

安装请参考官网:https://launchpad.net/mydumper/+download

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

脚本如下:

#!/bin/bash

Time=$(date +"%Y-%m-%d-%H")

Timeago=$(date +"%Y-%m-%d" --date='2 days ago')

BACK_DIR=/opt/mysql_bak/$Time

source /etc/profile

user=voole_back

pass=voole_back

mkdir $BACK_DIR

mysql=`/usr/bin/which mysql`

mydumper=`/usr/bin/which mydumper`

mysqldump=`/usr/bin/which mysqldump`

l_ip=`/sbin/ifconfig |grep inet| grep -i bcast|cut -d':' -f2|awk '{print $1}'`

$mydumper -u $user -p $pass --trx-consistency-only --regex '^(?!(sys|test|performance_schema))' --triggers --events --routines  -o $BACK_DIR -t 10

cd /opt/mysql_bak

tar -czf mydumper_$Time.tar.gz $Time

file="mydumper_$Time.tar.gz"

sudo  rsync -zrtoapg --progress $file root@172.16.20.6::back5

4、数据中心表结构设计

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

  • 库表汇报的表结构

CREATE TABLE `db_table` (

  `id` int(255) NOT NULL AUTO_INCREMENT,

  `ip` varchar(20) NOT NULL,

  `type` varchar(20) DEFAULT NULL,

  `dbname` varchar(100) DEFAULT NULL,

  `table` varchar(50) DEFAULT NULL,

  `info` varchar(255) DEFAULT NULL,

  `checksum` varchar(255) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `idx_db` (`dbname`),

  KEY `idx_table` (`table`),

  KEY `idx_ip_db` (`ip`,`dbname`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=63131 DEFAULT CHARSET=utf8 COMPRESSION='lz4';
  • SQL 表结构

CREATE TABLE `db_list_sql` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `type` varchar(255) DEFAULT NULL,

  `sql` text,

  `ip` varchar(255) DEFAULT NULL,

  `result` varchar(255) DEFAULT NULL,

  `num` int(11) NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1161970 DEFAULT CHARSET=utf8;

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

比较结果如下:

邮件截图

1058-0.jpg.png


2)还原后数据SQL的校验

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

比较结果如下:

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

未命名1490943984.png

异常截图

1059-0.jpg.png

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

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

  2. 某些表还原异常,数据丢失。(比如我遇到过触发器的情况,表与表有依赖)

  3. 我用从库的备份比对主库的SQL。(有可能从库和主库不一致)

  4. 备份时有丢失的表或记录。(有时备份的命令问题或漏备份)

附件SQL信息

1059-0.jpg.png

8、关于备份的汇报   

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

如下:

1060-0.jpg.png

总结

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

作者介绍

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

Daplus社群.png

原文来自:DBAplus社群

声明:所有来源为“聚合数据”的内容信息,未经本网许可,不得转载!如对内容有异议或投诉,请与我们联系。邮箱:marketing@think-land.com

掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务
备份校验两不误,MySQL自动备份还原校验设计详解
发布:2017-03-31

背景

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

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

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

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

系统处理流程

程序处理流程如下:

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

功能介绍

  1. 自动备份功能

    (可自行设置,我是配置的定时任务,平台在对接中)

  2. 自动还原功能

    自动下载备份并还原。

  3. SQL及库表自动上报功能

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

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

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

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

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


注:在下面演示过程中以手动形式,可根据公司具体情况设置为自动。

环境介绍

  • 数据库机器:172.16.20.5
  • 备份机器:172.16.20.6
  • 还原机器:172.16.20.7
  • 备份工具:mydumper
  • 编程语言:Shell+Perl
  • 备份传输工具:rsync

部署

1、备份机器rsync部署

对于数据中心做备份之前采取过如下几个方案。我简单概括一下:

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

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

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

重要部分如下:

[back5]

path = /opt/mysql_bak/172.16.20.5

comment = www file

ignore errors

read only = false

list = false

uid = root

gid = root

2、数据库机器和还原机器安装mydumper

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

安装请参考官网:https://launchpad.net/mydumper/+download

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

脚本如下:

#!/bin/bash

Time=$(date +"%Y-%m-%d-%H")

Timeago=$(date +"%Y-%m-%d" --date='2 days ago')

BACK_DIR=/opt/mysql_bak/$Time

source /etc/profile

user=voole_back

pass=voole_back

mkdir $BACK_DIR

mysql=`/usr/bin/which mysql`

mydumper=`/usr/bin/which mydumper`

mysqldump=`/usr/bin/which mysqldump`

l_ip=`/sbin/ifconfig |grep inet| grep -i bcast|cut -d':' -f2|awk '{print $1}'`

$mydumper -u $user -p $pass --trx-consistency-only --regex '^(?!(sys|test|performance_schema))' --triggers --events --routines  -o $BACK_DIR -t 10

cd /opt/mysql_bak

tar -czf mydumper_$Time.tar.gz $Time

file="mydumper_$Time.tar.gz"

sudo  rsync -zrtoapg --progress $file root@172.16.20.6::back5

4、数据中心表结构设计

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

  • 库表汇报的表结构

CREATE TABLE `db_table` (

  `id` int(255) NOT NULL AUTO_INCREMENT,

  `ip` varchar(20) NOT NULL,

  `type` varchar(20) DEFAULT NULL,

  `dbname` varchar(100) DEFAULT NULL,

  `table` varchar(50) DEFAULT NULL,

  `info` varchar(255) DEFAULT NULL,

  `checksum` varchar(255) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `idx_db` (`dbname`),

  KEY `idx_table` (`table`),

  KEY `idx_ip_db` (`ip`,`dbname`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=63131 DEFAULT CHARSET=utf8 COMPRESSION='lz4';
  • SQL 表结构

CREATE TABLE `db_list_sql` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `type` varchar(255) DEFAULT NULL,

  `sql` text,

  `ip` varchar(255) DEFAULT NULL,

  `result` varchar(255) DEFAULT NULL,

  `num` int(11) NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1161970 DEFAULT CHARSET=utf8;

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

比较结果如下:

邮件截图

1058-0.jpg.png


2)还原后数据SQL的校验

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

比较结果如下:

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

未命名1490943984.png

异常截图

1059-0.jpg.png

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

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

  2. 某些表还原异常,数据丢失。(比如我遇到过触发器的情况,表与表有依赖)

  3. 我用从库的备份比对主库的SQL。(有可能从库和主库不一致)

  4. 备份时有丢失的表或记录。(有时备份的命令问题或漏备份)

附件SQL信息

1059-0.jpg.png

8、关于备份的汇报   

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

如下:

1060-0.jpg.png

总结

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

作者介绍

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

Daplus社群.png

原文来自:DBAplus社群

声明:所有来源为“聚合数据”的内容信息,未经本网许可,不得转载!如对内容有异议或投诉,请与我们联系。邮箱:marketing@think-land.com

选择想要的接口, 看看能免费获取多少次调用 选择(单选)或填写想要的接口
  • 短信API服务
  • 银行卡四元素校验
  • 身份证实名认证
  • 手机状态查询
  • 三网手机实名制认证(简版)
  • 身份证OCR识别
  • 风险信息查询
  • 企业工商信息
短信API服务
  • 短信API服务
  • 银行卡四元素校验
  • 身份证实名认证
  • 手机状态查询
  • 三网手机实名制认证(简版)
  • 身份证OCR识别
  • 风险信息查询
  • 企业工商信息
  • 确定
选择您的身份
请选择寻找接口的目的
预计每月调用量
请选择预计每月调用量
产品研发的阶段
请选择产品研发的阶段
×

前往领取
×
企业用户认证,
可获得1000次免费调用
注册登录 > 企业账户认证 > 领取接口包
企业用户认证领取接口包 立即领取
× 企业用户认证,
可获得1000次免费调用,立即领取>
数 据 驱 动 未 来
Data Drives The Future