mysqldump全量备份+mysqlbinlog增量备份脚本

# Author: laizhiyuan
# Date: 20190915
# Desc: Timed online logic backup MySQL By mysqldump cmd


#!/bin/bash


# 备份目的地IP
BACK_DEST_IP=xx.xx.xx.xx
# 备份目的地端口
BACK_DEST_PORT=22
# 备份目的地用户
BACK_DEST_USER=root


# 目的地备份目录
BACK_DEST_DIR=/opt/backup/mysql/logic/full
# 本地备份目录
BACK_DIR=/opt/backup/mysql/logic/full


# 脚本工作目录
WORK_DIR=/tmp


# mysql工作目录
MYSQL_BASEDIR=/usr/local/mysql


# mysqldump命令参数
MYSQLDUMP_HOST=localhost
MYSQLDUMP_PORT=3306
MYSQLDUMP_USER=root


# 检查目录和环境
function check_mkdir_and_env(){


        test ! -d $BACK_DIR && mkdir -p $BACK_DIR || echo "" > dev/null




        ssh -p $BACK_DEST_PORT $BACK_DEST_USER@$BACK_DEST_IP "test ! -d $BACK_DEST_DIR && mkdir -p $BACK_DEST_DIR || echo ''"


        # 导出环境变量
        export MYSQL_HOME=$MYSQL_BASEDIR
        PATH=$PATH:$MYSQL_HOME/bin
        export PATH
}


function backup(){


        # 备份
        TIME_DIR=`date +%Y-%m-%d_%H-%M-%S`
        mysqldump -h$MYSQLDUMP_HOST -P$MYSQLDUMP_PORT -u$MYSQLDUMP_USER --single-transaction --flush-logs -h$MYSQLDUMP_HOST --master-data=2 --routines --triggers --flush-privileges --default-character-set=utf8 --all-databases > $BACK_DIR/$TIME_DIR.sql || exit 2






        # 压缩
        tar -zcvf $TIME_DIR.tar.gz $BACK_DIR/$TIME_DIR.sql


        # 发送到远程机子
        scp -P $BACK_DEST_PORT -r $TIME_DIR.tar.gz $BACK_DEST_USER@$BACK_DEST_IP:$BACK_DEST_DIR/ || exit 2


        # 清除归档
        rm -rf $TIME_DIR.tar.gz




}


