percona-xtrabackup备份

葛海阳
2023-12-01
#!/bin/bash
# 需要安装 percona-xtrabackup
# xtrabackup: https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
# xtrabackup 版本：2.4.24  (RPM安装)
# MySQL 版本: 5.7.36 (RPM安装)
# version: 22.01.17

# 备份服务器 ip
DB_BACKUP_SERVER="localhost"
DB_BACKUP_SERVER_PORT="16036"
# username
DB_BACKUP_USER="backup_user"
# password
DB_BACKUP_USER_PASSWD="2iBa#I9Cc8GH80ky"
# xtrabackup 登录变量
AUTH="--host=${DB_BACKUP_SERVER} --user=${DB_BACKUP_USER} --password=${DB_BACKUP_USER_PASSWD} --port=${DB_BACKUP_SERVER_PORT}"

# MySQL数据目录
DB_DATA_PATH="/data/mysql57"
# 备份目录, 自动备份目录格式: 年/月/日
BD_BACKUP_PATH="/data/mysqlbackup"
# 手动备份目录
DB_MANUAL_BACKUP_PATH="/data/mysqlbackup/manual"
# 手动备份归档目录
DB_ARCHIVE_PATH="${DB_MANUAL_BACKUP_PATH}/archive"
# 配置文件
DB_CONFIG_FILE="/etc/my.cnf"
# backup time
DB_BACKUP_TIME=`date +%Y%m%d_%H%M`

# 备份日志
DB_BACKUP_LOG_PATH="${BD_BACKUP_PATH}/logs"
DB_BACKUP_LOG_FILE="${DB_BACKUP_LOG_PATH}/mysql-backup-$(date +%Y%m).log"

# xtrabackup 日志
XTRABACKUP_LOG_PATH="${DB_BACKUP_LOG_PATH}/xtrabackup"
XTRABACKUP_LOG_FILE="${XTRABACKUP_LOG_PATH}/xtrabackup-$(date +%Y%m%d%H).log"


[ -d ${DB_BACKUP_LOG_PATH} ] || mkdir -p ${DB_BACKUP_LOG_PATH}
[ -d ${XTRABACKUP_LOG_PATH} ] || mkdir -p ${XTRABACKUP_LOG_PATH}
[ -d ${DB_MANUAL_BACKUP_PATH} ] || mkdir -p ${DB_MANUAL_BACKUP_PATH}
[ -d ${DB_ARCHIVE_PATH} ] || mkdir -p ${DB_ARCHIVE_PATH}


checkXtrabackupStatus() {
    # 判断备份情况
    status=`tail -n 20 ${XTRABACKUP_LOG_FILE} |grep -v 'prints "completed OK!' |grep "completed OK" |wc -l`
    if [ ${status} -ge 1 ];then
        echo -e "SUCCESS: status: successful, xtrabackup status: completed OK." >> ${DB_BACKUP_LOG_FILE}
        SUCCESS=`tail -n 5 ${XTRABACKUP_LOG_FILE} |grep -v "IMPORTANT"`
        echo -e "SUCCESS INFO: \n${SUCCESS}" >> ${DB_BACKUP_LOG_FILE}
    else
        echo -e "ERROR: status: failed." >> ${DB_BACKUP_LOG_FILE}
        ERROR=`tail -n 5 ${XTRABACKUP_LOG_FILE} |grep -v "IMPORTANT"`
        echo -e "ERROR INFO: \n${ERROR}" >> ${DB_BACKUP_LOG_FILE}
    fi
    # 打包xtrabackup日志
    cd ${XTRABACKUP_LOG_PATH}
    tar zcf `ls |grep "xtrabackup-$(date +%Y%m%d%H).log" |grep -v ".tar.gz"`.tar.gz `ls |grep "xtrabackup-$(date +%Y%m%d%H).log" |grep -v ".tar.gz"` --remove-files
    echo -e "XTRABACKUP_LOG_FILE: $(ls ${XTRABACKUP_LOG_FILE}.tar.gz)" >> ${DB_BACKUP_LOG_FILE}
}

