Percona XtraBackup全备与增量备份脚本

1、全备脚本

#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="root"
MYSQL_CNF="/mysql/data/3306/my.cnf"

BAK_FULL_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlfullbackup
BAK_LOG="$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/backup.log"

if [[ ! -d ${BAK_FULL_DIR} ]]; then
    mkdir -p ${BAK_FULL_DIR}
fi

xtrabackup --defaults-file=${MYSQL_CNF} --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --backup --stream=xbstream --extra-lsndir="${BAK_FULL_DIR}/LSN_INFO" --target-dir=${BAK_FULL_DIR} --parallel=2 1> "${BAK_FULL_DIR}/mysql_backup_full.xbstream" 2>> ${BAK_LOG}

2、增量脚本,1h一次

#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="root"
MYSQL_CNF="/mysql/data/3306/my.cnf"

BAK_FULL_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlfullbackup
BAK_LOG="$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/backup.log"

ti=`(date +%H)`
ti1=$((10#$ti - 0))
ti2=$((ti1 - 1))

#BASE_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti2}
INC_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti1}


if [[ ! -d ${INC_DIR} ]]; then
    mkdir -p ${INC_DIR}
fi

if [[ ${ti2} -eq 0 ]];then
    BASE_DIR=${BAK_FULL_DIR}
else
    BASE_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti2}
fi

xtrabackup --defaults-file=${MYSQL_CNF} \
           --user=${MYSQL_USERNAME} \
           --password=${MYSQL_PASSWORD} \
           --backup --stream=xbstream \
           --target-dir=${INC_DIR} \
           --incremental-basedir="${BASE_DIR}/LSN_INFO" \
           --extra-lsndir="${INC_DIR}/LSN_INFO" > "${INC_DIR}/mysqlincbackup${ti1}.xbstream" 2>> ${BAK_LOG}

3、清理脚本

#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"

find ${BAK_DIR_ROOT} -type d -mtime +7 -name "mysqlbackup*" -exec rm -rf {} \;

4、crontab任务

5 0 * * */bin/bash  /mysql/backup/fullbackup.sh
5 1-23 * * */bin/bash  /mysql/backup/incrbackup.sh
30 0 * * */bin/bash  /mysql/backup/cleanbackup.sh

5、恢复脚本 ./恢复脚本.sh > ircrestore.log 2>&1

#!/bin/bash

#xbstream fullbackup 全量的先恢复
cd mysqlfullbackup
xbstream -x < mysql_backup_full.xbstream
cd ..
#xbstream incbackup 增量的
numdirall=`ls -l |grep -c "^d"`
ti1=$((10#$numdirall - 0))
numdir=$((ti1 - 1))
#echo ${numdir}

for ((i=1;i<=${numdir};i++))
 do
   cd mysqlincbackup${i}
   xbstream -x < mysqlincbackup${i}.xbstream
   sleep 2
   cd ..
 done
##prepare##
#1.prepare fullbackup
xtrabackup --prepare --apply-log-only --target-dir=mysqlfullbackup

#2.prepare incbackup
for ((j=1;j<${numdir};j++))
 do
   xtrabackup --prepare --apply-log-only --target-dir=mysqlfullbackup --incremental-dir=mysqlincbackup${j}
   sleep 2
 done

#3.prepare last incbackup
xtrabackup --prepare --target-dir=mysqlfullbackup --incremental-dir=mysqlincbackup${numdir}

#4.prepare all data fullbackup
xtrabackup --prepare --target-dir=mysqlfullbackup

##rsync data
rsync -avrP mysqlfullbackup/* --exclude='xtrabackup_*' /mysql/data/3306/data

#edit privileges
chown -R mysql:mysql /mysql/data/3306/data/*
chmod -R 775 /mysql/data/3306/data/*