# 清空本地所有备份
function cleanup_local(){


        rm -rf $BACK_DIR/*
}


# 清空本地和远程机子所有备份,一般调试时使用，正常情况下慎用!
function cleanup_all(){


        cleanup_local
         ssh -p $BACK_DEST_PORT $BACK_DEST_USER@$BACK_DEST_IP "rm -rf $BACK_DEST_DIR"
}


# 进入工作目录
cd $WORK_DIR
# 检查目录和环境
check_mkdir_and_env


# 根据参数执行函数
if [ "$1" = 'cleanup_local' ]; then
        cleanup_local
elif [ "$1" = 'cleanup_all' ]; then
        cleanup_all
elif [ "$1" = 'backup' ]; then
        backup
else
    echo "You must enter parameters backup or cleanup_local or cleanup_all"
    exit 2
fi

# 备份
sh mysql_mysqldump_backup.sh backup


# 清空本地测试数据
sh mysql_mysqldump_backup.sh cleanup_local


# 清空本地和远程机子测试数据
sh mysql_mysqldump_backup.sh cleanup_all

# Author: laizhiyuan
# Date: 20190915
# Desc: Online incremental backup of binary logs


#!/bin/bash


# 备份目的地IP
BACK_DEST_IP=xx.xx.xx.xx
# 备份目的地端口
BACK_DEST_PORT=22
# 备份目的地用户
BACK_DEST_USER=root


# 备份目的地备份目录
BACK_DEST_DIR=/opt/backup/mysql/binlog
# 本地备份目录
BACK_DIR=/opt/backup/mysql/binlog


# 二进制日志文件前缀
BINLOG_PRE=mysql-bin
# 二进制日志目录
BINLOG_DIR=/usr/local/mysql/data
# 二进制日志编号文件名称
BINLOG_NUMBER_FILE_NAME=mysql-bin.index
# 二进制日志编号记录文件
BINLOG_NUMBER_FILE=$BINLOG_DIR/$BINLOG_NUMBER_FILE_NAME
# 最后一次备份的二进制日志编号记录文件
LAST_BACK_BINLOG_NUMBER_FILE=$BACK_DIR/mysql-bin.index


# 脚本工作目录
WORK_DIR=/tmp


# 检查目录和环境
function check_mkdir_and_env(){


        test ! -d $BACK_DIR && mkdir -p $BACK_DIR || echo "" > dev/null




        ssh -p $BACK_DEST_PORT $BACK_DEST_USER@$BACK_DEST_IP "test ! -d $BACK_DEST_DIR && mkdir -p $BACK_DEST_DIR || echo ''"


        # 导出环境变量
        export MYSQL_HOME=$MYSQL_BASEDIR
        PATH=$PATH:$MYSQL_HOME/bin
        export PATH
}


# 备份
function backup(){


        # 时间作为备份目录前缀
        TIME_DIR_PRE=`date +%Y-%m-%d_%H-%M-%S_`


        # 本次备份文件编号上限值，本次备份<=上限值，截取000001格式
        MAX_NUMBER=`less $BINLOG_NUMBER_FILE | awk 'END {print}' | sed 's/.\/mysql-bin.//g'`


        # 滚动前先备份下mysql-bin.index文件
        cp $BINLOG_NUMBER_FILE $BACK_DIR/$TIME_DIR_PRE$BINLOG_NUMBER_FILE_NAME


         # 滚动日志
        mysql -e "flush logs";


        # 本次备份文件编号下限值，本次备份>=下限值，截取000001格式
        MIN_NUMBER="000000"
        if  [ -f $LAST_BACK_BINLOG_NUMBER_FILE ]; then
                # 上一次备份最大编号，截取000001格式
                MIN_NUMBER=`less $LAST_BACK_BINLOG_NUMBER_FILE | awk 'END {print}' | sed 's/.\/mysql-bin.//g'`
        fi


        # 循环所有二进制日志文件
        for binlog_file in `ls $BINLOG_DIR | grep "$BINLOG_PRE*"`
        do


                # 截取为000001格式
                NOW_BINLOG_NUMBER=`echo $binlog_file | sed 's/mysql-bin.//g'`


                # 排除mysql-bin.index文件
                if [ "$binlog_file" = "$BINLOG_NUMBER_FILE_NAME" ]; then
                        continue
                fi


                # 判断文件编号>=下限值
                test "$NOW_BINLOG_NUMBER" -ge "$MIN_NUMBER" && IS_BACK="ok" || IS_BACK="no"


                if [ "$IS_BACK" = "ok" ]; then


                        # 判断文件编号<=上限值
                        test "$NOW_BINLOG_NUMBER" -le "$MAX_NUMBER" && IS_BACK="ok" || IS_BACK=“no”


                        if [ "$IS_BACK" = "ok" ]; then


                                # 备份动作
                                cp $BINLOG_DIR/$binlog_file $BACK_DIR/$TIME_DIR_PRE$binlog_file
                        fi
                fi
        done


        # 删除上次备份留下的编号文件
        rm -rf $BACK_DIR/$BINLOG_NUMBER_FILE_NAME


        # 发送本次增量备份到远程机子
        scp -P $BACK_DEST_PORT -r $BACK_DIR/* $BACK_DEST_USER@$BACK_DEST_IP:$BACK_DEST_DIR/


        # 清空备份目录
        rm -rf $BACK_DIR/*


        # 更新下次备份文件编号下限值
        cp $BINLOG_NUMBER_FILE $BACK_DIR/$BINLOG_NUMBER_FILE_NAME


}




# 清空本地所有备份
function cleanup_local(){


        rm -rf $BACK_DIR/*
}


# 清空本地和远程机子所有备份,一般调试时使用，正常情况下慎用!
function cleanup_all(){


        cleanup_local
        ssh -p $BACK_DEST_PORT $BACK_DEST_USER@$BACK_DEST_IP "rm -rf $BACK_DEST_DIR"
}


# 进入工作目录
cd $WORK_DIR
# 检查目录和环境
check_mkdir_and_env


# 根据参数执行函数
if [ "$1" = 'cleanup_local' ]; then
        cleanup_local
elif [ "$1" = 'cleanup_all' ]; then
        cleanup_all
elif [ "$1" = 'backup' ]; then
        backup
else
        echo "You must enter parameters backup or cleanup_local or cleanup_all"
        exit 2
fi

# 备份
sh mysql_mysqlbinlog_backup.sh backup


# 清空本地测试数据
sh mysql_mysqlbinlog_backup.sh cleanup_local


# 清空本地和远程机子测试数据
sh mysql_mysqlbinlog_backup.sh cleanup_all