# 手动全量备份：不指定数据库即全量备
fullBackup() {
    echo -e "################### Manual Full Backups ###################" >> ${DB_BACKUP_LOG_FILE}
    echo -e "Backup Time: $(date "+%Y/%m/%d %H:%M:%S")" >> ${DB_BACKUP_LOG_FILE}

    if [ -d ${DB_MANUAL_BACKUP_PATH}/data ];then
        cd ${DB_MANUAL_BACKUP_PATH}
        mkdir -p data_${DB_BACKUP_TIME}
        mv data data_${DB_BACKUP_TIME}
        INC_DIR=`ls ${DB_MANUAL_BACKUP_PATH} |grep -v "archive"| tail -1`
        if  [[ "${INC_DIR}" =~ "inc_" ]];then
            mv inc_* data_${DB_BACKUP_TIME}
        fi
        tar zcf ${DB_ARCHIVE_PATH}/data_${DB_BACKUP_TIME}.tar.gz data_${DB_BACKUP_TIME} --remove-files
    fi

    echo -e "full backup: ${DB_MANUAL_BACKUP_PATH}/data" >> ${DB_BACKUP_LOG_FILE}
    innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync ${DB_MANUAL_BACKUP_PATH} >> ${XTRABACKUP_LOG_FILE} 2>&1
    cd ${DB_MANUAL_BACKUP_PATH}
    mv -v `ls |grep "20" |grep -v "archive"` data >> ${DB_BACKUP_LOG_FILE}
    
    # 判断Xtrabackup执行状态
    checkXtrabackupStatus

    cd ${DB_MANUAL_BACKUP_PATH}/archive
    # 保留3份手动备份
    RE=3
    FN=`ls *.tar.gz |wc -l`
    [ ${FN} -le ${RE} ] || for f in `ls -crt`;do rm -rvf `ls ${f} |awk -F".tar.gz" '{print$1}'`.tar.gz >> ${DB_BACKUP_LOG_FILE}; let FN=(${FN}-1); if [ ${FN} -eq ${RE} ];then break; fi; done;
}

# 手动增量备份
incrementalBackup() {
    echo -e "################### Manual Incremental Backups ###################" >> ${DB_BACKUP_LOG_FILE}
    echo -e "BackupTime: $(date "+%Y/%m/%d %H:%M:%S")" >> ${DB_BACKUP_LOG_FILE}

    [ -d ${DB_MANUAL_BACKUP_PATH} ] || mkdir -p ${DB_MANUAL_BACKUP_PATH}
    END_DIR=`ls ${DB_MANUAL_BACKUP_PATH} |grep -v "archive"| tail -1`

    # 如果数据目录中只有data，那么就针对data目录进行增量备份
    if [ "${END_DIR}" == "data" ];then
        echo -e "incremental backup: ${DB_MANUAL_BACKUP_PATH}/inc_1" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync --incremental ${DB_MANUAL_BACKUP_PATH} --incremental-basedir=${DB_MANUAL_BACKUP_PATH}/data >> ${XTRABACKUP_LOG_FILE} 2>&1
        cd ${DB_MANUAL_BACKUP_PATH}
        mv -v `ls |grep $(date +%Y-%m-%d)` inc_1 >> ${DB_BACKUP_LOG_FILE}
    #如果数据目录中已经含有inc的目录，那么根据inc数字最大的目录进行增量备份
    elif  [[ "${END_DIR}" =~ "inc" ]];then
        NUM=`echo ${END_DIR} | grep -o [0-9]*`
        let NUM_ADD=(NUM + 1)
        echo -e "incremental backup: ${DB_MANUAL_BACKUP_PATH}/inc_${NUM_ADD}" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync --incremental ${DB_MANUAL_BACKUP_PATH} --incremental-basedir=${DB_MANUAL_BACKUP_PATH}/inc_${NUM} >> ${XTRABACKUP_LOG_FILE} 2>&1
        cd ${DB_MANUAL_BACKUP_PATH}
        mv -v `ls |grep $(date +%Y-%m-%d)` inc_${NUM_ADD} >> ${DB_BACKUP_LOG_FILE}
    fi
    
    # 判断Xtrabackup执行状态
    checkXtrabackupStatus
}

