你的位置:首页 > 数据库

[数据库]MySQL 全量及增量备份,基于xtrabackup 2.3.5


昨天一个pgp加密盘坏了,东西全丢了。。。,我的笔记啊!我咋怎么2呢。。

备份个脚本吧,方便以后手机查看,话说我都快忘记帐号密码了

备注:脚步恢复时只能恢复到备份前的状态,如果需要恢复到当前状态,还需配合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.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 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 -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 1fi	  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-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\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