基于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的错误日志是否有异常报错来判断恢复是否正常。

    vim dbrecover.sh

#!/bin/sh

if [ $# -ne 1 ]

then

    echo "usage: `basename $0` [mysql01|mysql02]"

    exit 1

fi

hostname=$1

today=`date +%Y%m%d`

sh /home/mysql/shell/mysql_recover.sh $hostname $today

    vim mysql_recover.sh

#!/bin/sh

if [ $# -ne 2 ]

then

    echo "usage: `basename $0` [mysql01|mysql02|mysql03] 20170501 "

    exit 1

fi

hostname=$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=`grep datadir /etc/my.cnf|awk -F \= '{print $NF}'`

errlog=`grep log-error /etc/my.cnf|awk -F \= '{print $NF}'`

n1="0" ##周几做热备，周一到周六为1~6，周日为0。

n2="6" ##周几最后一次增备，周一到周六为1~6，周日为0。

mkdir $logdir ##创建日志目录

function getdir()

{

if [ $week -eq "$n1" ]

then

    fulldir=`find /mysqlbackup/databak/${hostname}/ -type d -name "*${today}*full"`

    #/mysqlbackup/databak/mysql01/mysql01_20170430_023001_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` 

    #/mysqlbackup/databak/mysql01/mysql01_20170429_023001_incr

    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

}

function uncompress()

{

    dir=$1

    /usr/bin/innobackupex --decompress --parallel=8 $dir >>$logdir/uncompress_${timestamp}.log 2>&1

    success_flag=`cat $logdir/uncompress_${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"`

    if [ -n "$success_flag" ] 

          then

            echo "the full dbbackup $fullbakdir recovery is success!" >> $logdir/recover_${timestamp}.log

          else

            echo "the full dbbackup $fullbakdir 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

    success_flag=`cat $logdir/incr_recover_${timestamp}.log|grep "innobackupex: completed OK"`

        if [ -n "$success_flag" ] 

        then

                echo "the incr dbbackup $incrbakdir recovery is success!" >> $logdir/recover_${timestamp}.log

        else

                echo "the incr dbbackup $incrbakdir recovery is fail!" >> $logdir/recover_${timestamp}.log

                exit 1 

        fi

}

function mysqlrecover()

{

    fullbakdir=$1    

    #uncompress $fullbakdir

    rm -fr $datadir

    /bin/ln -s $fullbakdir  $datadir

    chown -R mysql:mysql $datadir 

    chown -R mysql:mysql $fullbakdir

    /sbin/service mysqld start

    error_flag=`grep -i error $errlog`

        if [ -z "$error_flag" ]

        then

                echo "the mysqld don't report error, mysql recover is success!" >> $logdir/recover_${timestamp}.log

        else

                echo "the mysqld report error, mysql recover is fail, please check!" >> $logdir/recover_${timestamp}.log

                exit 1

        fi

    /sbin/service mysqld stop

}

getdir

if [ $week -eq "$n1" ]

then

    full=`cat $fullname`

    full_recover $full

else

    incr=`cat $incrname`

    full=`cat $fullname`

    incr_recover $incr $full

    if [ $week -eq "$n2" ]

    then

        mysqlrecover $full

    fi

fi

time2=`date +%s`

times=$((${time2}-${time1}))

echo "it takes $times seconds to finish the recover!!!" >> $logdir/recover_${timestamp}.log

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

cat /etc/my.cnf

[mysqld]

datadir=/mysqlbackup/mysql_test

socket=/mysqlbackup/mysql_test/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/mysqlbackup/mysql_test/mysqld.log

pid-file=/mysqlbackup/mysql_test/mysqld.pid

    生成的日志文件如下：

-rw-r--r-- 1 root root 613918 May  4 09:34 incr_recover_20170504092501.log

-rw-r--r-- 1 root root   307 May  4 09:34 recover_20170504092501.log

-rw-r--r-- 1 root root 259310 May  4 09:26 uncompress_20170504092501.log

其中：

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

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

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

©著作权归作者所有：来自51CTO博客作者Jenkin_lin的原创作品，如需转载，请注明出处，否则将追究法律责任