基于Innobackupex的MySQL备份脚本 - 数据库编程

le} 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 1

基于Innobackupex的MySQL备份脚本(二)