利用 Xtrabackup 自动备份 MariaDB 数据库脚本

#!/bin/bash
BACKUP_BIN=/usr/bin/xtrabackup
#mysql目标服务器以及用户名和密码
MYSQL_CMD="--host=localhost --user=backupuser --password=bei123 --port=3306"
MY_CNF=/etc/mysql/my.cnf #mysql的配置文件
MYSQL=/usr/bin/mysql
MYSQL_ADMIN=/usr/bin/mysqladmin
BACKUP_DIR=/home/lily/XtrabackupBkDa/Backdata #备份的数据目录
	
BACKUP_ARCHIVE_DIR=/home/lily/XtrabackupBkDa/Archivedata #备份的归档目录
	
BACKUP_HISTORYLOG_DIR=/home/lily/XtrabackupBkDa/Historylog
	
BACK_DIRECTORY_DATEDIR=`date +%Y-%m-%d_%H-%M-%S`
backhistoryfile=${BACKUP_HISTORYLOG_DIR}/backup_history.`date +%Y%m%d%H%M%S`.txt
# 备份的头部信息
echo "--------------------------------------------------------" > $backhistoryfile
echo >> $backhistoryfile
echo "$0: Xtrbackup备份脚本" >> $backhistoryfile
echo "开始于: `date +%F' '%T`" >> $backhistoryfile
echo >> $backhistoryfile
	
