实用shell脚本-mysql异地备份

mysql异地备份shell脚本

cd /data/scripts
vim mysqlLogicBackup.sh
#!/bin/bash
###################################################################
# Date:        2022-05-10 09:50:00
# Auther:      Created by min.li
# Blog:        https://www.cnblogs.com/liminops/
# Description: This scripts function is mysql remote logic backup
# Version:     1.0
###################################################################
#设置xz压缩使用cpu的核数为2核
export XZ_DEFAULTS="-T 2"
#预设的环境参数
DateTime="`date +%Y%m%d`"
MYSQL_DUMP_CMD='/usr/local/mysql/bin/mysqldump'
linuxJobNotify_CMD='/usr/local/bin/linuxJobNotify'
#这里的企业微信机器人消息通知工具，需要小伙伴自行实现
RemoteHostUser='user'
RemoteHostIP='123.123.123.123'
RemoteHostPort='8888'
backPath='/data/backup/mysql'
at_user='bob'
webhook='43111111-19b9-4e11-115a-0822222222e5'

LogicBackup () {
    #全库mysql备份
    start_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Start backup ${Instance} ..."|tee -a log
    ${MYSQL_DUMP_CMD} -h${IP} -u${User} -p${Password} -P${Port} --single-transaction --master-data=2 --routines --all-databases > ${Instance}-${DateTime}-alldb.sql|tee -a log
    #如果备份失败直接退出
    if [ $? -ne 0 ];then
        echo -e " `date +"%Y-%m-%d %H:%M:%S"` ${Instance} backup failed !"|tee -a log
        ${linuxJobNotify_CMD} -j "db back" -s "mysqldump备份失败" -S "${start_time}" -E "`date "+%Y-%m-%d %H:%M:%S"`" -t ${Instance} -H ${HOSTNAME} -u ${at_user} -k ${webhook}
        exit
    fi
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Backup ${Instance} Success."|tee -a log
}

CompressCopy () {
    #打包并压缩
    current_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Start Compress ${Istance} ..."|tee -a log
    tar -Jcf ${Instance}-${DateTime}-alldb.sql.xz ${Instance}-${DateTime}-alldb.sql
    if [ $? -ne 0 ];then
        echo -e "`date +"%Y-%m-%d %H:%M:%S"` Compression ${Instance} fail."|tee -a log
        ${linuxJobNotify_CMD} -j "db back" -s "压缩失败" -S "${current_time}" -E "`date "+%Y-%m-%d %H:%M:%S"`" -t ${Instance} -H ${HOSTNAME} -u ${at_user} -k ${webhook}
        exit
    fi
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Compress ${Istance} Success."|tee -a log
    #清理备份sql文件
    rm -f ${Instance}-${DateTime}-alldb.sql
    if [ $? -ne 0 ];then
        exit
    fi
    #传输到本地机房
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Start Remote Copy ${Istance} ..."|tee -a log
    scp -P ${RemoteHostPort} ${Instance}-${DateTime}-alldb.sql.xz ${RemoteHostUser}@${RemoteHostIP}:${backPath}
    if [ $? -ne 0 ];then
        echo -e "`date +"%Y-%m-%d %H:%M:%S"` Remote Copy ${Instance} fail."|tee -a log
        ${linuxJobNotify_CMD} -j "db back" -s "SCP传输失败" -S "${current_time}" -E "`date "+%Y-%m-%d %H:%M:%S"`" -t ${Instance} -H ${HOSTNAME} -u ${at_user} -k ${webhook}
        exit
    fi
    echo -e "`date +"%Y-%m-%d %H:%M:%S"` Remote Copy ${Istance} Success."|tee -a log
    #清理压缩文件
    rm -f ${Instance}-${DateTime}-alldb.sql.xz
    if [ $? -ne 0 ];then
        exit
    fi
}

#db01
Instance='db01'
User='backup'
Password='asdfasdfasdf4321'
Port=3306
IP='192.168.1.101'
LogicBackup
CompressCopy

#db02
Instance='db02'
User='backup'
Password='asdfasdfasdf4321'
Port=3306
IP='192.168.1.102'
LogicBackup
CompressCopy

配置cron定时任务

cd /data/scripts
chmod +x mysqlLogicBackup.sh
crontab -e
0 3 * * * cd /data/scripts && bash mysqlLogicBackup.sh