基于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 </pre><br />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 #增備 </pre><br />
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！