基于XtraBackup的备份有效性检查脚本

生产环境的MySQL是通过crontab的方式，定时调度热备脚本备份数据。目前是通过XtraBackup软件实现热备。关于热备脚本方面，请查看博客《使用shell实现mysql自动全备、增备&日志备份》：http://linzhijian.blog.51cto.com/1047212/1891745 ，这里不再展开说明。
备份存放：通过XtraBackup的流式备份，将备份异地存放到备份服务器上。
备份策略：周日全备，周一到周六增备。
目前缺漏：这些备份数据未能实现有效性检查，无法探知这些备份是否具有可用性，需要通过一定的机制实现有效性检测。
目前在备份机的备份文件列表如下：
drwxr-xr-x 18 mysql mysql 4096 Apr 9 03:28 mysql01_20170409_023001_full
drwxr-xr-x 18 mysql mysql 4096 Apr 10 03:25 mysql01_20170410_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 11 03:26 mysql01_20170411_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 12 03:25 mysql01_20170412_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 13 03:26 mysql01_20170413_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 14 03:26 mysql01_20170414_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 15 03:27 mysql01_20170415_023001_incr
drwxr-xr-x 18 mysql mysql 4096 Apr 16 03:29 mysql01_20170416_023001_full
drwxr-xr-x 18 mysql mysql 4096 Apr 17 03:26 mysql01_20170417_023001_incr
其中full结尾的说明当天是全备的，incr结尾的说明当天是增备的。
脚本实现逻辑：自动恢复全备数据，并依次恢复其余的增备数据到全备数据中，最后将恢复完毕的全备数据用mysqld拉起来，检查MySQL的错误日志是否有异常报错来判断恢复是否正常。
[root@localhost ~]# vim mysql_recover.sh
#!/bin/sh
hostname=$1
week=`date +%w` ##获取当天是周几。
time1=`date +%s`
timestamp=`date +%Y%m%d%H%M%S`
logdir="/home/mysql/log/mysqlrecoverlog/$hostname/"
dir="/mysqlbackup/databak/$hostname/"
fullname="$dir/full_backup_file.txt" ##记录全备的路径名
incrname="$dir/incr_backup_file.txt" ##记录当天要恢复的增备的路径名
today=`date +%Y%m%d`
datadir="/mysqlbackup/mysql_test/$hostname/data"
n1="1" ##周几做热备，周一到周六为1~6，周日为0。
n2="6" ##周几最后一次增备，周一到周六为1~6，周日为0。

###getdir()函数是获取脚本运行当天的热备文件的路径名，并做校验（一天一个备份文件）###
function getdir()
{
if [ $week -eq "$n1" ]
then
fulldir=`find /mysqlbackup/databak/${hostname}/ -type d -name "*${today}*full"`
if [ ! -n "$fulldir" ]
then
echo "the fulldir not exist!!!" >> $logdir/recover_${timestamp}.log
exit 1
fi
num=`find /mysqlbackup/databak/${hostname}/ -type d -name "*${today}*full"|wc -l`
if [ $num -eq "1" ]
then
echo $fulldir > $fullname
else
echo "there are not only full dbbackup in $today, please check!!!" >> $logdir/recover_${timestamp}.log
exit 1
fi

else
incrdir=`find /mysqlbackup/databak/${hostname}/ -type d -name "*${today}*incr"`
if [ ! -n "$incrdir" ]
then
echo "the incrdir not exist!!!" >> $logdir/recover_${timestamp}.log
fi
num=`find /mysqlbackup/databak/${hostname}/ -type d -name "*${today}*incr"|wc -l`
if [ $num -eq "1" ]
then
echo $incrdir > $incrname
else
echo "there are not only incr dbbackup in $today, please check!!!" >> $logdir/recover_${timestamp}.log
exit 1
fi
fi
}

##由于采用流式备份，备份文件需要先行解压。umcompress函数就是解压函数####
function uncompress()
{
dir=$1
/usr/bin/innobackupex --decompress --parallel=8 $dir >>$logdir/uncompress_${timestamp}.log 2>&1
success_flag=`cat $logdir/uncompress_${dir}_${timestamp}.log|grep "completed OK"`
if [ -n success_flag ]
then
echo "$dir decompress sucessfully!" >> $logdir/recover_${timestamp}.log
else
echo "$dir decompress failed " >> $logdir/recover_${timestamp}.log
exit 1
fi
}

###恢复全量备份函数##
function full_recover()
{
fullbakdir=$1
uncompress $fullbakdir
/usr/bin/innobackupex --use-memory=2G --apply-log --redo-only $fullbakdir >>$logdir/full_recover_${timestamp}.log 2>&1
success_flag=`cat $logdir/full_recover_${timestamp}.log|grep "innobackupex: completed OK"`
#failed_flag=`cat $logdir/full_recover_${timestamp}.log|grep "xtrabackup: error:"`
if [ -n "$success_flag" ]
then
echo "the full dbbackup $fullbakdir recovery is success!" >> $logdir/recover_${timestamp}.log
else
echo "the full dbbackup $fullbakdri recovery is fail!" >> $logdir/recover_${timestamp}.log
exit 1
fi
}

###恢复增量备份函数###
function incr_recover()
{
incrbakdir=$1
fullbakdir=$2
uncompress $incrbakdir
uncompress $fullbakdir
if [ $week -ne "$n2" ]
then
/usr/bin/innobackupex --use-memory=2G --apply-log --redo-only --incremental-dir=$incrbakdir $fullbakdir >>$logdir/incr_recover_${timestamp}.log 2>&1
else
/usr/bin/innobackupex --use-memory=2G --apply-log --incremental-dir=$incrbakdir $fullbakdir >>$logdir/incr_recover_${timestamp}.log 2>&1
fi
#/usr/bin/innobackupex --use-memory=2G --apply-log --redo-only --incremental-dir=$incrbakdir $fullbakdir >>$logdir/incr_recover_${timestamp}.log 2>&1
success_flag=`cat $logdir/incr_recover_${timestamp}.log|grep "innobackupex: completed OK"`
#failed_flag=`cat $logdir/incr_recover_${timestamp}.log|grep "xtrabackup: error:"`
if [ -n "$success_flag" ]
then
echo "the incr dbbackup $fullbakdir recovery is success!" >> $logdir/recover_${timestamp}.log
else
echo "the incr dbbackup $fullbakdri recovery is fail!" >> $logdir/recover_${timestamp}.log
exit 1
fi
}

getdir

if [ $week -eq "$n1" ]
then
full=`cat $fullname`
full_recover $full
else
incr=`cat $incrname`
full=`cat $fullname`
incr_recover $incr $full
fi

time2=`date +%s`
times=$((${time2}-${time1}))
echo "it takes $times seconds to finish the recover!!!" >> $logdir/recover_${timestamp}.log
由于目前备份机没有安装MySQL服务，故脚本目前暂未实现通过mysqld启动服务，检查错误日志以验证数据的可用性。这个功能毕竟比较简单，这里就由读者去实现吧。