# 手动恢复数据
manualRecovery() {
    echo -e "################### Manual Recovery ###################" >> ${DB_BACKUP_LOG_FILE}
    echo -e "RecoveryTime: $(date "+%Y/%m/%d %H:%M:%S")" >> ${DB_BACKUP_LOG_FILE}

    [ ! -d ${DB_DATA_PATH}/data ] || [ "`ls ${DB_DATA_PATH}/data`" == "" ] || { echo "ERROR: MySQL data directory is not empty: ${DB_DATA_PATH}/data" >> ${DB_BACKUP_LOG_FILE};exit 2; }
    INC_NUM=`ls -d ${DB_MANUAL_BACKUP_PATH}/inc_* 2> /dev/null | wc -l`
    # 如果没有增量数据直接还原
    if [ "${INC_NUM}" -eq 0 ];then
        echo -e "prepare full backup: ${DB_MANUAL_BACKUP_PATH}/data" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --apply-log ${DB_MANUAL_BACKUP_PATH}/data >> ${XTRABACKUP_LOG_FILE} 2>&1
    elif [ "${INC_NUM}" -ge 1 ];then
        # 有增量数据的时候，根据增量数据的文件夹个数，循环恢复
        echo -e "prepare full backup: ${DB_MANUAL_BACKUP_PATH}/data" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --apply-log --redo-only ${DB_MANUAL_BACKUP_PATH}/data >> ${XTRABACKUP_LOG_FILE} 2>&1

        for i in `seq 1 ${INC_NUM}`;do
            if [ $i -eq ${INC_NUM} ];then
                echo -e "prepare incremental backup: ${DB_MANUAL_BACKUP_PATH}/inc_${i}" >> ${DB_BACKUP_LOG_FILE}
                innobackupex --apply-log ${DB_MANUAL_BACKUP_PATH}/data --incremental-dir=${DB_MANUAL_BACKUP_PATH}/inc_${i} >> ${XTRABACKUP_LOG_FILE} 2>&1
                break
            fi
            echo -e "prepare incremental backup: ${DB_MANUAL_BACKUP_PATH}/inc_${i}" >> ${DB_BACKUP_LOG_FILE}
            innobackupex --apply-log --redo-only ${DB_MANUAL_BACKUP_PATH}/data --incremental-dir=${DB_MANUAL_BACKUP_PATH}/inc_${i} >> ${XTRABACKUP_LOG_FILE} 2>&1
        done
    fi

    #  开始恢复数据
    echo -e "recovery backup: ${DB_MANUAL_BACKUP_PATH}/data" >> ${DB_BACKUP_LOG_FILE}
    innobackupex --copy-back ${DB_MANUAL_BACKUP_PATH}/data >> ${XTRABACKUP_LOG_FILE} 2>&1
    chown -R mysql:mysql ${DB_DATA_PATH}/data

    # 判断Xtrabackup执行状态
    checkXtrabackupStatus
}

