生产环境的MySQL是通过crontab的方式，定时调度热备脚本备份数据。目前是通过XtraBackup软件实现热备。关于热备脚本方面，请查看我原先的博客《使用shell实现mysql自动全备、增备&日志备份》：http://linzhijian.blog.51cto.com/1047212/1891745 ，这里不再展开说明。

备份存放：通过XtraBackup的流式备份，将备份异地存放到备份服务器上。

备份策略：周日全备，周一到周六增备。

目前缺漏：这些备份数据未能实现有效性检查，无法探知这些备份是否具有可用性，需要通过一定的机制实现有效性检测。

目前在备份机的备份文件列表如下：

drwxr-xr-x18mysqlmysql4096Apr903:28mysql01_20170409_023001_fulldrwxr-xr-x18mysqlmysql4096Apr1003:25mysql01_20170410_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1103:26mysql01_20170411_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1203:25mysql01_20170412_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1303:26mysql01_20170413_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1403:26mysql01_20170414_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1503:27mysql01_20170415_023001_incrdrwxr-xr-x18mysqlmysql4096Apr1603:29mysql01_20170416_023001_fulldrwxr-xr-x18mysqlmysql4096Apr1703:26mysql01_20170417_023001_incr

其中full结尾的说明当天是全备的，incr结尾的说明当天是增备的。

脚本实现逻辑：自动恢复全备数据，并依次恢复其余的增备数据到全备数据中，最后将恢复完毕的全备数据用mysqld拉起来，检查MySQL的错误日志是否有异常报错来判断恢复是否正常。

vim dbrecover.sh

#!/bin/shif[$#-ne1]thenecho"usage:`basename$0`[mysql01|mysql02]"exit1fihostname=$1today=`date+%Y%m%d`sh/home/mysql/shell/mysql_recover.sh$hostname$today

vim mysql_recover.sh

