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

原创

wg_acaGciBB 2021-07-06 11:13:08 ©著作权

©著作权归作者所有：来自51CTO博客作者wg_acaGciBB的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql_mysqldump_backup.sh脚本代码：

# Author: laizhiyuan# Date: 20190915# Desc: Timed online logic backup MySQL By mysqldump cmd
#!/bin/bash
# 备份目的地IPBACK_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=localhostMYSQLDUMP_PORT=3306MYSQLDUMP_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_localelif [ "$1" = 'cleanup_all' ]; then        cleanup_allelif [ "$1" = 'backup' ]; then        backupelse    echo "You must enter parameters backup or cleanup_local or cleanup_all"    exit 2fi

执行方式：

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

mysql_mysqlbinlog_backup.sh脚本代码：

# Author: laizhiyuan# Date: 20190915# Desc: Online incremental backup of binary logs
#!/bin/bash
# 备份目的地IPBACK_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_localelif [ "$1" = 'cleanup_all' ]; then        cleanup_allelif [ "$1" = 'backup' ]; then        backupelse        echo "You must enter parameters backup or cleanup_local or cleanup_all"        exit 2fi

执行方式：

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