基于Innobackupex的MySQL备份脚本

    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 last_lsn $physicalBackupDir/$dt/base_$dt/xtrabackup_checkpoints|cut -b 12- >$checkPointDir/ckp_${dt}         }                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 last_lsn $basedir/inc_$dt/xtrabackup_checkpoints|cut -b 12- >$checkPointDir/ckp_$dt         }                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=InnoBK                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     #增备