# 自动备份数据: 周一全量备份，周二~周日增量备份
# 备份目录格式：${BD_BACKUP_PATH}/YEAR/MONTH/DAY
# ${BD_BACKUP_PATH}/2022/01/05
autoBackup() {
    echo -e "################### Automatic Backup ###################" >> ${DB_BACKUP_LOG_FILE}
    echo -e "Backup Time: $(date "+%Y/%m/%d %H:%M:%S")" >> ${DB_BACKUP_LOG_FILE}

    YEAR=`date +%Y`
    MONTH=`date +%m`
    DAY=`date +%d`
    DAY_OF_WEEK=`date -d ${YEAR}${MONTH}${DAY} +%u`
    YESTERDAY=`date +%Y/%m/%d --date="-1 day"`

    FULL_DIR="${BD_BACKUP_PATH}/${YEAR}/${MONTH}"
    mkdir -p ${FULL_DIR}

    # 周一全量备份
    if [ ${DAY_OF_WEEK} -eq 1 ];then
        echo -e "full backup: ${FULL_DIR}/${DAY}" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync ${FULL_DIR} >> ${XTRABACKUP_LOG_FILE} 2>&1
        
        cd ${FULL_DIR}
        mv -v `ls ${FULL_DIR} |grep $(date "+%Y-%m-%d_%H")` ${DAY} >> ${DB_BACKUP_LOG_FILE}
    elif [ ! -d ${BD_BACKUP_PATH}/${YESTERDAY} ];then
        # 如果前一天备份不存在，返回错误或者进行全量备份
        echo -e "ERROR: Not exist yesterday backup data: ${BD_BACKUP_PATH}/${YESTERDAY}" >> ${DB_BACKUP_LOG_FILE}
        echo -e "now full backup: ${FULL_DIR}/${DAY}" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync ${FULL_DIR} >> ${XTRABACKUP_LOG_FILE} 2>&1
        
        cd ${FULL_DIR}
        mv -v `ls ${FULL_DIR} |grep $(date "+%Y-%m-%d_%H")` ${DAY} >> ${DB_BACKUP_LOG_FILE}
    else
        # 周二~周日增量备份
        echo -e "incremental backup: ${FULL_DIR}/${DAY} " >> ${DB_BACKUP_LOG_FILE}
        innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync --incremental ${FULL_DIR} --incremental-basedir=${BD_BACKUP_PATH}/${YESTERDAY} >> ${XTRABACKUP_LOG_FILE} 2>&1
        
        cd ${FULL_DIR}
        mv -v `ls ${FULL_DIR} |grep $(date "+%Y-%m-%d_%H")` ${DAY} >> ${DB_BACKUP_LOG_FILE}
    fi

    # 判断Xtrabackup执行状态
    checkXtrabackupStatus

    # 保留最近2份全量备份的数据
    cd ${BD_BACKUP_PATH}
    # 获取上周一的前一天日期
    let GET_LAST_WEEK=(${DAY_OF_WEEK} + 7)
    for x in `seq 0 6`;do
        let REMOVE_DAY=(${GET_LAST_WEEK} + x)
        LAST_WEEK=`date -d ${YEAR}${MONTH}${DAY}" -${REMOVE_DAY} days" +%Y/%m/%d`
        [ ! -d ${BD_BACKUP_PATH}/${LAST_WEEK} ] || { echo "REMOVE: ${LAST_WEEK}" >> ${DB_BACKUP_LOG_FILE}; rm -rf ${LAST_WEEK}; }
    done
}

# 自动恢复备份
autoRecovery() {
    echo -e "################### Automatic Recovery ###################" >> ${DB_BACKUP_LOG_FILE}
    echo -e "Recovery Time: $(date "+%Y/%m/%d %H:%M:%S")" >> ${DB_BACKUP_LOG_FILE}

    # 恢复数据库DATA目录如果非空，报错退出
    [ ! -d ${DB_DATA_PATH}/data ] || [ "`ls ${DB_DATA_PATH}/data`" == "" ] || { echo "ERROR: MySQL data directory is not empty: ${DB_DATA_PATH}/data" >> ${DB_BACKUP_LOG_FILE};exit 2; }
    
    YEAR=${2}
    MONTH=$3
    DAY=$4
    DAY_OF_WEEK=`date -d ${YEAR}${MONTH}${DAY} +%u`

    # 如果日期的目录不存在，报错退出
    [ -d ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY} ] || { echo "ERROR: Wrong input date: ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY}" >> ${DB_BACKUP_LOG_FILE};exit 3; }

    # 如果日期目录为空，报错退出
    [ "`ls ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY}`" == "" ] && { echo "ERROR: Dir no data: ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY}" >> ${DB_BACKUP_LOG_FILE};exit 4; }
    
    let F_DAY=(${DAY_OF_WEEK} - 1)
    MONDAY=`date -d ${YEAR}${MONTH}${DAY}" -${F_DAY} days" +%Y/%m/%d `
    
    # 如果为星期一(1), 则恢复全量备份
    if [ ${DAY_OF_WEEK} -eq 1 ];then
        echo -e "prepare full backup: ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY}" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --apply-log ${BD_BACKUP_PATH}/${YEAR}/${MONTH}/${DAY} >> ${XTRABACKUP_LOG_FILE} 2>&1

        # 判断Xtrabackup执行状态
        checkXtrabackupStatus
     elif [ ${DAY_OF_WEEK} -ge 2 ];then
        # 如果非星期一, 则使用全量+增量进行恢复
        # 准备全量备份目录
        echo -e "prepare full backup: ${BD_BACKUP_PATH}/${MONDAY}" >> ${DB_BACKUP_LOG_FILE}
        innobackupex --apply-log --redo-only ${BD_BACKUP_PATH}/${MONDAY} >> ${XTRABACKUP_LOG_FILE} 2>&1

        # 准备增量备份目录
        for n in `seq 1 ${F_DAY}`;do
            let N_DAY=(${n}-1)
            INC_DAY=`date -d ${YEAR}${MONTH}${DAY}" -${N_DAY} days" +%Y/%m/%d`

            if [ $n -eq 0 ];then
                echo -e "prepare incremental backup: ${BD_BACKUP_PATH}/${INC_DAY}" >> ${DB_BACKUP_LOG_FILE}
                innobackupex --apply-log ${BD_BACKUP_PATH}/${MONDAY} --incremental-dir=${BD_BACKUP_PATH}/${INC_DAY} >> ${XTRABACKUP_LOG_FILE} 2>&1
                break
            fi
            echo -e "prepare incremental backup: ${BD_BACKUP_PATH}/${INC_DAY}" >> ${DB_BACKUP_LOG_FILE}
            innobackupex --apply-log --redo-only ${BD_BACKUP_PATH}/${MONDAY} --incremental-dir=${BD_BACKUP_PATH}/${INC_DAY} >> ${XTRABACKUP_LOG_FILE} 2>&1
        done
    fi

    # 开始恢复数据
    echo -e "recovery backup: ${BD_BACKUP_PATH}/${MONDAY}" >> ${DB_BACKUP_LOG_FILE}
    innobackupex --copy-back ${BD_BACKUP_PATH}/${MONDAY} >> ${XTRABACKUP_LOG_FILE} 2>&1
    chown -R mysql:mysql ${DB_DATA_PATH}

    # 判断Xtrabackup执行状态
    checkXtrabackupStatus
}

