本文主要向大家介绍了MySQL数据库之基于XtraBackup的备份有效性检查脚本 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
备份存放:通过XtraBackup的流式备份,将备份异地存放到备份服务器上。
备份策略:周日全备,周一到周六增备。
目前缺漏:这些备份数据未能实现有效性检查,无法探知这些备份是否具有可用性,需要通过一定的机制实现有效性检测。
目前在备份机的备份文件列表如下:drwxr-xr-x 18 mysql mysql 4096 Apr 9 03:28 mysql01_20170409_023001_fulldrwxr-xr-x 18 mysql mysql 4096 Apr 10 03:25 mysql01_20170410_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 11 03:26 mysql01_20170411_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 12 03:25 mysql01_20170412_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 13 03:26 mysql01_20170413_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 14 03:26 mysql01_20170414_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 15 03:27 mysql01_20170415_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 16 03:29 mysql01_20170416_023001_fulldrwxr-xr-x 18 mysql mysql 4096 Apr 17 03:26 mysql01_20170417_023001_incr
其中full结尾的说明当天是全备的,incr结尾的说明当天是增备的。
脚本实现逻辑:自动恢复全备数据,并依次恢复其余的增备数据到全备数据中,最后将恢复完毕的全备数据用mysqld拉起来,检查MySQL的错误日志是否有异常报错来判断恢复是否正常。
vim dbrecover.sh#!/bin/shif [ $# -ne 1 ]then echo "usage: `basename $0` [mysql01|mysql02]" exit 1fihostname=$1today=`date +%Y%m%d`sh /home/mysql/shell/mysql_recover.sh $hostname $today
vim mysql_recover.sh#!/bin/shif [ $# -ne 2 ]then echo "usage: `basename $0` [mysql01|mysql02|mysql03] 20170501 " exit 1fihostname=$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 }getdirif [ $week -eq "$n1" ]then full=`cat $fullname` full_recover $fullelse incr=`cat $incrname` full=`cat $fullname` incr_recover $incr $full if [ $week -eq "$n2" ] then mysqlrecover $full fifitime2=`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_testsocket=/mysqlbackup/mysql_test/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/mysqlbackup/mysql_test/mysqld.logpid-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:当次恢复备份文件记录的简要日志信息
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!