MySQL备份脚本--xtrabackup

#!/bin/bash
#mysql的登录用户名和密码(根据实际情况填写)
mysql_hostname=192.1.1.1
mysql_username=root
mysql_password=123456
mysql_port=3306
mysql_socket="/tmp/mysql.sock"backup_dir="/opt/mysql/backup"
xtrabackup_path="/home/mysql/software/xtrabackup/bin/innobackupex"
mysql_cnf="/opt/mysql/thunisoft/my.cnf"INC_BASE_LIST="${backup_dir}/inc_list.txt"
LOCK_FILE=/tmp/innobackupex.lock
THREAD=3mkdir -p ${backup_dir}
CURRENT_BACKUP_PATH="${backup_dir}/$(date +%F_%H-%M)"
[[ -d ${CURRENT_BACKUP_PATH} ]] && CURRENT_BACKUP_PATH="${backup_dir}/$(date +%F_%H-%M-%S)"print_help(){
    echo "--------------------------------------------------------------"echo "Usage: $0 full | inc | help "echo "--------------------------------------------------------------"exit 1
}[[ $# -lt 1 || "$1" == "help" ]] && print_help[[ -f "$LOCK_FILE" ]] && echo -e "Usage: rm -f $LOCK_FILE\nUsage: chattr -i $LOCK_FILE && rm -f $LOCK_FILE" && exit 1FullBackup(){
    touch $LOCK_FILEchattr +i $LOCK_FILElocal rc=0${xtrabackup_path} --defaults-file=${mysql_cnf} --user=${mysql_username} --port=${mysql_port} --socket=${mysql_socket} --password=${mysql_password} --host=${mysql_hostname} --parallel=${THREAD} --no-timestamp ${CURRENT_BACKUP_PATH} > ${CURRENT_BACKUP_PATH}_full.log 2>&1grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_full.log > /dev/null 2>&1if [ $? -ne 0 ];thenrc=1[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}elseecho "NULL|${CURRENT_BACKUP_PATH}|full" >> ${INC_BASE_LIST}[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1fichattr -i ${LOCK_FILE}rm -f $LOCK_FILEchattr +a ${INC_BASE_LIST}return $rc
}IncBackup(){
    touch $LOCK_FILEchattr +i $LOCK_FILElocal rc=0PREV_BACKUP_DIR=$(sed '/^$/d' ${
     INC_BASE_LIST} | tail -1 | awk -F '|' '{print $2}')${xtrabackup_path} --defaults-file=${mysql_cnf} --user=${mysql_username} --port=${mysql_port} --socket=${mysql_socket} --password=${mysql_password} --host=${mysql_hostname} --no-timestamp --incremental ${CURRENT_BACKUP_PATH} --incremental-basedir=${PREV_BACKUP_DIR} > ${CURRENT_BACKUP_PATH}_inc.log 2>&1grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_inc.log > /dev/null 2>&1if [ $? -ne 0 ];thenrc=1[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}elseecho "${PREV_BACKUP_DIR}|${CURRENT_BACKUP_PATH}|inc" >> ${INC_BASE_LIST}[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1fichattr -i ${LOCK_FILE}rm -f $LOCK_FILEchattr +a ${INC_BASE_LIST}return $rc
}## 全量备份
if [ "$1" == "full" ];thenFullBackup
fi## 增量备份
if [ "$1" == "inc" ];then## 判断上一次备份是否存在，无则进行全量备份if [[ ! -f ${INC_BASE_LIST} || $(sed '/^$/d' ${
     INC_BASE_LIST} | wc -l) -eq 0 ]];thenFullBackupelseIncBackupfi
fi## 删除14天前的备份
if [[ -d ${BACKUP_BASE_DIR} && $(pwd) != "/" ]];thenfind ${BACKUP_BASE_DIR} -name "$(date -d '14 days ago' +'%F')_*" | xargs chattr -ifind ${BACKUP_BASE_DIR} -name "$(date -d '14 days ago' +'%F')_*" | xargs rm -rf
fi## 备份计划任务
## 
## 每周日凌晨1:30一次全量备份
## 每周一~六凌晨1:30一次增量备份
## 30 1 * * 0 xtrabackup.sh full
## 30 1 * * 1,2,3,4,5,6 xtrabackup.sh inc
##
## 恢复数据步骤：
## (1)、查看备份日志，找到全量备份和增量备份的关系（注意增量备份的顺序）
##
## cat ${BACKUP_BASE_DIR}/${INC_BASE_LIST}
## (2)、全量备份
## innobackupex --apply-log --redo-only ${BACKUP_BASE_DIR}/full_dir
##
## (3)、第一个增量
## innobackupex --apply-log --redo-only ${BACKUP_BASE_DIR}/full_dir \
## --incremental-dir=${BACKUP_BASE_DIR}/one_inc_dir
##
## (4)、第二个增量,注意不加 --redo-only
## innobackupex --apply-log ${BACKUP_BASE_DIR}/full_dir \
## --incremental-dir=${BACKUP_BASE_DIR}/two_inc_dir
## 
## (5)、把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据
## innobackupex --apply-log ${BACKUP_BASE_DIR}/full_dir## (6)、恢复数据
## innobackupex --defaults-file=/opt/mysql/thunisoft/my.cnf --copy-back --rsync ${BACKUP_BASE_DIR}/full_dir