# 备份列表
backupList() {
    echo -e "################### List Of Available Backups ###################"
    echo -e "最近2份全量备份" 
    echo -e "------------------------------------------" 
    for i in `ls ${BD_BACKUP_PATH} |grep "20"`;do 
        for x in `ls ${BD_BACKUP_PATH}/$i`;do
            for z in `ls  ${BD_BACKUP_PATH}/$i/$x`;do 
            BACKUP_TYPE=`cat  ${BD_BACKUP_PATH}/$i/$x/$z/xtrabackup_checkpoints |grep backup_type |awk -F" = " '{print$2}'`
            echo -e "$i $x $z (${BACKUP_TYPE})"
            done
        done
    done
    echo -e "------------------------------------------" 
}
# 帮助信息
help() {
    echo -e "\
MySQL 自动备份恢复 (使用 xtrabackup - innobackupex);
注意: xtrabackup/innobackupex 只能备份InnoDB和XtraDB两种存储引擎的表, 不支持备份MyISAM数据表;

用法: $0 [-f/-i/-m/-a/-r/-h]

    -f : 手动全量备份(保留最近3份备份的数据); 
    -i : 手动增量备份(必须进行全量备份后才能进行增量备份); 
    -m : 手动恢复备份(恢复全量和增量备份数据); 
    -l : 查看可用于恢复的备份(自动备份); 
    -a : 自动备份数据(按照: 年/月/日 进行备份, 周一为全量, 周二~周日为增量), 保留最近2份全量备份(一份全量包含当周N天增量); 
    -r : 使用autobackup备份的数据进行还原, 用法: $0 年 月 日 , 恢复至2020.01.07日: $0 2022 01 07;
    -h : 查看帮助;
"
}


case $1 in
    -f)
        fullBackup
        echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE}
    ;;
    -i)
        incrementalBackup
        echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE}
    ;;
    -m)
        manualRecovery
        echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE}
    ;;
    -a)
        autoBackup
        echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE}
    ;;
    -l)
        backupList
    ;;
    -r)
        autoRecovery $1 $2 $3 $4
        echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE}
    ;;
    -h)
        help
    ;;
    "" )
        help
    ;;
esac
percona-xtrabackup备份

相关阅读

相关文章

相关问答

相关文档
