基于innobackupex备份工具的mysql全量备份和增量备份shell脚本 – 21运维
通知: .-...

基于innobackupex备份工具的mysql全量备份和增量备份shell脚本

mysql 21运维 2282浏览 0评论

参考网上的资料和根据自己服务器的mysql多实例实际情况,搞了一个基于innobackupex备份工具的mysql全量备份和增量备份脚本,暂定版本V1.0,后续还有很多需要改进和优化的。思路和方式很多,这里目前用做学习练习用,以后会改善脚本和功能实现。
该脚本基本可以实现需要的功能,但不够简洁和智能,代码过于臃肿。

#!/bin/bash
# innobackupex mysql full and incr
#author:21yunwei
#version:V1.0

BACKTIME=`date +%Y%m%d%H%M%S`
BACK_DIR=/home/mysqlbak
FULLBACKUP_DIR=/home/mysqlbak/full/
INCRBACKUP_DIR=/home/mysqlbak/incr/
MYSQL=/usr/local/mysql/bin/mysql
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
MYSQL_UP=`mysqladmin -uroot  -photmail.com  -S  /data/mysql_3306/mysql_3306.sock  status | grep  Uptime`
MYSQL_CNF=/data/test/mysql3306.cnf  
MYSQl_CMD=`mysql  -uroot  -pxxxxxx  -S /data/mysql_3306/mysql_3306.sock`
#INDEXFILE=/home/mysqlbak/full/index
OPRATE_LOGFILE=/home/mysqlbak/mysqlbak.log
LOGFILE=/home/mysqlbak/mysqlbak.log
INNOBACKUPEX=/usr/bin/innobackupex
INNOBACK_taillog=/home/mysqlbak/innobackupex.log
FULLBACK_INTERVAL=604800
STARTED_TIME=`date +%s`

if [ $MYSQL_UP -ne  0 ]
	then
	echo  "Mysql is down,please check"
	exit 1
fi

if [ ! -d $BACK_DIR ] 
	then
	echo  "$BACK_DIR is not exists,creating"
	/bin/mkdir -p $BACK_DIR
fi

if [ ! -d $FULLBACKUP_DIR ]
	then
	echo   "$FULLBACKUP_DIR is not exists,creating" 
	/bin/mkdir -p $FULLBACKUP_DIR
fi

if [ ! -d $INCRBACKUP_DIR ] 
	then
	 echo "INCRBACKUP_DIR is $INCRBACKUP_DIR"
	 /bin/mkdir -p $INCRBACKUP_DIR 
fi

if [ ! -f $LOGFILE ] 
	then
	echo "$LOGFILE  is not exists,creating "
	touch  $LOGFILE 
fi

if [ ! -f $INDEXFILE ] 
	then
	echo "$INDEXFILE  is not exists,creating "
	touch  $INDEXFILE
fi

echo "----------------------------"
echo
echo "$0: MySQL备份脚本"
echo "备份开始于: $BAKTIME "
echo
echo "----------------------------"

#find latest full and give latest value
LATEST_FULL_BACK=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

#find last full and get mtime
LATEST_FULL_BACK_CREATED_TIME=`stat -c %Y $FULLBACKUP_DIR/$LATEST_FULL_BACK`

#judge full or not ,then decide to make full or incr
if [ "$LATEST_FULL_BACK" -a `expr $LATEST_FULL_BACK_CREATED_TIME + $FULLBACK_INTERVAL` -ge $STARTED_TIME ] 
	then
	echo -e "完全备份$LATEST_FULL_BACK未过期,将根据$LATEST_FULL_BACK名字作为增量备份目录命名"
	echo "        "
	NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACK
	/home/mysqlbak/incr/2016-06-13_15-07-31
	mkdir -p $NEW_INCRDIR
		LATEST_INCR_BACK=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`  #获取最新增量备份目录名
		if [ ! $LATEST_INCR_BACK ] 
		then
			INCRBASEDIR=$FULLBACK_DIR/$LATEST_FULL_BACK      #定义增量基础备份目录名$INCRBASEDIR,还没有做增量目录情况下。
			INCRBASEDIR=/home/mysqlbak/full/2016-06-13_15-07-31
			echo -e "增量备份将以$INCRBASEDIR作为备份基础"
			echo "        "
			else
			INCRBASEDIR=$LATEST_INCR_BACK			 #定义增量基础备份目录名$INCRBASEDIR,已经存在增量目录情况下。
			echo -e "增量备份将以$INCRBASEDIR作为备份基础"
			echo "        "
		fi
	echo "使用$INCRBASEDIR作为基础做新的增量备份."
	$INNOBACKUPEX --defaults-file=$MYSQL_CNF  $MYSQL_CMD --incremental-basedir=$INCRBASEDIR  --incremental $NEW_INCRDIR > $INNOBACK_taillog 2>&1
        if [$? -eq 0  ]
                then
                echo "incremental back sucess."
          else
                echo "incremental back error,please check."
        fi
	
  else
	echo  "*********************************"
	echo -e "Do new full bakup,please wait..."
	echo  "*********************************"
	$INNOBACKUPEX --defaults-file=$MYSQL_CNF   $MYSQL_CMD --compress $FULLBACKUP_DIR > $INNOBACK_taillog 2>&1 
	if [$? -eq 0  ]
		then
		echo "full back sucess."
	  else
		echo "full back error,please check."
	fi
fi


echo -e "find expired file and delete:">>$OPRATE_LOGFILE
for expire_full_file in $(/usr/bin/find $FULLBACK_DIR/ -mtime +7)
do
 if [ -d $expire_full_file ]
	 then
		 rm -rf $expire_full_file
		 echo -e "delete full expired dir:$expire_full_file" >>$OPRATE_LOGFILE
	 elif [ -f $expire_full_file ]
		 then
		 rm -rf $expire_full_file
		 echo -e "delete full expired file:$expire_full_file" >>$OPRATE_LOGFILE
 fi
done

for expire_incr_file in $(/usr/bin/find $INCRBACKUP_DIR/ -mtime +7)
do
 if [ -d $expire_incr_file ]
         then
                 rm -rf $expire_incr_file
                 echo -e "delete incr expired dir:$expire_incr_file" >>$OPRATE_LOGFILE
         elif [ -f $expire_incr_file ]
                 then
                 rm -rf $expire_incr_file
                 echo -e "delete incr  expired file:$expire_incr_file" >>$OPRATE_LOGFILE
 fi
done
tree   $BACK_DIR
echo "Mysql 3306 full and incr backup finished." >>$OPRATE_LOGFILE

后期会进行更为智能和高效方式进行实现。请期待 V 2.0、V3.0 。。。。V N.0.该脚本是之前测试用的,线上的脚本没有拿出来。思想是一样的,只是目录统一了具体变量。可以自己优化。

 

转载请注明:21运维 » 基于innobackupex备份工具的mysql全量备份和增量备份shell脚本

发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (3)

  1. 群主,你的牛逼版本的mysql备份脚本怎么还没有出来?
    阿里王2017-04-14 18:10 回复
    • 之前才开始学innobackupex的时候想复杂了,之后用的更简单的脚本http://www.21yunwei.com/archives/5013 ,当时线上就用的这种。现在改用了阿里的RDS以及aws的Aurora,脚本都没用了。
      21运维2017-04-14 18:55 回复
  2. :oops: :eek: :oops: :idea:
    s2017-03-07 15:00 回复