MySQL innobackupex 备份工具脚本

全备脚本

比如每周日凌晨1点整进行全备一次.

#!/bin/bash
# innobackupex_full.sh
# for mysql5.1,5.5,5.6
# wxg
# 2015/12/2
#********** crontab e.g. **********
# 0 1 * * 0  sh /path/innobackupex_full.sh
# 0 1 * * 1,2,3,4,5,6 sh /path/innobackupex_incremental.sh
#**********************************


XtraBindir=/usr/local/src/percona-xtrabackup-2.3.2-Linux-x86_64/bin
XtraBindirFor51=/usr/local/src/percona-xtrabackup-2.0.8/bin
BackupDbs="levin alice"
BackupDir=/backups/mysql
FullBackupDir=
BackupTmpLog=/tmp/backup.log

MysqlBasedir=/usr/local/mysql5627
MysqlEtcFile=$MysqlBasedir/etc/my.cnf
MysqlUser=root
MysqlPassword=mysql
MysqlSocket=$MysqlBasedir/tmp/mysql.sock
MysqlVersion=$($MysqlBasedir/bin/mysql -V|egrep -o 'Distrib [0-9.]+'|awk -F. '{print $2}')

[ -z "$FullBackupDir" ] &&
    FullBackupDir=$BackupDir/week$(date +%V)/full_$(date +%Y%m%d)

[ -s $BackupTmpLog ] && >$BackupTmpLog

log(){
    Time=$(date +%F" "%T)
    echo "$Time [$1]: $2" >>$BackupTmpLog 2>&1
}

pipelog(){
    while read line;do
        Time=$(date +%F" "%T)
        echo $line|sed "s/^/$Time [$1]: /" >>$BackupTmpLog 2>&1
    done
}

full_backup(){
    Bindir=$1
    SlaveOrNo=$( $MysqlBasedir/bin/mysql -u$MysqlUser -p$MysqlPassword -NBe "show slave status" 2>/dev/null|
        grep -v "Logging to file" )
    [ -z "$SlaveOrNo" ] && SlaveOption="" || SlaveOption="--slave-info"
    export PATH="$PATH:$Bindir:$MysqlBasedir/bin"
    log "NOTE" "use innobackupex: $Bindir/innobackupex."
    log "NOTE" "full backup mysql: $MysqlBasedir."
    log "NOTE" "backup to: $FullBackupDir."
    (
    exec 2>&1
    $Bindir/innobackupex \
        --defaults-file="$MysqlEtcFile" \
        --user="$MysqlUser" \
        --password="$MysqlPassword" \
        --socket="$MysqlSocket" \
        $SlaveOption \
        --no-timestamp \
        --databases="$BackupDbs" \
        "$FullBackupDir"
    ) | pipelog "innobackupex full-backup"
}

[ -d "$FullBackupDir" ] && {
    log "ERROR" "$FullBackupDir already exists.Aborting!"
    exit 1
}

