MySQL 使用XtraBackup的shell脚本介绍

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       :   [e-mail]                                          #

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

# 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=[e-mail]

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\n" | 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    

然后根据备份策略设置crontab作业,例如如下所示

-----------------------------------------------------------------分割线------------------------------------------------------------------------------

在使用这个脚本中,发现了一个小bug,没有定期清理增量备份的历史备份,修正后的脚本如下所示.

#!/bin/bash 

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

#                                                                               #

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

#                                                                                      #

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

#                                                                                #

# ScriptName            :    mysql_backup.sh                                     #

# Author                :    Kerry                                               #

# CerateDate            :    2016-04-24                                          #

# Blogs                 :    www.cnblogs.com/kerrycode                           #

# @author               :    [e-mail]                                #

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

# Version        Modified Date            Description                            #

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

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

# V.1.1          2016-07-26     fix the bug increase backup did not rm the old backupset#

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

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

BACKUP_DIR=/backup/mysql/mysql_db_backup

FULLBACKUP_PATH=$BACKUP_DIR/full

INCRBACKUP_PATH=$BACKUP_DIR/incr

BACKUP_LOG_PATH=$BACKUP_DIR/logs

BACKUP_KEEP_DAY=30

MYSQL_CONF=/usr/my.cnf

INNOBACKUPEX=/usr/bin/innobackupex

MYSQL_CMD=/usr/bin/mysql

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

MAIL_FROM="root@`hostname`"

MAIL_TO="[e-mail]"

MAIL_TO_ERROR="[e-mail]"

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 Succeed 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

    find ${FULLBACKUP_PATH}  -mtime +$BACKUP_KEEY_DAY -name "*" -exec rm -rf {} \;

}

mysql_incr_backup()

{

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

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

  if [ $LATEST_FULL_BACKUP ];then

        #不是第一次增量备份，以最新的增量备份目录为base_dir

        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

  #cd ${INCRBACKUP_PATH}

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

  find ${INCRBACKUP_PATH}  -mtime +$BACKUP_KEEY_DAY -name "*" -exec rm -rf {} \;

}

case $1 in

       full)

          mysql_backup_check

          mysql_full_backup

          ;;

        incr)

          mysql_backup_check

mysql_incr_backup

          ;;

         *)

          echo "full backup"

          echo "incr backup"

          ;;

esac