基于Innobackupex的MySQL备份脚本

ゝ一世哀愁。 2022-08-05 16:29 122阅读 0赞

Innobackupex是Xtrabackup的一部分，其实质也是调用xtrabackup。主要的不同是Xtrabackup除了支持innodb引擎外还支持xtradb引擎。本文主要封装了Innobackupex到shell脚本进行定期备份，供大家参考。

1、脚本描述  
a、支持增量备份以及全备  
b、需要传递到备份脚本(如备份路径，连接相关参数等)  
c、基于周日，周三的全量备份，其他增量备份  
d、可根据需要调整脚本，比如压缩备份的文件夹以及rsync等

2、脚本内容

################################################################################
    # File     : innobk.sh                                                         # 
    # Author   : Leshami                                                           #     
    # Blog     : http://blog.csdn.net/leshami                                      #
    # Date     : 20141113                                                          #
    # Description :                                                                #
    #    The script will call innobackupex to                                      #
    #    take a full or increment backup for mysql db.                             #
    #    Currently it will take follow principal to backup:                        #
    #       Sun,Wend take full backup.                                             #  
    #       Mon,Tue,Thur,Fri,Sat take incremental backup.                          #
    #                                                                              #
    # Usage Example:                                                               #
    #     innobk.sh --help|-?                                                      #
    #     innobk.sh --backup-dir=/dbbak --defaults-file=/inst3606/my3606.cnf \     #
    #                   --host=127.0.0.1 --port=3606 --user=xxx --password=xxx     #
    #		                                                                       #
    ################################################################################
    # Change History:                                                              #
    # --------------------------------------------------                           #     
    # Init Development	Leshami		2014-11-13                                     #
    ################################################################################
    
    #!/bin/bash
    #set -x
    
    # Get the key value of input arguments format like '--args=value'.
    function get_key_value()
    {
        echo "$1" | sed 's/^--[a-zA-Z_-]*=//' 
    }
    
    # Usage will be helpful when you need to input the valid arguments.
    function usage()
    {
    cat <<EOF
    Usage: $0 [configure-options]
      -?, --help                Show this help message.
      --backup-dir=<>           Set backup directory
      --defaults-file=[]        Set mysql configuration file directory
      --host=<>                 Set mysql host
      --port=<>                 Set mysql port
      --user=<>                 Set mysql user name
      --password=<>             Set mysql user password
    EOF
    }
    
    # Parse the input arguments and get the value of the input argument.
    if [ $# -eq 0 ];then
          usage
    #      print_default
          exit 0;
    fi
    
    function parse_options()
    {
      while test $# -gt 0
      do
        case "$1" in
        --backup-dir=*)
          backupDir=`get_key_value "$1"`;;
        --defaults-file=*)
          defaultFile=`get_key_value "$1"`;;
        --host=*)
          Host=`get_key_value "$1"`;;
        --port=*)
          mysqlPort=`get_key_value "$1"`;;
        --user=*)
          mysqlUser=`get_key_value "$1"`;;
        --password=*)
          mysqlPassword=`get_key_value "$1"`;;
        -? | --help )
          usage
    #      print_default
          exit 0;;
        *)
          echo "Unknown option '$1'"
          exit 1;;
        esac
        shift
      done
    }
    
    # Call the parse_options function to parse the input arguments and initialisze env.
    parse_options "$@"
    physicalBackupDir=${backupDir}/physical
    logDir=${backupDir}/log
    checkPointDir=${backupDir}/checkpoint
    cmdInno=/usr/bin/innobackupex
    sock=/tmp/mysql.sock
    
    day=`date +%w`
    lastday=`date -d '1 days ago' +%Y%m%d`
    dt=`date +%Y%m%d`
    ts=`date +%Y%m%d%H%M%S`
    logFile=${backupDir}/log/innobak_${ts}.log
    
    if [ "${day}" -eq 0 ] || [ "${day}" -eq 3 ];then
       if [ ! -d "$physicalBackupDir/$dt" ];then
          echo "mkdir -p $physicalBackupDir/$dt"
          mkdir -p $physicalBackupDir/$dt    
       fi
    fi
    
    if [ -z "$defaultFile" ]; then
        defaultFile=/etc/my.cnf
    fi
    
    if [ ! -d "${logDir}" ]; then
        mkdir -p ${logDir}
    fi
    
    if [ ! -d "${checkPointDir}" ]; then
        mkdir -p ${checkPointDir}
    fi  
    
    echo "Start innobackup at `date`."               >>${logFile} 
    echo "Current defaults file is : ${defaultFile}" >>${logFile}
    echo "Current host is : ${Host}"                 >>${logFile}
    echo "Current port is : ${mysqlPort}"            >>${logFile}   
    echo "Current mysql user is : ${mysqlUser}"      >>${logFile}    
    echo "Current password is : ${mysqlPassword}"    
    echo "Current log directory is : ${logDir}"      >>${logFile}
    echo "Current log file is : ${logFile}"          >>${logFile}
    
    # Define backup function for full and incremental backup type.
    function back_full()
    {
    echo "$cmdInno --user=$mysqlUser --password=$mysqlPassword  --no-timestamp \
          --defaults-file=$defaultFile $physicalBackupDir/$dt/base_${dt} \
          --socket=$sock 2> ${logDir}/bak_$ts.log" >>${logFile}
    
       $cmdInno --user=$mysqlUser --password=$mysqlPassword  --no-timestamp \
       --defaults-file=$defaultFile $physicalBackupDir/$dt/base_$dt --socket=$sock 2> ${logDir}/bak_${ts}.log
       grep to_lsn $physicalBackupDir/$dt/base_$dt/xtrabackup_checkpoints|cut -b 10- >$checkPointDir/ckp_${dt} 
    }            #将grep last_lsn改成grep to_lsn @20150203  #更正cut后位数12到10 @20150212
    
    function back_inc()
    {
    echo "   $cmdInno --user=$mysqlUser --password=$mysqlPassword --socket=$sock --no-timestamp \  
       --defaults-file=$defaultFile --incremental $basedir/inc_$dt \
       --incremental-lsn=`cat $checkPointDir/ckp_$lastday` 2>${logDir}/bak_${ts}.log " >>${logFile}
    
      $cmdInno --user=$mysqlUser --password=$mysqlPassword --port=${mysqlPort} --socket=$sock --no-timestamp \
     --defaults-file=$defaultFile --incremental $basedir/inc_$dt \
     --incremental-lsn=`cat $checkPointDir/ckp_$lastday` 2>${logDir}/bak_${ts}.log
      grep to_lsn $basedir/inc_$dt/xtrabackup_checkpoints|cut -b 10- >$checkPointDir/ckp_$dt 
    }           #将grep last_lsn改成grep to_lsn @20150203  #更正cut后位数12到10 @20150212
    
    case $day in
        0)
            # Sunday Full backup
            back_full
            ;;
        1)
            # Monday Relatively Sunday's incremental backup
            basedir=$physicalBackupDir/`date -d "1 days ago" +%Y%m%d` 
            back_inc
            ;;
        2)
            # Tuesday Compared with Monday's incremental backup
            basedir=$physicalBackupDir/`date -d "2 days ago" +%Y%m%d`
            back_inc
            ;;
        3)
            # Wednesday Full backup
            back_full
            ;;
        4)
            # Thursday  Relatively Wednesday's incremental backup
            basedir=$physicalBackupDir/`date -d "1 days ago" +%Y%m%d`
            back_inc
            ;;
        5)
            # Friday Compared with Thursday's incremental backup
            basedir=$physicalBackupDir/`date -d "2 days ago" +%Y%m%d`
            back_inc
            ;;
        6)
            # Saturday Compared with Friday's incremental backup
             basedir=$physicalBackupDir/`date -d "3 days ago" +%Y%m%d`
            back_inc
            ;;
    esac
    
    # Check backup log ,remove history logfile and bacupset.
    retention=5
    
    find ${physicalBackupDir} -type f -mtime +$retention -exec rm {} \;
    find ${logDir} -type f -mtime +$retention -exec rm {} \;
    
    # Send mail for backup result.
    echo "" >>${logFile}
    echo "Below is detail log for current innobackup.">>${logFile}
    cat ${logDir}/bak_${ts}.log >>${logFile}
    mailadd='jack@12306.cn,ww@12306.cn'
    
    if [ -e "${logFile}" ]; then
       status=`grep -i "innobackupex: completed OK!" ${logFile}`
       if [ -n "${status}" ]; then
          cat ${logFile} |mutt -s "Successful backup for MySQL hotbackup on `hostname`." $mailadd
       else 
          cat ${logFile} |mutt -s "Failed backup for MySQl hotbackup on `hostname`." $mailadd
       fi
    else
       echo "The hotbackup logfile was not found on `hostname`." | \   
       mutt -s "Failed backup for MySQl hotbackup on `hostname`." $mailadd
    fi
       
    exit

3、调用示例

SHELL> more call_innobk.sh 
    #!/bin/bash
    /db_scripts/innobk.sh --backup-dir=/data/backup --host=127.0.0.1 --port=3306 --user=innobk --password=xxx
    
    SHELL> crontab -l
    0 3 * * * /db_scripts/call_innobk.sh
    
    SHELL> cd /data/backup
    SHELL> ls
    checkpoint  log  physical
    SHELL> cd physical/
    SHELL> ls
    20141228  20141231  20150104  20150107  
    SHELL> cd 20150107
    SHELL> ls -hltr
    total 16K
    drwxr-xr-x 9 root root 4.0K Jan  7 03:05 base_20150107    #全备
    drwxr-xr-x 9 root root 4.0K Jan  8 03:04 inc_20150108     #增备
    drwxr-xr-x 9 root root 4.0K Jan  9 03:03 inc_20150109     #增备
    drwxr-xr-x 9 root root 4.0K Jan 10 03:03 inc_20150110     #增备

[![鹏城DBA总群][DBA]][DBA_DBA]

[DBA]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_DBA]: /images/20220806/d99f2bf8a5714d5a83cc62633a16ff20.png