四、Percona XtraBackup自动备份脚本

全量脚本

#!/bin/bash
# set -e
# 1.声明数据库账号信息等
user=root
password=*******
LEAVE_DAY_AGO=$(date -d "-30 day" +'%s' )
# 2.动态创建备份文件的临时存储目录
[ -d ${backup_basedir}/backupdb ]|| mkdir -p ${backup_basedir}/backupdb &>>${backup_basedir}/backupdb/backupdb_log.log
[ -d ${backup_basedir}/backup_data/db ]|| mkdir -p ${backup_basedir}/backup_data/db &>>${backup_basedir}/backupdb/backupdb_log.log
echo "start copying ..." &>>${backup_basedir}/backupdb/backupdb_log.log

# 3.执行备份命令开始数据备份
/usr/bin/innobackupex --user="$user" --password="$password" ${backup_basedir}/backup_data/db >${backup_basedir}/backupdb/backupdb_log.log 2>&1
# 4. 校验备份结果是否通过
if [ "$?" != "0" ]; then
    # 4.2 备份失败
    echo " backup db error !" &>>${backup_basedir}/backupdb/backupdb_log.log
    exit 1;
else
    # 4.2 备份成功
    echo " end copied! ..." &>>${backup_basedir}/backupdb/backupdb_log.log
    echo `ls -lt ${backup_basedir}/backup_data/db |grep - |head -n 1 |awk '{print $9}'` >${backup_basedir}/backup_data/last_backup_dir_name
fi;

# 5. 删除 ${LEAVE_DAY_AGO} 天前的备份目录及子目录、文件
path="${backup_basedir}/backup_data/db"
files=$(ls $path)
for filename in $files
do
    file=(${filename:0:10})
    if echo $file | grep -Eq "[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}" && date -d $file+%Y%m%d > dev/null 2>&1;then
        echo $filename 'format is right date' &>>${backup_basedir}/backupdb/backupdb_log.log
        file_time=$(date -d $file +'%s' )

        #校验备份记录是否已过期
        if [$file_time -lt $LEAVE_DAY_AGO ];then
            rm -rf $path/$filename*
            echo " remove $filename directory " &>>${backup_basedir}/backupdb/backupdb_log.log
        else
            echo "$filename is valid backup directory" &>>${backup_basedir}/backupdb/backupdb_log.log
        fi;
    else
        echo "date format is invalid" $filename &>>${backup_basedir}/backupdb/backupdb_log.log
    fi;
done
复制

增量脚本

#!/bin/bash
# set -e

# 1.声明数据库账号信息等
user=root
password=*******
LEAVE_DAY_AGO=$(date -d "-30 day" +'%s' )

# 2.动态创建临时文件临时存储目录
[ -d ${backup_basedir}/backupdb ]|| mkdir -p ${backup_basedir}/backupdb &>>${backup_basedir}/backupdb/backupdb_increment_log.log
[ -d ${backup_basedir}/backup_data/db/increment ]|| mkdir -p ${backup_basedir}/backup_data/db/increment &>>${backup_basedir}/backupdb/backupdb_increment_log.log
echo "increment start copying ..." &>>${backup_basedir}/backupdb/backupdb_increment_log.log

# 3.搜素并获取上一次全量备份的目录地址
last_backup_dir_name=`cat ${backup_basedir}/backup_data/last_backup_dir_name`

# 4.执行备份命令开始数据备份
/usr/bin/innobackupex --user="$user" --password="$password" --incremental ${backup_basedir}/backup_data/db/increment/ --incremental-basedir ${backup_basedir}/backup_data/db/"$last_backup_dir_name" >${backup_basedir}/backupdb/backupdb_increment_log.log 2>&1

# 5.校验备份结果是否超过
if [ "$?" != "0" ]; then
    # 4.2 备份失败
    echo " increment backup db error !" &>>${backup_basedir}/backupdb/backupdb_increment_log.log
    exit 1;
else
    # 4.2 备份成功
    echo "increment end copied! ..." &>>${backup_basedir}/backupdb/backupdb_increment_log.log
fi;

# 6. 删除 ${LEAVE_DAY_AGO} 天前的备份目录及子目录、文件
path="${backup_basedir}/backup_data/db/increment"
files=$(ls $path)
for filename in $files
do
    file=(${filename:0:10})
    if echo $file | grep -Eq "[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}" && date -d $file+%Y%m%d > dev/null 2>&1;then
        echo $filename 'format is right date' &>>${backup_basedir}/backupdb/backupdb_increment_log.log
        file_time=$(date -d $file +'%s' )

        #校验备份记录是否已过期
        if [$file_time -lt $LEAVE_DAY_AGO ];then
            rm -rf $path/$filename*
            echo " remove $filename directory " &>>${backup_basedir}/backupdb/backupdb_increment_log.log
        else
            echo "$filename is valid backup directory" &>>${backup_basedir}/backupdb/backupdb_increment_log.log
        fi;
    else
        echo "date format is invalid" $filename &>>${backup_basedir}/backupdb/backupdb_increment_log.log
    fi;
done
复制

设置crontab 调度执行 crontab -e

# 每周一凌晨4点执行全量备份
0 4 * * 1 sh ${backup_basedir}/backupdb/db_backup.sh >>${backup_basedir}/backupdb/backupdb_log.log
# 每天凌晨2点执行增量备份
0 2 * * * sh ${backup_basedir}/backupdb/db_backup_increment.sh >>${backup_basedir}/backupdb/backupdb_increment_log.log
复制