定时备份mysql数据库到其他服务器

🧰 脚本功能简介

• 自动备份：定时执行，无需手动操作。
• Docker兼容：专为Docker容器中的MySQL设计。
• 远程传输：备份文件自动传输到指定远程服务器。
• 灵活配置：数据库、本地路径、远程服务器等参数可自定义。

📝 脚本详解

1. 环境配置

CONTAINER_NAME="yudao-mysql" 

# 数据库配置  
DB_HOST="localhost"  
DB_USER=""  
DB_PASSWORD=""  
DB_NAME=""  

# 本地备份文件存储路径  
LOCAL_BACKUP_PATH="/app/backup"  

# 远程服务器配置  
REMOTE_HOST=""  
REMOTE_USER="root"  
REMOTE_PATH="/app/backup"  

2. 备份文件命名

TIMESTAMP=$(date +"%Y%m%d%H%M%S")  
BACKUP_FILE_NAME="${DB_NAME}_${TIMESTAMP}.sql"  
LOCAL_BACKUP_FILE_PATH="${LOCAL_BACKUP_PATH}/${BACKUP_FILE_NAME}"  

3. 创建本地备份目录

mkdir -p "${LOCAL_BACKUP_PATH}"  

4. 执行备份命令

DUMP_CMD="mysqldump -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME}"  
docker exec "${CONTAINER_NAME}" ${DUMP_CMD} > "${LOCAL_BACKUP_FILE_PATH}"  

5. 备份结果检查与远程传输

if [ $? -eq 0 ]; then  
    echo "Backup successfully created: ${LOCAL_BACKUP_FILE_PATH}"  
    scp "${LOCAL_BACKUP_FILE_PATH}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}"  
    
    if [ $? -eq 0 ]; then  
        echo "Backup successfully transferred to ${REMOTE_HOST}:${REMOTE_PATH}"  
        rm "${LOCAL_BACKUP_FILE_PATH}"  
        echo "Local backup file deleted: ${LOCAL_BACKUP_FILE_PATH}"  
    else  
        echo "Failed to transfer backup to remote server."  
    fi  
else  
    echo "An error occurred during the backup process."  
fi  

📆 定时任务设置

使用crontab

# 编辑crontab配置
crontab -e

# 添加定时任务，例如每天凌晨1点执行
0 1 * * * /path/to/your/script.sh

使用Jenkins

1. 创建新任务：在Jenkins中创建一个新的定时构建任务。
2. 配置定时触发：设置任务的触发时间。
3. 执行脚本：在构建步骤中添加执行脚本的命令。

🌟 结语

数据安全是每个项目的基石，有了这个自动备份与远程传输脚本，你再也不用担心数据丢失的问题。赶快试试吧，让数据安全更有保障！

---

源代码：

#!/bin/bash  

CONTAINER_NAME="yudao-mysql" 

# 数据库配置  
DB_HOST="localhost"  
DB_USER=""  
DB_PASSWORD=""  
DB_NAME=""  

# 本地备份文件存储路径  
LOCAL_BACKUP_PATH="/app/backup"  

# 远程服务器配置  
REMOTE_HOST=""  
REMOTE_USER="root"  
REMOTE_PATH="/app/backup"  

# 生成备份文件名  
TIMESTAMP=$(date +"%Y%m%d%H%M%S")  
BACKUP_FILE_NAME="${DB_NAME}_${TIMESTAMP}.sql"  
LOCAL_BACKUP_FILE_PATH="${LOCAL_BACKUP_PATH}/${BACKUP_FILE_NAME}"  

# 创建本地备份目录（如果不存在）  
mkdir -p "${LOCAL_BACKUP_PATH}"  

# 构建mysqldump命令  
DUMP_CMD="mysqldump -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME}"  

# 在Docker容器中执行mysqldump命令并导出备份文件  
docker exec "${CONTAINER_NAME}" ${DUMP_CMD} > "${LOCAL_BACKUP_FILE_PATH}"  

# 检查命令执行结果  
if [ $? -eq 0 ]; then  
    echo "Backup successfully created: ${LOCAL_BACKUP_FILE_PATH}"  
    
    # 通过SSH将备份文件传输到远程服务器  
    scp "${LOCAL_BACKUP_FILE_PATH}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}"  
    
    if [ $? -eq 0 ]; then  
        echo "Backup successfully transferred to ${REMOTE_HOST}:${REMOTE_PATH}"  
        
        # 可选：删除本地备份文件  
        rm "${LOCAL_BACKUP_FILE_PATH}"  
        echo "Local backup file deleted: ${LOCAL_BACKUP_FILE_PATH}"  
    else  
        echo "Failed to transfer backup to remote server."  
    fi  
else  
    echo "An error occurred during the backup process."  
fi