Mysql数据库脚本备份【本地压缩+远程备份副本】

自己选个路径。如：mkdir -p /opt/mysql/

cd /opt/mysql/

vim mysql_backup.sh

#!/bin/bash
# Database info
DB_USER="root"                          # 数据库备份用户
DB_PASS="root"                 # 备份用户密码
DB_HOST="10.10.6.27"                    # 数据库 IP
BACKUP_DIR="/opt/mysql"                 # 本地备份路径
DATE=$(date -d '-1 day' +%Y-%m-%d)      # 获取当前时间
JUST_7_DAY_AGO=$(date -d '-7 day' +%Y-%m-%d)  # 取 7 天前的时间，格式为：2024-5-1，用于删除备份文件取文件时间，该参数可自行调整
REMOTE_IP=${node-02}    # 可以改成具体IP，或者/etc/hosts 配置一下


#### 根据实际情况进行数据库罗列，此处可修改 ###
DB_LIST=("gz_metadata" "gz_manager" "gz_exy" "gz_jkj" "gz_mzy" "gz_users")               # 需要备份的数据库

# 创建备份目录，带日期 
if [ ! -d "$BACKUP_DIR/$DATE/" ]; then
    mkdir -p $BACKUP_DIR/$DATE
fi
if [ ! -d "$BACKUP_DIR/logs/" ]; then
    mkdir -p $BACKUP_DIR/logs/
fi

echo "开始本地备份中... $(date) " >> $BACKUP_DIR/logs/$DATE.log

if [ ! -d "$BACKUP_DIR/$DATE" ]; then
    echo "error $date 存放备份文件的路径不存在，终止执行 。。。" >> $BACKUP_DIR/logs/$DATE.log
    exit 1
fi

# 逐个数据库进行备份 
for _db in "${DB_LIST[@]}"; do
    /usr/local/mysql-8.0.26/bin/mysqldump -u$DB_USER -p$DB_PASS --databases $_db > $BACKUP_DIR/$DATE/${_db}_${DATE}.sql
    if [ ! -e $BACKUP_DIR/$DATE/${_db}_${DATE}.sql ]; then
        echo "error $(date) 数据${_db}数据库导出失败，$BACKUP_DIR/$DATE/${_db}_${DATE}.sql文件不存在。程序退出" >> $BACKUP_DIR/logs/$DATE.log
        exit 1
    fi
    echo "$(date) ${_db} db bakcup success！ $(date) " >> $BACKUP_DIR/logs/$DATE.log
done

echo "本地备份完成！ $(date)" >> $BACKUP_DIR/logs/$DATE.log

echo "对备份文件夹开始进行压缩... $(date) " >> $BACKUP_DIR/logs/$DATE.log
cd $BACKUP_DIR && tar -zcvf mysql_backup_$DATE.sql.tar.gz ./$DATE >> $BACKUP_DIR/logs/$DATE.log && rm -fr $BACKUP_DIR/$DATE/

if [ ! -e mysql_backup_$DATE.sql.tar.gz ]; then
    echo "文件压缩失败，退出程序 ... $(date) " >> $BACKUP_DIR/logs/$DATE.log
    exit 1
fi

echo "备份文件夹压缩完成！ $(date)" >> $BACKUP_DIR/logs/$DATE.log

echo "开始删除 7 天前的数据库备份文件... $(date) " >> $BACKUP_DIR/logs/$DATE.log
rm -rf $BACKUP_DIR/mysql_backup_${JUST_7_DAY_AGO}.sql.tar.gz
echo "7 天前的数据库备份文件删除完毕！ $(date) " >> $BACKUP_DIR/logs/$DATE.log

# 远程备份服务器需要先开通ssh 免密
echo "开始远程备份中... $(date) " >> $BACKUP_DIR/logs/$DATE.log
scp -r $BACKUP_DIR/mysql_backup_${DATE}.sql.tar.gz node-02:$(pwd)
echo "远程备份完毕！ $(date) " >>/$BACKUP_DIR/logs/$DATE.log

创建定时任务： crontab -e

0 2 * * * /opt/mysql/mysql_backup.sh