MySQL backup script based on Innobackupex, innobackupexmysql

MySQL backup script based on Innobackupex, innobackupexmysql

Innobackupex is part of Xtrabackup, and its essence is to call xtrabackup. The main difference is that Xtrabackup not only supports the innodb engine, but also supports the xtradb engine. This article mainly encapsulates Innobackupex to shell scripts for regular backup for your reference.

 

1. Script description
A. Support Incremental backup and full backup
B. It must be passed to the backup script (such as the backup path and connection parameters)
C. Full backup Based on Sunday and Wednesday, and other Incremental Backup
D. You can adjust the script as needed, such as compressing the backup folder and rsync.

 

2. Script content

################################################################################# 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. Call example

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 20150107 SHELL> ls-hltrtotal limit 9 root 4.0 K Jan 7 05 base_20150107 # Full backup drwxr -xr-x 9 root 4.0 K Jan 8 inc_20150108 # Add drwxr-xr-x 9 root 4.0 K Jan 9 inc_20150109 # Add drwxr-xr-x 9 root root 4.0 K Jan 10 inc_20150110 # Add backup