#!/bin/shif[$#-ne2]thenecho"usage:`basename$0`[mysql01|mysql02|mysql03]20170501"exit1fihostname=$1#hostname="mysql02"#today=`date+%Y%m%d`today=$2#week=`date+%w`week=`date-d$today+%w`time1=`date+%s`timestamp=`date+%Y%m%d%H%M%S`logdir="/home/mysql/log/mysqlrecoverlog/$hostname/$timestamp"dir="/mysqlbackup/databak/$hostname/"fullname="$dir/full_backup_file.txt"incrname="$dir/incr_backup_file.txt"datadir=`grepdatadir/etc/my.cnf|awk-F\='{print$NF}'`errlog=`greplog-error/etc/my.cnf|awk-F\='{print$NF}'`n1="0"##周几做热备，周一到周六为1~6，周日为0。n2="6"##周几最后一次增备，周一到周六为1~6，周日为0。mkdir$logdir##创建日志目录functiongetdir(){if[$week-eq"$n1"]thenfulldir=`find/mysqlbackup/databak/${hostname}/-typed-name"*${today}*full"`#/mysqlbackup/databak/mysql01/mysql01_20170430_023001_fullif[!-n"$fulldir"]thenecho"thefulldirnotexist!!!">>$logdir/recover_${timestamp}.logexit1finum=`find/mysqlbackup/databak/${hostname}/-typed-name"*${today}*full"|wc-l`#/mysqlbackup/databak/mysql01/mysql01_20170429_023001_incrif[$num-eq"1"]thenecho$fulldir>$fullnameelseecho"therearenotonlyfulldbbackupin$today,pleasecheck!!!">>$logdir/recover_${timestamp}.logexit1fielseincrdir=`find/mysqlbackup/databak/${hostname}/-typed-name"*${today}*incr"`if[!-n"$incrdir"]thenecho"theincrdirnotexist!!!">>$logdir/recover_${timestamp}.logfinum=`find/mysqlbackup/databak/${hostname}/-typed-name"*${today}*incr"|wc-l`if[$num-eq"1"]thenecho$incrdir>$incrnameelseecho"therearenotonlyincrdbbackupin$today,pleasecheck!!!">>$logdir/recover_${timestamp}.logexit1fifi}functionuncompress(){dir=$1/usr/bin/innobackupex--decompress--parallel=8$dir>>$logdir/uncompress_${timestamp}.log2>&1success_flag=`cat$logdir/uncompress_${timestamp}.log|grep"completedOK"`if[-nsuccess_flag]thenecho"$dirdecompresssucessfully!">>$logdir/recover_${timestamp}.logelseecho"$dirdecompressfailed">>$logdir/recover_${timestamp}.logexit1fi}functionfull_recover(){fullbakdir=$1uncompress$fullbakdir/usr/bin/innobackupex--use-memory=2G--apply-log--redo-only$fullbakdir>>$logdir/full_recover_${timestamp}.log2>&1success_flag=`cat$logdir/full_recover_${timestamp}.log|grep"innobackupex:completedOK"`if[-n"$success_flag"]thenecho"thefulldbbackup$fullbakdirrecoveryissuccess!">>$logdir/recover_${timestamp}.logelseecho"thefulldbbackup$fullbakdirrecoveryisfail!">>$logdir/recover_${timestamp}.logexit1fi}functionincr_recover(){incrbakdir=$1fullbakdir=$2uncompress$incrbakdiruncompress$fullbakdirif[$week-ne"$n2"]then/usr/bin/innobackupex--use-memory=2G--apply-log--redo-only--incremental-dir=$incrbakdir$fullbakdir>>$logdir/incr_recover_${timestamp}.log2>&1else/usr/bin/innobackupex--use-memory=2G--apply-log--incremental-dir=$incrbakdir$fullbakdir>>$logdir/incr_recover_${timestamp}.log2>&1fisuccess_flag=`cat$logdir/incr_recover_${timestamp}.log|grep"innobackupex:completedOK"`if[-n"$success_flag"]thenecho"theincrdbbackup$incrbakdirrecoveryissuccess!">>$logdir/recover_${timestamp}.logelseecho"theincrdbbackup$incrbakdirrecoveryisfail!">>$logdir/recover_${timestamp}.logexit1fi}functionmysqlrecover(){fullbakdir=$1#uncompress$fullbakdirrm-fr$datadir/bin/ln-s$fullbakdir$datadirchown-Rmysql:mysql$datadirchown-Rmysql:mysql$fullbakdir/sbin/servicemysqldstarterror_flag=`grep-ierror$errlog`if[-z"$error_flag"]thenecho"themysqlddon'treporterror,mysqlrecoverissuccess!">>$logdir/recover_${timestamp}.logelseecho"themysqldreporterror,mysqlrecoverisfail,pleasecheck!">>$logdir/recover_${timestamp}.logexit1fi/sbin/servicemysqldstop}getdirif[$week-eq"$n1"]thenfull=`cat$fullname`full_recover$fullelseincr=`cat$incrname`full=`cat$fullname`incr_recover$incr$fullif[$week-eq"$n2"]thenmysqlrecover$fullfifitime2=`date+%s`times=$((${time2}-${time1}))echo"ittakes$timessecondstofinishtherecover!!!">>$logdir/recover_${timestamp}.log

备份机目前采用二进制包安装MySQL的方式，直接上传到/usr/local/mysql目录上，配置好相应的/etc/profile和/etc/my.cnf即可。

cat /etc/my.cnf

[mysqld]datadir=/mysqlbackup/mysql_testsocket=/mysqlbackup/mysql_test/mysql.sockuser=mysql#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityriskssymbolic-links=0[mysqld_safe]log-error=/mysqlbackup/mysql_test/mysqld.logpid-file=/mysqlbackup/mysql_test/mysqld.pid

生成的日志文件如下：

-rw-r--r--1rootroot613918May409:34incr_recover_20170504092501.log-rw-r--r--1rootroot307May409:34recover_20170504092501.log-rw-r--r--1rootroot259310May409:26uncompress_20170504092501.log

其中：

uncompress_20170504092501.log：解压备份文件时产生的日志信息

incr_recover_20170504092501.log：XtraBackup应用备份文件时产生的日志信息

recover_20170504092501.log：当次恢复备份文件记录的简要日志信息