MySQL数据库备份与恢复 Part 4：Xtrabackup 企业级备份实战

企业级备份策略

周日、周三进行全库备份，其余进行增量备份。

计划任务

# database Backup strategy --- create by ocean
* 2 * * * /app/bak/scripts/XtraBackup.sh

数据库备份脚本

#!/bin/bash
# filename      : XtraBackup.sh
# Author        : liyang
day=`date +%w`
dt=`date +%Y%m%d`
lastday=`date -d '1 days ago' +%Y%m%d`
user=backup
pwd=backup1234
log=backuplog.`date +%Y%m%d`
MYSQL_BASE=/app/mysql5.7
BAK_BASE=/app/bak
BAK_LOG=/app/bak/logs

case $day in  
    0)  
        # Sunday Full backup
        find $BAK_BASE -name "xtra_*" -mtime +31 -exec rm -rf {} \;
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd --no-timestamp $BAK_BASE/xtra_base_$dt > $BAK_LOG/$log 2>&1
        ;;  
    1)  
        # Monday Relatively Sunday's incremental backup  
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  $BAK_BASE/xtra_inc_$dt --incremental-basedir=$BAK_BASE/xtra_base_$lastday > $BAK_LOG/$log 2>&1  
        ;;  
    2)  
        # Tuesday Compared with Monday's incremental backup  
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  $BAK_BASE/xtra_inc_$dt --incremental-basedir=$BAK_BASE/xtra_inc_$lastday > $BAK_LOG/$log 2>&1     
        ;;  
    3)  
        # Wednesday Full backup
        find $BAK_BASE -name "xtra_*" -mtime +31 -exec rm -rf {} \;
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd --no-timestamp $BAK_BASE/xtra_base_$dt > $BAK_LOG/$log 2>&1   
        ;;  
    4)  
        # Thursday  Relatively Wednesday's incremental backup  
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  $BAK_BASE/xtra_inc_$dt --incremental-basedir=$BAK_BASE/xtra_base_$lastday > $BAK_LOG/$log 2>&1    
        ;;  
    5)  
        # Friday Compared with Thursday's incremental backup  
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  $BAK_BASE/xtra_inc_$dt --incremental-basedir=$BAK_BASE/xtra_inc_$lastday > $BAK_LOG/$log 2>&1    
        ;;  
    6)  
        # Saturday Compared with Friday's incremental backup  
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  $BAK_BASE/xtra_inc_$dt --incremental-basedir=$BAK_BASE/xtra_inc_$lastday > $BAK_LOG/$log 2>&1   
        ;;  
esac 
find $BAK_LOG -mtime +31 -type f -name 'backuplog.*' -exec rm -rf {} \;

恢复脚本

#!/bin/bash
# filename      : xtrabackup_recover.sh
# Author        : liyang 
day=`date +%w`
dt=`date +%Y%m%d`
lastday=`date -d '1 days ago' +%Y%m%d`
lasttwoday=`date -d '2 days ago' +%Y%m%d`
lastthreeday=`date -d '3 days ago' +%Y%m%d`
user=backup
pwd='backup1234'
rpwd='root1234'
log=recoverlog.`date +%Y%m%d`
MYSQL_BASE=/app/mysql5.7
BAK_BASE=/app/bak
BAK_LOG=/app/bak/logs
REC_DATA=/app/bak/mysqldata
REC_BIN_LOG=/app/bak/mysqllog


case $day in  
    0)  
        # Sunday Recover Database 
        innobackupex --apply-log  $BAK_BASE/xtra_base_$dt > $BAK_LOG/$log 2>&1
        $MYSQL_BASE/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$dt >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $BAK_BASE
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf &
        binlog=`cat $BAK_BASE/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    1)  
        # Monday Recover Database 
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lastday > $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastday/ --incremental-dir=$BAK_BASE/xtra_inc_$dt/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastday >> $BAK_LOG/$log 2>&1
        /app/mysql8/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$lastday >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf &
        binlog=`cat $BAK_BASE/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    2)  
        # Tuesday Recover Database
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lasttwoday > $BAK_LOG/$log 2>&1
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lasttwoday/ --incremental-dir=$BAK_BASE/xtra_inc_$lastday/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lasttwoday/ --incremental-dir=$BAK_BASE/xtra_inc_$dt/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lasttwoday >> $BAK_LOG/$log 2>&1
        /app/mysql8/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$lasttwoday >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf &
        binlog=`cat $BAK_BASE/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    3)  
        # Wednesday Recover Database 
        innobackupex --apply-log  $BAK_BASE/xtra_base_$dt > $BAK_LOG/$log 2>&1
        $MYSQL_BASE/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$BAK_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$dt >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf & 
        binlog=`cat $BAK_BASE/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    4)  
        # Thursday  Recover Database 
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lastday > $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastday/ --incremental-dir=$BAK_BASE/xtra_inc_$dt/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastday >> $BAK_LOG/$log 2>&1
        $MYSQL_BASE/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$lastday >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf & 
        binlog=`cat $BAK_BASE/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    5)  
        # Friday Recover Database  
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lasttwoday > $BAK_LOG/$log 2>&1
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lasttwoday/ --incremental-dir=$BAK_LOG/xtra_inc_$lastday/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_LOG/xtra_base_$lasttwoday/ --incremental-dir=$BAK_LOG/xtra_inc_$dt/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_LOG/xtra_base_$lasttwoday >> $BAK_LOG/$log 2>&1
        $MYSQL_BASE/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$lasttwoday >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf &
        binlog=`cat $BAK_BASE/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
    6)  
        # Saturday Recover Database  
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lastthreeday > $BAK_LOG/$log 2>&1
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lastthreeday/ --incremental-dir=$BAK_BASE/xtra_inc_$lasttwoday/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log --redo-only $BAK_BASE/xtra_base_$lastthreeday/ --incremental-dir=$BAK_BASE/xtra_inc_$lastday/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastthreeday/ --incremental-dir=$BAK_BASE/xtra_inc_$dt/ >> $BAK_LOG/$log 2>&1
        innobackupex --apply-log  $BAK_BASE/xtra_base_$lastthreeday >> $BAK_LOG/$log 2>&1
        $MYSQL_BASE/bin/mysqladmin -uroot -p$rpwd -S $MYSQL_BASE/tmp/mysql.sock shutdown &
        rm -rf $REC_DATA/*
        innobackupex --defaults-file=$MYSQL_BASE/my.cnf --copy-back $BAK_BASE/xtra_base_$lastthreeday >> $BAK_LOG/$log 2>&1
        chown -R mysql:mysql $REC_DATA
        $MYSQL_BASE/bin/mysqld_safe -uroot -p$rpwd --defaults-file=$MYSQL_BASE/my.cnf &
        binlog=`cat $BAK_BASE/xtra_base_$lastthreeday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat $BAK_BASE/xtra_base_$lastthreeday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos $REC_BIN_LOG/$binlog | mysql -u$user -p$pwd
        ;;  
esac 
#find $BAK_LOG -mtime +6 -type f -name 'recoverlog.*' -exec rm -rf {} \;