MySQL数据库使用XtraBackup备份实现示例(2)

ps -ef | grep /home/mysql/bin/mysqld
if [ $? -ne 0 ];then
        echo "MySQL may not working correctly, can not find pid for mysql." >>$HOMEDIR/logs/$logfile
        exit 7
fi

#确认增量备份所依赖的全量备份最新的目录
basedir=`ls -lrt /opt/DB_backup/full_bakcup | tail -1 | awk '{print $9}'`
#判定如果该主机未执行过全灵备份，则执行全量备份，取消增量备份
if [ "$basedir" == "" ];then
    echo "This DB dosen't has a full backup before, cannot execute incremental backup." >>$HOMEDIR/logs/$logfile
    echo "Begin to execute full backup." >>$HOMEDIR/logs/$logfile
    sh /opt/DB_backup/shell/full_backup.sh
    if [ $? -eq 0 ];then
        echo "The first full backup on this server is successful, the incremental backup will begin at tomorrow.">>$HOMEDIR/logs/$logfile
        exit
    fi
    exit 77
fi

#确认该主机已存在的全量备份是否早于7天,如果不是7天内的全量备份,则执行全量备份.
T1=`date +"%Y-%m-%d"`
T2=`echo "$basedir" |awk -F_ '{print $1}'`
Day1=`date +%s -d "$T1"`
Day2=`date +%s -d "$T2"`
Day3=$((($Day1-$Day2)/86400))
if [ $Day3 -gt 7 ];then
    echo "The lastest full backup is 7 days ago, begin to execute full backup." >>$HOMEDIR/logs/$logfile
    sh /opt/DB_backup/shell/full_backup.sh
    if [ $? -eq 0 ];then
        echo "exec full backup successful, pls check full backup logs.">>$HOMEDIR/logs/$logfile
        exit
    fi
    exit 99
fi

#将全量备份的目录打印到日志
echo "The basedir is $basedir" >> $HOMEDIR/logs/$logfile

#执行增量备份
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password="abc@123" --incremental --incremental-basedir=/opt/DB_backup/full_bakcup/$basedir /opt/DB_backup/incremental_backup >> $HOMEDIR/logs/$logfile

[ $? -eq 0 ] && echo "Incremental backup successful." >> $HOMEDIR/logs/$logfile

3，恢复方案

　　数据的恢复操作是比较简单的

　　全量恢复

#先prepare，利用--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
innobackupex --apply-log /opt/DB_backup/full_bakcup/2019-05-09_10-45-32

#还原操作需要清空源库的所有数据，并删除原始数据目录下的数据文件
mysql> drop database xxx;

rm -rf ib_logfile0 ib_logfile1 ibdata1

#恢复命令
innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /opt/DB_backup/full_bakcup/2019-05-09_10-45-32

　　增量恢复

#如果是数据只是部分数据有损，在确定有损数据范围后，可直接还原增量即可
innobackupex --apply-log /opt/DB_backup/full_bakcup/2019-05-09_10-45-32 --incremental-dir=/home/DB_backup/incremental_backup/2019-05-11_03-00-00

#如果是数据全部损毁，执行如下操作
#1，先还原全量
innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /opt/DB_backup/full_bakcup/2019-05-09_10-45-32
#2，在还原增量
innobackupex --apply-log /opt/DB_backup/full_bakcup/2019-05-09_10-45-32 --incremental-dir=/home/DB_backup/incremental_backup/2019-05-11_03-00-00

Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx