基于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=rootMYSQL_PASSWORD=rootMYSQL_HOST=localhostMYSQL_PORT=3306 MY_CNF=/etc/my.cnfMYSQL_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_DIRmkdir -p $BACKUP_LOG_DIR #colour levelSETCOLOR_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 -vif [ `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 1fi if ！ `echo "exit" | mysql -s $MYSQL_CMD --show-warnings=false` ; then LogErrorMsg "数据库的用户名、密码、主机名或端口中存在错误配置！" exit 1fi # 备份的头部信息 LogMsg "----------------------------------------------" LogMsg "$0: MySQL备份脚本"LogMsg "----------------------------------------------"LogMsg "开始于: `date +%F" "%T`" #查找最新的完全备份文件夹（不包括父级目录） LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P " | sort -nr | head -1` # 查找最最新全库备份时间,日期格式：2016-07-20LATEST_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 " | 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 " ` if [ -z "$EXPIRE_FILE" ];then LogMsg "未找到可以删除的过期备份文件"else for efile in `find $FULLBACKUP_DIR/ -mindepth 1 -maxdepth 1 -type d -mtime +7 -printf "%P "` do LogMsg "删除过期全备文件:$efile" rm -rf $FULLBACKUP_DIR/$efile LogMsg "删除过期增量备份文件:$efile" rm -rf $INCRBACKUP_DIR/$efile donefi LogSucMsg "备份完成于: `date +%F" "%T" "`"exit 0 更多XtraBackup相关教程见以下内容：MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htmMySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htmMySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm用XtraBackup实现MySQL的主从复制快速部署【主不锁表】 http://www.linuxidc.com/Linux/2012-10/71919p2.htm安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htmXtraBackup 的详细介绍：请点这里
XtraBackup 的下载地址：请点这里本文永久更新链接地址