#!/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 ];thenecho -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}elseecho -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-filesecho -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 ];thencd ${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_" ]];thenmv inc_* data_${DB_BACKUP_TIME}fitar zcf ${DB_ARCHIVE_PATH}/data_${DB_BACKUP_TIME}.tar.gz data_${DB_BACKUP_TIME} --remove-filesfiecho -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>&1cd ${DB_MANUAL_BACKUP_PATH}mv -v `ls |grep "20" |grep -v "archive"` data >> ${DB_BACKUP_LOG_FILE}# 判斷Xtrabackup執行狀態checkXtrabackupStatuscd ${DB_MANUAL_BACKUP_PATH}/archive# 保留3份手動備份RE=3FN=`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" ];thenecho -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>&1cd ${DB_MANUAL_BACKUP_PATH}mv -v `ls |grep $(date +%Y-%m-%d)` inc_1 >> ${DB_BACKUP_LOG_FILE}#如果數據目錄中已經含有inc的目錄，那么根據inc數字最大的目錄進行增量備份elif  [[ "${END_DIR}" =~ "inc" ]];thenNUM=`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>&1cd ${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 ];thenecho -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>&1elif [ "${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>&1for i in `seq 1 ${INC_NUM}`;doif [ $i -eq ${INC_NUM} ];thenecho -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>&1breakfiecho -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>&1donefi#  開始恢復數據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>&1chown -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 ];thenecho -e "full backup: ${FULL_DIR}/${DAY}" >> ${DB_BACKUP_LOG_FILE}innobackupex --defaults-file=${DB_CONFIG_FILE} ${AUTH} --rsync ${FULL_DIR} >> ${XTRABACKUP_LOG_FILE} 2>&1cd ${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>&1cd ${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>&1cd ${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`;dolet 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=$3DAY=$4DAY_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 ];thenecho -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執行狀態checkXtrabackupStatuselif [ ${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}`;dolet N_DAY=(${n}-1)INC_DAY=`date -d ${YEAR}${MONTH}${DAY}" -${N_DAY} days" +%Y/%m/%d`if [ $n -eq 0 ];thenecho -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>&1breakfiecho -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>&1donefi# 開始恢復數據echo -e "recovery backup: ${BD_BACKUP_PATH}/${MONDAY}" >> ${DB_BACKUP_LOG_FILE}innobackupex --copy-back ${BD_BACKUP_PATH}/${MONDAY} >> ${XTRABACKUP_LOG_FILE} 2>&1chown -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`;dofor 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})"donedonedoneecho -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)fullBackupecho "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE};;-i)incrementalBackupecho "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE};;-m)manualRecoveryecho "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE};;-a)autoBackupecho "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE};;-l)backupList;;-r)autoRecovery $1 $2 $3 $4echo "--------------------------------------------------------------------------------------------------" >> ${DB_BACKUP_LOG_FILE};;-h)help;;"" )help;;
esac