#查找最新的备份
LATEST_BACKUP=`find ${BACKUP_DIR} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
#看备份目录下是否有备份，
if [ "${LATEST_BACKUP}" ] ; then
# 如果有则进行增量备份
LATEST_INCRE_BACKUP=`find ${BACKUP_DIR}/${LATEST_BACKUP} -mindepth 1 -maxdepth 1 -type d \
-regextype "posix-extended" -regex ".*incre[1-7]$" -printf "%P\n" | sort -nr | head -1`
#查找最新的增量备份名称，为incre1-incre7
if [ "${LATEST_INCRE_BACKUP}" ] ; then
#如果存在增量备份，则以此增量备份为基础进行正常增量备份，产生 incre2、incre3、incre4、incre5、incre6、incre7 增量备份。
#如果进行了第7次增量备份incre7，则整体打包全备目录至归档目录保存并删除全备目录。
LATEST_INCRE_NUMBER=$[${LATEST_INCRE_BACKUP:0-1:1}+1]
echo "全备份目录${BACKUP_DIR}/${LATEST_BACKUP}/下已有增量备份${LATEST_INCRE_BACKUP}，进行第${LATEST_INCRE_NUMBER}次增量备份incre${LATEST_INCRE_NUMBER}......" >> $backhistoryfile
tmplog=${BACKUP_DIR}/${LATEST_BACKUP}/tmplog/xtrabackup.`date +%Y%m%d%H%M%S`.incre${LATEST_INCRE_NUMBER}.log
	
	
${BACKUP_BIN} --defaults-file=$MY_CNF --use-memory=2G $MYSQL_CMD \
--backup --target-dir=${BACKUP_DIR}/${LATEST_BACKUP}/incre${LATEST_INCRE_NUMBER} \
--incremental-basedir=${BACKUP_DIR}/${LATEST_BACKUP}/${LATEST_INCRE_BACKUP} \
> $tmplog 2>&1
	
if [ -z "`tail -1 $tmplog | grep 'completed OK!'`" ] ; then
echo "${BACKUP_BIN}命令执行失败，执行状态请查看$tmplog文件。" >> $backhistoryfile
echo >> $backhistoryfile
exit 1
else
echo "第${LATEST_INCRE_NUMBER}次增量备份成功，备份目录为${BACKUP_DIR}/${LATEST_BACKUP}/incre${LATEST_INCRE_NUMBER}。" >> $backhistoryfile
echo >> $backhistoryfile
if [ "${LATEST_INCRE_NUMBER}" = "7" ] ; then
echo "因为已经进行了${LATEST_INCRE_NUMBER}次增量备份，现归档打包并删除备份目录${BACKUP_DIR}/${LATEST_BACKUP}/。" >> $backhistoryfile
tar -czPf ${BACKUP_ARCHIVE_DIR}/xtrabackup_${LATEST_BACKUP}.tar.gz ${BACKUP_DIR}/${LATEST_BACKUP}
echo "备份${LATEST_BACKUP}归档压缩完毕。" >> $backhistoryfile
rm -rf ${BACKUP_DIR}/${LATEST_BACKUP}
echo "已经归档打包备份目录${BACKUP_DIR}/${LATEST_BACKUP}/保存至${BACKUP_ARCHIVE_DIR}/${LATEST_BACKUP}.tar.gz，并已经删除备份目录${BACKUP_DIR}/${LATEST_BACKUP}/。" >> $backhistoryfile
fi
echo "完成于: `date +%F' '%T`" >> $backhistoryfile
echo >> $backhistoryfile
echo "--------------------------------------------------------" >> $backhistoryfile
fi
else
#如果不存在增量备份，则进行第一次增量备份，目录为incre1
echo "全备份目录${BACKUP_DIR}/${LATEST_BACKUP}/下还没有增量备份，进行第一次增量备份incre1......" >> $backhistoryfile
	
tmplog=${BACKUP_DIR}/${LATEST_BACKUP}/tmplog/xtrabackup.`date +%Y%m%d%H%M%S`.incre1.log
	
${BACKUP_BIN} --defaults-file=$MY_CNF --use-memory=2G $MYSQL_CMD \
--backup --target-dir=${BACKUP_DIR}/${LATEST_BACKUP}/incre1 \
--incremental-basedir=${BACKUP_DIR}/${LATEST_BACKUP}/fulbas \
> $tmplog 2>&1
	
if [ -z "`tail -1 $tmplog | grep 'completed OK!'`" ] ; then
echo "${BACKUP_BIN}命令执行失败，执行状态请查看$tmplog文件。" >> $backhistoryfile
echo >> $backhistoryfile
exit 1
else
echo "第一次增量备份成功，备份目录为${BACKUP_DIR}/${LATEST_BACKUP}/incre1。" >> $backhistoryfile
echo >> $backhistoryfile
echo "完成于: `date +%F' '%T`" >> $backhistoryfile
echo >> $backhistoryfile
echo "--------------------------------------------------------" >> $backhistoryfile
fi
fi
else
# 如果没有则进行全备份
echo "备份目录${BACKUP_DIR}/下还没有全备份，进行全备份......" >> $backhistoryfile
	
mkdir -p ${BACKUP_DIR}/${BACK_DIRECTORY_DATEDIR}/fulbas
mkdir -p ${BACKUP_DIR}/${BACK_DIRECTORY_DATEDIR}/tmplog
	
tmplog=${BACKUP_DIR}/${BACK_DIRECTORY_DATEDIR}/tmplog/xtrabackup.`date +%Y%m%d%H%M%S`.fulbas.log
${BACKUP_BIN} --defaults-file=$MY_CNF --use-memory=2G $MYSQL_CMD \
--backup --target-dir=${BACKUP_DIR}/${BACK_DIRECTORY_DATEDIR}/fulbas \
> $tmplog 2>&1
	
if [ -z "`tail -1 $tmplog | grep 'completed OK!'`" ] ; then
echo "${BACKUP_BIN}命令执行失败，执行状态请查看$tmplog文件。" >> $backhistoryfile
echo >> $backhistoryfile
exit 1
else
echo "全备份成功，全备份目录为${BACKUP_DIR}/${BACK_DIRECTORY_DATEDIR}/fulbas/。" >> $backhistoryfile
echo >> $backhistoryfile
echo "完成于: `date +%F' '%T`" >> $backhistoryfile
echo >> $backhistoryfile
echo "--------------------------------------------------------" >> $backhistoryfile
fi
	
fi
	
# 删除16天之前的归档备份文件
find ${BACKUP_ARCHIVE_DIR}/* -mtime +16 -exec rm {} \;
	
# 删除8天之前的备份日志文件
find ${BACKUP_HISTORYLOG_DIR}/* -mtime +8 -exec rm {} \;

crontab -e

18 12 * * * /home/lily/xtrabackup_autobak.sh

crontab -l