MySQL使用XtraBackup的shell指令碼介紹

瀟湘隱者發表於2016-04-27
MySql指令碼

   mysql_backup.sh是關於MySQL的一個使用XtraBackup做備份的shell指令碼，實現了簡單的完整備份和增量備份、以及郵件傳送
備份資訊等功能。功能目前還比較簡單，後續將繼續完善和增加相關功能。參考了網上MySQL備份的指令碼，如有不足的地方，歡迎大家拍磚！

1： 使用前，請先做一些配置選項修改，例如備份路徑設定、連線資料庫的賬號密碼等等。

2： 在BACKUP_DIR目錄下，建立full、incr、logs、scripts四個目錄，將mysql_backup.sh放置在scripts目錄下。

#!/bin/bash 
 
###################################################################################


# This script is used for MySQL daily backup. it is a package of Xtrabackup shell


# script       #


#                                                                                 #


# #################################################################################


#                                                                                 #


# ScriptName    :    mysql_backup.sh                                              #


# Author        :    瀟湘隱者                                              #


# CerateDate    :    2016-04-24                                 #


# Blogs        :   www.cnblogs.com/kerrycode                                    #


# @author       :   kerry2008code@qq.com                                          #


#*********************************************************************************#


# Version        Modified Date            Description                             #


#*********************************************************************************#


# V.1.0          2016-04-24          create the script for mysql daily backup     #


###################################################################################


 


 


 


BACKUP_DATE=$(date +%Y-%m-%d_%H_%M_%S)


BACKUP_DIR=/backup/mysql


FULLBACKUP_PATH=$BACKUP_DIR/full


INCRBACKUP_PATH=$BACKUP_DIR/incr


BACKUP_LOG_PATH=$BACKUP_DIR/logs


BACKUP_KEEP_DAY=7


MYSQL_CONF=/usr/my.cnf


INNOBACKUPEX=/usr/bin/innobackupex


MYSQL_CMD=/usr/bin/mysql


MYSQL_CONNECT="--host=127.0.0.1 --user=root --password=123456 --port=3306"


MAIL_FROM="root@`hostname`"


MAIL_TO=konglb@xxx.com




 


error()


{


 echo "$1" 1>&2


 exit 1


}


 


#before the backup, check the system enviroment setting and mysql status and so on


mysql_backup_check()


{


 


    if [ ! -d $FULLBACKUP_PATH ];then


        mkdir -p $FULLBACKUP_PATH


    fi


    


    if [ ! -d $INCRBACKUP_PATH ];then


        mkdir -p $INCRBACKUP_PATH


    fi


    


    if [ ! -d $BACKUP_LOG_PATH ];then


        mkdir -p $BACKUP_LOG_PATH


    fi


    


    


    if [ ! -x $INNOBACKUPEX ];then


       error "$INNOBACKUPEX did not exists"


    fi


    


    if [ ! -x $MYSQL_CMD ];then


       error "mysql client did not exists!"


    fi


    


     mysql_status=`netstat -nl | awk `NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}``  


      


        if [ "$mysql_status" != "Yes" ];then  


          error "MySQL did not start. please check it"  


        fi


   


        if ! `echo `exit` | $MYSQL_CMD -s $MYSQL_CONNECT` ; then  


         error "please check the user and password is correct!"  


        fi  


}


 


 


xtra_backup()


{


  if [ $# = 2 ];then


        $INNOBACKUPEX --defaults-file=$MYSQL_CONF $MYSQL_CONNECT  --no-timestamp  $1/full_$BACKUP_DATE>$2 2>&1


  elif [ $# = 3 ];then


        $INNOBACKUPEX  --defaults-file=$MYSQL_CONF $MYSQL_CONNECT  --no-timestamp --incremental  $1/incr_$BACKUP_DATE  --incremental-basedir $2 >$3 2>&1


  else


      error "the parameter is not correct"


  fi


}


 


 


lastest_fullback_dir()


{


    if [ -d $1 ]; then


        path=`ls -t $1 |head -n 1`


        if [  $path ]; then


            echo $path


        else


            error "lastest_fullback_dir(): 目錄為空,沒有最新目錄"


        fi


    else


        error "lastest_fullback_dir(): 目錄不存在或者不是目錄"


    fi


}


 


 


mysql_full_backup()


{


    xtra_backup  $FULLBACKUP_PATH $BACKUP_LOG_PATH/full_$BACKUP_DATE.log


    


    if [ "${PIPESTATUS[0]}" -ne 0 ]; then


        (echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";


      cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;


    ) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}


    else


      (echo "Subject: MySQL Backup Success: MySQL Backup failed on `hostname`";


      cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;


    ) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}


    fi


 


    


    cd $FULLBACKUP_PATH 


    


    ls -t | tail -n +$BACKUP_KEEP_DAY | xargs rm -rf


}


 


mysql_incr_backup()


{


 


  LATEST_FULL_BACKUP=`find $FULLBACKUP_PATH -mindepth 1 -maxdepth 1 -type d -printf "%P
" | sort -nr | head -1` 


  


  LATEST_FULL_BACKUP_TIME=`stat -c %Y $FULLBACKUP_PATH/$LATEST_FULL_BACKUP`


  


  if [ $LATEST_FULL_BACKUP ];then


        #


        xtra_backup $INCRBACKUP_PATH  $FULLBACKUP_PATH/`lastest_fullback_dir $FULLBACKUP_PATH`  $BACKUP_LOG_PATH/incr_$BACKUP_DATE.log 


  else


        # the first incremental backup need do full backup first 


         xtra_backup $FULLBACKUP_PATH  $BACKUP_LOG_PATH/full_$BACKUP_DATE.log 


         


         if [ "${PIPESTATUS[0]}" -ne 0 ]; then


                        (echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";


                   cat ${BACKUP_LOG_PATH}/incr_${BACKUP_DATE}.log;


                    ) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}


            fi


  fi


  


 


}


 


 


 


case $1 in


 


       full)


          mysql_backup_check


          mysql_full_backup


          ;;


 


        incr)


          mysql_backup_check


          mysql_incr_backup


          ;;


 


         *)


          echo "full backup parameter missed!"


          echo "incr backup parameter missed!"


          ;;


esac    














相關文章