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

 

mysql_mysqldump_backup.sh脚本代码：

# 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

mysql_mysqlbinlog_backup.sh脚本代码：

扫码关注个人公众号【Java软件变成之家】即可获取（mysql_mysqlbinlog_backup.sh）二进制日志备份脚本。