【MySQL】Xtrabackup备份及恢复脚本

简介:

此备份脚本的策略是每周日和周三进去全备 其余每天增量备份。

# 备份脚本XtraBackup.sh:

#!/bin/bash
# filename      : XtraBackup.sh
# Author        : wang
day=`date +%w`
dt=`date +%Y%m%d`
lastday=`date -d '1 days ago' +%Y%m%d`
user=root
pwd='xxxxx'
log=backuplog.`date +%Y%m%d`

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

#!/bin/bash
# filename      : xtrabackup_recover.sh
# Author        : wang
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=root
pwd='xxxxxxx'
log=recoverlog.`date +%Y%m%d`
datefile=/mysqldata

case $day in  
    0)  
        # Sunday Recover Database 
        innobackupex --apply-log  /backup/xtra_base_$dt > /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$dt >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start
        binlog=`cat /backup/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    1)  
        # Monday Recover Database 
        innobackupex --apply-log --redo-only /backup/xtra_base_$lastday > /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastday/ --incremental-dir=/backup/xtra_inc_$dt/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastday >> /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$lastday >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start
        binlog=`cat /backup/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    2)  
        # Tuesday Recover Database
        innobackupex --apply-log --redo-only /backup/xtra_base_$lasttwoday > /tmp/$log 2>&1
        innobackupex --apply-log --redo-only /backup/xtra_base_$lasttwoday/ --incremental-dir=/backup/xtra_inc_$lastday/ >> /tmp/$log 2>&innobackupex --apply-log  /backup/xtra_base_$lasttwoday/ --incremental-dir=/backup/xtra_inc_$dt/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lasttwoday >> /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-fil=/etc/my.cnf --copy-back /backup/xtra_base_$lasttwoday >> /tmp/$$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start
        binlog=`cat /backup/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    3)  
        # Wednesday Recover Database 
        innobackupex --apply-log  /backup/xtra_base_$dt > /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$dt >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start 
        binlog=`cat /backup/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$dt/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    4)  
        # Thursday  Recover Database 
        innobackupex --apply-log --redo-only /backup/xtra_base_$lastday > /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastday/ --incremental-dir=/backup/xtra_inc_$dt/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastday >> /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$lastday >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start 
        binlog=`cat /backup/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$lastday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    5)  
        # Friday Recover Database  
        innobackupex --apply-log --redo-only /backup/xtra_base_$lasttwoday > /tmp/$log 2>&1
        innobackupex --apply-log --redo-only /backup/xtra_base_$lasttwoday/ --incremental-dir=/backup/xtra_inc_$lastday/ >> /tmp/$log 2>&innobackupex --apply-log  /backup/xtra_base_$lasttwoday/ --incremental-dir=/backup/xtra_inc_$dt/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lasttwoday >> /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$lasttwoday >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start
        binlog=`cat /backup/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$lasttwoday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
    6)  
        # Saturday Recover Database  
        innobackupex --apply-log --redo-only /backup/xtra_base_$lastthreeday > /tmp/$log 2>&1
        innobackupex --apply-log --redo-only /backup/xtra_base_$lastthreeday/ --incremental-dir=/backup/xtra_inc_$lasttwoday/ >> /tmp/$log 2>&1
        innobackupex --apply-log --redo-only /backup/xtra_base_$lastthreeday/ --incremental-dir=/backup/xtra_inc_$lastday/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastthreeday/ --incremental-dir=/backup/xtra_inc_$dt/ >> /tmp/$log 2>&1
        innobackupex --apply-log  /backup/xtra_base_$lastthreeday >> /tmp/$log 2>&1
        service mysqld stop
        rm -rf $datefile/*
        innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xtra_base_$lastthreeday >> /tmp/$log 2>&1
        chown -R mysql:mysql $datefile
        service mysqld start
        binlog=`cat /backup/xtra_base_$lastthreeday/xtrabackup_binlog_info|awk '{print $1}'`
        pos=`cat /backup/xtra_base_$lastthreeday/xtrabackup_binlog_info|awk '{print $2}'`
        mysqlbinlog --no-defaults --start-position=$pos /mysqllog/$binlog | mysql -u$user -p$pwd
        ;;  
esac 
find /tmp -mtime +6 -type f -name 'recoverlog.*' -exec rm -rf {} \;

文章作者: Ciwei
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Ciwei !
 上一篇
【MySQL】如何优雅的备份账号相关信息 【MySQL】如何优雅的备份账号相关信息
前言: 最近遇到实例迁移的问题,数据迁完后还需要将数据库用户及权限迁移过去。进行逻辑备份时,我一般习惯将MySQL系统库排除掉,这样备份里面就不包含数据库用户相关信息了。这时候如果想迁移用户相关信息 可以采用以下三种方案,类似的 我们也可以
2019-07-22
下一篇 
【MySQL】Xtrabackup备份工具使用 【MySQL】Xtrabackup备份工具使用
简介: Xtrabackup是由percona提供的mysql数据库备份工具,支持在线热备份(备份时不影响数据读写). Xtrabackup有两个主要的工具:xtrabackup、innobackupex 1、xtraback
2019-07-22
  目录