shell编写mysql备份工具

 如需转载，请经本人同意。

这是之前写的一个备份脚本，调用的备份工具是xtrabackup

编写思路是：每周一全备份，备份后提取lSN号，对备份文件进行压缩，其余时候在LSN的基础上进行增量备份，并对30天外的备份数据进行清楚操作。该脚本对shell的基本语法都有覆盖，对快速入门shell会有帮助。

脚本语言：shell

#!/usr/bin/bash
# @author Jane.Hoo
# @written at:2015/12/21
# @GRANT SELECT, RELOAD, SUPER, LOCK TABLES, SHOW VIEW, EVENT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'xxxxxxxxx'
#


MY_DAY=`date +%A`
MY_TIMESTAMP=`date +%Y%m%d`
MY_DATABASEDIR=/mysql/databackup/
BACKUPWORKSPACE=$MY_DATABASEDIR'BACKUPSPACE'$MY_TIMESTAMP
DUMPFILE=''

USER=backupuser
PASSWORD=backuP_#
MYSQL_CONFIG=/etc/my.cnf
PARALLEL=10
DATABASES='"zabbix mysql test"'
TOOL='/usr/local/xtrabackup/bin/innobackupex'

LOGFILE=$MY_DATABASEDIR'LOG'$MY_TIMESTAMP
LSN=0
LSN_PATH=$MY_DATABASEDIR'LSN-RECORD.TXT'
echo "">$LOGFILE
if [ ! -d $BACKUPWORKSPACE ];then
    mkdir -p $BACKUPWORKSPACE
else
    echo $BACKUPWORKSPACE" is exits. program exit"|tee -a $LOGFILE
    exit
fi

if [ $MY_DAY = 'Monday' ];then
    echo "today is monday,ALL BACKUP "|tee -a $LOGFILE    
    command="$TOOL --defaults-file=$MYSQL_CONFIG --user=$USER --password=$PASSWORD  --parallel=$PARALLEL   --databases=$DATABASES  --no-timestamp  $BACKUPWORKSPACE 2>>$LOGFILE"
    eval $command
    
    cd $BACKUPWORKSPACE
    LSN=`cat xtrabackup_info |grep innodb_to_lsn|head -n 1|awk -F ' = ' '{print $2}'`
    echo $LSN>$LSN_PATH
    DUMPFILE='ALL_DUMPFILE'$MY_TIMESTAMP'.tar.gz'
    cd $MY_DATABASEDIR
    tar -zcvf $DUMPFILE 'BACKUPSPACE'$MY_TIMESTAMP >> $LOGFILE
    if [ $? -ne 0 ];then
            echo "compress fail!."|tee -a $LOGFILE
            exit
    else
            echo "compress ok."|tee -a $LOGFILE
    fi    
    rm -rf $BACKUPWORKSPACE        
    if [ $? -ne 0 ];then
                echo "remove workspace fail!."|tee -a $LOGFILE
                exit
        else
                echo "dump compeletly ok."|tee -a $LOGFILE
        fi
else
    echo "today is "$MY_DAY"increment backup ">>$LOGFILE
    cd $MY_DATABASEDIR
    LSN=`cat LSN-RECORD.TXT |head -n 1`
    command="$TOOL --defaults-file=$MYSQL_CONFIG --user=$USER --password=$PASSWORD  --parallel=$PARALLEL   --databases=$DATABASES  --incremental-lsn=$LSN --incremental --no-timestamp  $BACKUPWORKSPACE 2>>$LOGFILE"
    eval $command
    cd $BACKUPWORKSPACE
    LSN=`cat xtrabackup_info |grep innodb_to_lsn|head -n 1|awk -F ' = ' '{print $2}'`
    echo $LSN>$LSN_PATH
    DUMPFILE='ICR_DUMPFILE'$MY_TIMESTAMP'.tar.gz'
        cd $MY_DATABASEDIR
        tar -zcvf $DUMPFILE 'BACKUPSPACE'$MY_TIMESTAMP >> $LOGFILE
        if [ $? -ne 0 ];then
                echo "compress fail!."|tee -a $LOGFILE
                exit
        else
                echo "compress ok."|tee -a $LOGFILE
        fi
        rm -rf $BACKUPWORKSPACE
        if [ $? -ne 0 ];then
                echo "remove workspace fail!."|tee -a $LOGFILE
                exit
        else
                echo "dump compeletly ok."|tee -a $LOGFILE
        fi
fi

find $MY_DATABASEDIR -name "*.tar.gz" -type f -mtime +30 -exec rm {} \;>>$LOGFILE 2>&1
find $MY_DATABASEDIR -name "LOG*" -type f -mtime +30 -exec rm {} \;>>$LOGFILE 2>&1

 

作者：jane.hoo 
出处：jane.hoo的博客 http://www.cnblogs.com/janehoo/ 
[人生不设限,生命不息，折腾不止] 
您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。