[ ! -d "${FullBackupDir%/*}" ] && mkdir -p ${FullBackupDir%/*}

[ "$MysqlVersion" -eq 1 ] &&
    full_backup $XtraBindirFor51 ||
        full_backup $XtraBindir

mv $BackupTmpLog $FullBackupDir

tail -1 $FullBackupDir/backup.log | grep -q "completed OK!" && echo "success." || echo "failed."


exit 0

增量备份脚本

每周一到周六的凌晨1点整进行增量备份.

#!/bin/bash
# innobackupex_incremental.sh
# for mysql5.1,5.5,5.6
# wxg
# 2015/12/2
#********** crontab e.g. **********
# 0 1 * * 0  sh /path/innobackupex_full.sh
# 0 1 * * 1,2,3,4,5,6 sh /path/innobackupex_incremental.sh
#**********************************


XtraBindir=/usr/local/src/percona-xtrabackup-2.3.2-Linux-x86_64/bin
XtraBindirFor51=/usr/local/src/percona-xtrabackup-2.0.8/bin
BackupDbs="levin alice"
BackupDir="/backups/mysql"
BackupTmpLog=/tmp/backup.log
IncrementalBasedir=/backups/mysql/week49/increment1
Incremental=/backups/mysql/week49/increment2

# value: day of week (0..6), 0 is Sunday
FullBackupDay=0
IncrementalStartBackupDay=$(($FullBackupDay+1))

MysqlBasedir=/usr/local/mysql5627
MysqlEtcFile=$MysqlBasedir/etc/my.cnf
MysqlUser=root
MysqlPassword=mysql
MysqlSocket=$MysqlBasedir/tmp/mysql.sock
MysqlVersion=$($MysqlBasedir/bin/mysql -V|egrep -o 'Distrib [0-9.]+'|awk -F. '{print $2}')
Dayth=$(date +%w)

[ -s $BackupTmpLog ] && >$BackupTmpLog

log(){
    Time=$(date +%F" "%T)
    echo "$Time [$1]: $2" >>$BackupTmpLog 2>&1
}

pipelog(){
    while read line;do
        Time=$(date +%F" "%T)
        echo $line|sed "s/^/$Time [$1]: /" >>$BackupTmpLog 2>&1
    done
}

incremental_backup(){
    Bindir=$1
    SlaveOrNo=$($MysqlBasedir/bin/mysql -u$MysqlUser -p$MysqlPassword -NBe "show slave status" 2>/dev/null|
        grep -v "Logging to file" )
    [ -z "$SlaveOrNo" ] && SlaveOption="" || SlaveOption="--slave-info"

    export PATH="$PATH:$Bindir:$MysqlBasedir/bin"
    log "NOTE" "use innobackupex: $Bindir/innobackupex."
    log "NOTE" "incremental backup mysql: $MysqlBasedir."
    log "NOTE" "--incremental-basedir: $IncrementalBasedir"
    log "NOTE" "--incremental: $Incremental."
    (
    exec 2>&1
    $Bindir/innobackupex \
        --defaults-file="$MysqlEtcFile" \
        --user="$MysqlUser" \
        --password="$MysqlPassword" \
        --socket="$MysqlSocket" \
        --databases="$BackupDbs" \
        $SlaveOption \
        --no-timestamp \
        --incremental-basedir="$IncrementalBasedir" \
        --incremental \
        "$Incremental"
    ) | pipelog "innobackupex incremental-backup"
}

[ $Dayth -eq $FullBackupDay ] && {
    log "ERROR" "Today is $FullBackupDay day of a week,and excute full backup, not incremental backup."
    exit 1
}

[ -z "$IncrementalBasedir" ] && {
    [ $Dayth -eq $IncrementalStartBackupDay ] &&
        IncrementalBasedir=$BackupDir/week$(date +%V)/full_$(date -d "-1day" +%Y%m%d) ||
            IncrementalBasedir=$BackupDir/week$(date +%V)/incremental_$(date -d "-1day" +%Y%m%d)
}

[ -z "$Incremental" ] && {
    [ $Dayth -eq $IncrementalStartBackupDay ] &&
        Incremental=$BackupDir/week$(date +%V)/incremental_$(date +%Y%m%d) ||
            Incremental=$BackupDir/week$(date +%V)/incremental_$(date +%Y%m%d)
}

[ -d $Incremental ] && {
    log "ERROR" "$Incremental already exists. Aborting!"
    exit 1
}

[ $MysqlVersion -eq 1 ] &&
    incremental_backup $XtraBindirFor51 ||
        incremental_backup $XtraBindir

mv $BackupTmpLog $Incremental

tail -1 $Incremental/backup.log|grep -q "completed OK!" && echo "success." || echo "failed."


exit 0

从备份恢复数据

#!/bin/bash
# innobackupex_restore.sh
# for mysql5.1,5.5,5.6
# wxg
# 2015/12/3
#********** restore **********
#1. ./innobackupex_restore.sh --apply-log --redo-only fulldir
#   ./innobackupex_restore.sh --apply-log --redo-only fulldir --incremental-dir=incremental1
#   ./innobackupex_restore.sh --apply-log fulldir --incremental-dir=incremental2(last incremental not add "--redo-only".)
#   ./innobackupex_restore.sh --apply-log fulldir
#
#2. bin/mysql.server stop && mv var/ var.bak
#   ./innobackupex_restore.sh --copy-back fulldir
#   cp -r var.bak/mysql var/; cp -r var.bak/performance_schema var/(copy back databases which not backuped.)
#   chown -R mysql var/
#
#3. bin/mysqlbinlog --start-position=10 --stop-position=100 var.bak/mysql-bin.000007 >007.sql
#   bin/mysql.server start
#   bin/mysql -uroot -ppassword <007.sql
#
#4. check
#*****************************


XtraBindir=/usr/local/src/percona-xtrabackup-2.3.2-Linux-x86_64/bin
XtraBindirFor51=/usr/local/src/percona-xtrabackup-2.0.8/bin
UseMemory=1G
Args="$@"
WorkDir=$(cd $(dirname $0) && pwd)
RestoreLog=$WorkDir/restore.log

MysqlBasedir=/usr/local/mysql5627
MysqlEtcFile=$MysqlBasedir/etc/my.cnf
MysqlUser=root
MysqlPassword=mysql
MysqlSocket=$MysqlBasedir/tmp/mysql.sock
MysqlVersion=$($MysqlBasedir/bin/mysql -V|egrep -o 'Distrib [0-9.]+'|awk -F. '{print $2}')

log(){
    Time=$(date +%F" "%T)
    echo "$Time [$1]: $2" >>$RestoreLog 2>&1
}

pipelog(){
    while read line;do
        Time=$(date +%F" "%T)
        echo $line|sed "s/^/$Time [$1]: /" >>$RestoreLog 2>&1
    done
}

restore(){
    Bindir=$1
    export PATH="$PATH:$Bindir:$MysqlBasedir/bin"
    log "innobackupex" "$Args"
    (
    exec 2>&1
    $Bindir/innobackupex \
        --defaults-file="$MysqlEtcFile" \
        --user="$MysqlUser" \
        --password="$MysqlPassword" \
        --socket="$MysqlSocket" \
        --use_memory="$UseMemory" \
        $Args
    ) | pipelog "innobackupex restore"
}

[ $MysqlVersion -eq 1 ] &&
    restore $XtraBindirFor51 ||
        restore $XtraBindir

tail -1 $RestoreLog | grep -q "completed OK!" && echo "success." || echo "failed."


exit 0