基於Innobackupex的MySQL備份指令碼，innobackupexmysql

基於Innobackupex的MySQL備份指令碼，innobackupexmysql

    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 DevelopmentLeshami2014-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 <<EOFUsage: $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 passwordEOF}# Parse the input arguments and get the value of the input argument.if [ $# -eq 0 ];then      usage#      print_default      exit 0;fifunction 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}/physicallogDir=${backupDir}/logcheckPointDir=${backupDir}/checkpointcmdInno=/usr/bin/innobackupexsock=/tmp/mysql.sockday=`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}.logif [ "${day}" -eq 0 ] || [ "${day}" -eq 3 ];then   if [ ! -d "$physicalBackupDir/$dt" ];then      echo "mkdir -p $physicalBackupDir/$dt"      mkdir -p $physicalBackupDir/$dt       fifiif [ -z "$defaultFile" ]; then    defaultFile=/etc/my.cnffiif [ ! -d "${logDir}" ]; then    mkdir -p ${logDir}fiif [ ! -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=5find ${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   fielse   echo "The hotbackup logfile was not found on `hostname`." | \      mutt -s "Failed backup for MySQl hotbackup on `hostname`." $mailaddfi   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=InnoBKSHELL> crontab -l0 3 * * * /db_scripts/call_innobk.shSHELL> cd /data/backupSHELL> lscheckpoint  log  physicalSHELL> cd physical/SHELL> ls20141228  20141231  20150104  20150107  SHELL> cd 20150107SHELL> ls -hltrtotal 16Kdrwxr-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     #增備