MySQL backup script based on Innobackupex

Innobackupex is part of Xtrabackup, and its essence is to call Xtrabackup. The main difference is that Xtrabackup supports the XTRADB engine in addition to supporting the InnoDB engine. This article mainly encapsulates the Innobackupex to shell script for regular backup, for your reference.

1. Script description
A, support for incremental backup and full provisioning
b, need to pass to backup script (such as backup path, connection related parameters, etc.)
C, full-scale backup based on Sunday, Wednesday, other incremental backups
D, can adjust the script as needed, such as compressing the backup folder and rsync, etc.

2. Script Content

################################################################################# File:innobk.sh     # # # # Author:leshami #                                                          # Blog:http://blog.csdn.net/leshami # # date:20141113    # # Description: # # The script would call Innobackupex to # # take a full or increment backup for Mys                             QL db.                                             # # Currently it'll 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 # # #####                                                              #############################################################################: # #--------------------------------------------------# # I NIT developmentleshami2014-11-13 ##################################################### #############################!/bin/bash#set-x# Get The key value of input arguments format like '--args=value '. function g Et_key_value () {echo "$" | sed ' s/^--[a-za-z_-]*=//'}# Usage would be helpful when you need to input the valid argumen Ts.function usage () {Cat &Lt;<eofusage: $ [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 th  E value of the input argument.if [$#-eq 0];then usage# print_default exit 0;fifunction parse_options () {    While test $#-GT 0 does case "$" in--backup-dir=*) backupdir= ' Get_key_value "$";;    --defaults-file=*) defaultfile= ' Get_key_value ' "$";;    --host=*) host= ' Get_key_value ' "$";;    --port=*) mysqlport= ' Get_key_value ' "$";;    --user=*) mysqluser= ' Get_key_value ' "$";;    --password=*) mysqlpassword= ' Get_key_value ' "$";; -? |      --HELP) usage# Print_defaultExit 0;;    *) echo "Unknown option" "Exit 1;; ESAC shift done}# Call the Parse_options function to parse the input arguments and Initialisze env.parse_options "[EMA Il protected] "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 was: ${defaultfile}" >>${logfile}echo "current Host is: ${host} ' >>${logfile}echo ' current port is: ${mysqlport} ' >>${logfile}    echo "Current MySQL user was: ${mysqluser}" >>${logfile} echo "Current password is: ${mysqlpassword}" echo "Current log directory was: ${logdir}" >>${logfile}echo "Current log file is: ${logfile}" &GT;&G t;${logfile}# Define backup function for full and incremental backup type.function back_full () {echo ' $cmdInno--user= $mysq       Luser--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--increm Ental $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 + $retentio N-exec RM {} \;find ${logdir}-type f-mtime + $retention-exec rm {} \;# Send mail for backup Result.echo "" >>${lo Gfile}echo "Below is detail logs for the current innobackup." >>${logfile}cat ${logdir}/bak_${ts}.log >>${logfile}mailadd= ' [email protected],[email  Protected] ' If [-E ' ${logfile} "]; Then status= ' Grep-i ' innobackupex:completedok! "${logfile} ' if [-N" ${status} "]; Then cat ${logfile} |mutt-s "Successful backup for MySQL hotbackup on ' hostname '." $mailadd Else cat ${logfi Le} |mutt-s "Failed Backup for MySQl hotbackup on ' hostname '." $mailadd fielse echo "The hotbackup logfile is not fo und on ' hostname '. "| \ mutt-s "Failed Backup for MySQl hotbackup on ' hostname '." $mailaddfi exit

3. Invoking the 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/backupshel l> 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_2015 0109     #增备drwxr-xr-x 9 root root 4.0K Jan 03:03 inc_20150110     #增备
   

MySQL backup script based on Innobackupex