基于XtraBackup 2.3.5 的 MySQL全量备份与增量备份
基于XtraBackup 2.3.5 的 MySQL全量备份与增量备份，备注：脚步恢复时只能恢复到备份前的状态，如果需要恢复到当前状态，还需配合binlog。
#!/bin/sh
####################################################
# AUTHOR : JusonSummer #
# VERSION : V1.0.0 #
# DATE : 2016-08-13 #
# CORPORATION : FiberHome #
####################################################
#基于xtrabackup 2.3.5
#星期四,星期日全备,其他工作日每天一次增量备份,手动执行可通过设置WEEK_NUM来确定是增量还是全量
INNOBACKUPEX=innobackupex #INNOBACKUPEX的命令
#mysql目标服务器以及备份用户及密码
MYSQL_USER=root
MYSQL_PASSWORD=root
MYSQL_HOST=localhost
MYSQL_PORT=3306
MY_CNF=/etc/my.cnf
MYSQL_CMD=" --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST --port=$MYSQL_PORT"
TMP_LOG="/var/log/backup.$$.log" # $$当前shell进程号
BACKUP_DIR=/mysql_backup # 备份的主目录
FULLBACKUP_DIR=$BACKUP_DIR/full # 全库备份的目录
INCRBACKUP_DIR=$BACKUP_DIR/incre # 增量备份的目录
BACKUP_LOG_DIR=$BACKUP_DIR/log # 备份日志的目录
LOGFILE_NAME=backup_`date +%Y%m%d%H%M%S`.log # 备份日志文件名
#星期
WEEK_NUM=`date +%w`
#WEEK_NUM=0
#新建全备和差异备份的目录
mkdir -p $FULLBACKUP_DIR
mkdir -p $INCRBACKUP_DIR
mkdir -p $BACKUP_LOG_DIR
#colour level
SETCOLOR_SUCCESS="echo -en \\033[1;32m"
SETCOLOR_FAILURE="echo -en \\033[1;31m"
SETCOLOR_WARNING="echo -en \\033[1;33m"
SETCOLOR_NORMAL="echo -en \\033[0;39m"
#日志输出函数
LogMsg()
{
time=`date "+%D %T"`
echo "[$time] : INFO : $*"
echo "[$time] : INFO : $*" >>$BACKUP_LOG_DIR/$LOGFILE_NAME
$SETCOLOR_NORMAL
}
LogWarnMsg()
{
time=`date "+%D %T"`
$SETCOLOR_WARNING
echo "[$time] : WARN : $*"
$SETCOLOR_NORMAL
}
LogSucMsg()
{
time=`date "+%D %T"`
$SETCOLOR_SUCCESS
echo "[$time] : SUCCESS : $*"
echo "[$time] : SUCCESS : $*" >>$BACKUP_LOG_DIR/$LOGFILE_NAME
$SETCOLOR_NORMAL
}
LogErrorMsg()
{
time=`date "+%D %T"`
$SETCOLOR_FAILURE
echo "[$time] : ERROR : $*"
$SETCOLOR_NORMAL
}
# 检查innobackupex错误输出
check_innobackupex_fail()
{
#保留一份备份的详细日志
cat $TMP_LOG>>$BACKUP_LOG_DIR/$LOGFILE_NAME
if [ -z "`tail -2 $TMP_LOG | grep 'completed OK!'`" ] ; then
LogErrorMsg "$INNOBACKUPEX命令执行失败"
rm -f $TMP_LOG
LogErrorMsg "错误日志请查看 $BACKUP_LOG_DIR/$LOGFILE_NAME"
exit 1
fi
}
# 选项检测
$INNOBACKUPEX -v
if [ `echo $?` -ne 0 ]; then
LogErrorMsg "$INNOBACKUPEX命令不存在,请确认是否安装xtrabackup或环境变量是否配置"
exit 1
fi
PORTNUM00=`netstat -lnt|grep ${MYSQL_PORT}|wc -l`
if [ $PORTNUM00 = 0 ];
then
LogErrorMsg "MySQL处于关闭状态,请打开MySQL."
exit 1
fi
if ! `echo 'exit' | mysql -s $MYSQL_CMD --show-warnings=false` ; then
LogErrorMsg "数据库的用户名、密码、主机名或端口中存在错误配置!"
exit 1
fi
# 备份的头部信息
LogMsg "----------------------------------------------"
LogMsg "$0: MySQL备份脚本"
LogMsg "----------------------------------------------"
LogMsg "开始于: `date +%F' '%T`"
#查找最新的完全备份文件夹(不包括父级目录)
LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
# 查找最最新全库备份时间,日期格式：2016-07-20
LATEST_FULL_BACKUP_CREATED_DATE=`stat -c %y $FULLBACKUP_DIR/$LATEST_FULL_BACKUP | cut -c 1-10`
#星期一,星期二,星期三,星期五,星期六执行增量备份
if [ $WEEK_NUM -eq 1 -o $WEEK_NUM -eq 2 -o $WEEK_NUM -eq 3 -o $WEEK_NUM -eq 5 -o $WEEK_NUM -eq 6 ] ; then
LogMsg "************************************"
LogMsg "正在执行增量备份...请稍等..."
LogMsg "************************************"
#无全备时,需先做全备
if [ -z $LATEST_FULL_BACKUP ] ; then
LogErrorMsg "服务器还没有做过全备份,请根据安装文档先做一次全备！"
exit 1
fi
# 以最新的全备目录名在增量备份目录下新建目录
LogMsg "增量备份放在$INCRBACKUP_DIR/$LATEST_FULL_BACKUP目录下"
NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP
mkdir -p $NEW_INCRDIR
# 查找最新的增量备份是否存在.指定一个备份的路径作为增量备份的基础
LATEST_INCR_BACKUP=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
if [ ! $LATEST_INCR_BACKUP ] ; then
INCRBASEDIR=$FULLBACKUP_DIR/$LATEST_FULL_BACKUP
LogMsg "增量备份将以$INCRBASEDIR备份集作为备份基础"
else
INCRBASEDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP/$LATEST_INCR_BACKUP
LogMsg "增量备份将以$INCRBASEDIR备份集作为备份基础"
fi
$INNOBACKUPEX --defaults-file=$MY_CNF $MYSQL_CMD --incremental $NEW_INCRDIR --incremental-basedir $INCRBASEDIR > $TMP_LOG 2>&1
check_innobackupex_fail
LogMsg "数据库成功增量备份到:$NEW_INCRDIR"
#星期四,星期日执行全量备份
elif [ $WEEK_NUM -eq 0 -o $WEEK_NUM -eq 4 ] ; then
LogMsg "************************************"
LogMsg "正在执行全新的完全备份...请稍等..."
LogMsg "************************************"
$INNOBACKUPEX --defaults-file=$MY_CNF $MYSQL_CMD $FULLBACKUP_DIR> $TMP_LOG 2>&1
check_innobackupex_fail
LogMsg "数据库成功全量备份到:$FULLBACKUP_DIR"
fi
#删除过期的全量备份以及增量备份,过期时间为7天
LogMsg "寻找过期的备份文件...请稍等..."
EXPIRE_FILE=`find $FULLBACKUP_DIR/ -mindepth 1 -maxdepth 1 -type d -mtime +7 -printf "%P\t" `
if [ -z "$EXPIRE_FILE" ];then
LogMsg "未找到可以删除的过期备份文件"
else
for efile in `find $FULLBACKUP_DIR/ -mindepth 1 -maxdepth 1 -type d -mtime +7 -printf "%P\n"`
do
LogMsg "删除过期全备文件:$efile"
rm -rf $FULLBACKUP_DIR/$efile
LogMsg "删除过期增量备份文件:$efile"
rm -rf $INCRBACKUP_DIR/$efile
done
fi
LogSucMsg "备份完成于: `date +%F' '%T' '`"
exit 0
更多XtraBackup相关教程见以下内容：
XtraBackup 的详细介绍：请点这里
XtraBackup 的下载地址：请点这里