📁MySQL数据库自动备份配置指南

📋 概述#

本文提供了在Ubuntu系统上配置MySQL数据库自动备份的完整步骤。配置完成后，系统将自动在每天的6点、13点、21点进行数据库备份，并保留最近7天的备份文件。

🚀 配置步骤#

第一步：创建目录结构#

# 创建备份目录
sudo mkdir -p /leonsong/backup/mysql

# 创建脚本目录
sudo mkdir -p /leonsong/script

# 设置目录权限
sudo chown -R $USER:$USER /leonsong/backup
sudo chown -R $USER:$USER /leonsong/script

第二步：创建日志文件#

# 创建日志文件并设置权限
sudo touch /var/log/mysql_backup.log
sudo chown ubuntu:ubuntu /var/log/mysql_backup.log
sudo chmod 644 /var/log/mysql_backup.log

第三步：创建备份脚本#

# 创建备份脚本
nano /leonsong/script/mysql_backup.sh

📌 请根据您的MySQL配置选择相应的脚本版本：

🔐 方案一：有密码版本#

适用于：MySQL root用户设置了密码的情况

创建密码配置文件（推荐）#

# 创建MySQL配置文件（更安全的方式）
nano ~/.my.cnf

添加以下内容：

[mysqldump]
user=root
password=your_mysql_password
host=localhost

[mysql]
user=root
password=your_mysql_password
host=localhost

设置配置文件权限：

chmod 600 ~/.my.cnf

有密码版本备份脚本：#

#!/bin/bash

# ===========================================
# MySQL多数据库备份脚本 - 有密码版本
# 脚本位置：/leonsong/script/mysql_backup.sh
# ===========================================

# 📝 数据库配置（方式一：使用配置文件，推荐）
# 如果使用了 ~/.my.cnf 配置文件，MySQL命令会自动读取
DB_USER="root"
DB_HOST="localhost"
# 密码通过 ~/.my.cnf 文件配置，更安全

# 📝 数据库配置（方式二：直接在脚本中配置密码，不推荐）
# DB_USER="root"
# DB_PASSWORD="your_mysql_password"  # ← 替换为您的实际密码
# DB_HOST="localhost"

# 🎯 要备份的数据库列表
DB_LIST=(
    "crm"                       # CRM数据库
    "fim_aps_system"           # FIM APS系统数据库
    # "其他数据库"               # 如需添加更多数据库，取消注释
)

# 📁 备份配置
BACKUP_DIR="/leonsong/backup/mysql"
DATE=$(date +"%Y%m%d_%H%M")
LOG_FILE="/var/log/mysql_backup.log"

# 🏷️ 确定备份时间标签
HOUR=$(date +"%H")
TIME_LABEL=""
case $HOUR in
    "06") TIME_LABEL="早晨" ;;
    "13") TIME_LABEL="中午" ;;
    "21") TIME_LABEL="晚上" ;;
    *) TIME_LABEL="其他时间" ;;
esac

# 📝 记录开始日志
echo "========================================" >> $LOG_FILE
echo "$(date): 开始执行${TIME_LABEL}多数据库备份..." >> $LOG_FILE
echo "$(date): 备份目录: $BACKUP_DIR" >> $LOG_FILE
echo "$(date): 准备备份 ${#DB_LIST[@]} 个数据库: ${DB_LIST[*]}" >> $LOG_FILE

# 🗂️ 确保备份目录存在
mkdir -p $BACKUP_DIR

# 📊 统计变量
SUCCESS_COUNT=0
FAILED_COUNT=0

# 💾 逐个备份数据库
for DB_NAME in "${DB_LIST[@]}"; do
    echo "$(date): 开始备份数据库: $DB_NAME" >> $LOG_FILE

    BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"

    # 有密码MySQL备份命令
    # 方式一：使用配置文件（推荐）
    if mysqldump --single-transaction --routines --triggers \
        -u$DB_USER -h$DB_HOST $DB_NAME 2>>$LOG_FILE | gzip > "${BACKUP_FILE}.gz"; then

    # 方式二：直接使用密码参数（不推荐，密码会在进程列表中可见）
    # if mysqldump --single-transaction --routines --triggers \
    #     -u$DB_USER -p$DB_PASSWORD -h$DB_HOST $DB_NAME 2>>$LOG_FILE | gzip > "${BACKUP_FILE}.gz"; then

        # ✅ 单个数据库备份成功
        BACKUP_SIZE=$(du -h "${BACKUP_FILE}.gz" | cut -f1)
        echo "$(date): ✅ $DB_NAME 备份成功 (大小: $BACKUP_SIZE)" >> $LOG_FILE
        SUCCESS_COUNT=$((SUCCESS_COUNT + 1))

    else
        # ❌ 单个数据库备份失败
        echo "$(date): ❌ $DB_NAME 备份失败！可能数据库不存在" >> $LOG_FILE
        FAILED_COUNT=$((FAILED_COUNT + 1))
        # 删除可能的空文件
        rm -f "${BACKUP_FILE}.gz"
    fi
done

# 📈 备份总结
echo "$(date): ========== 备份总结 ==========" >> $LOG_FILE
echo "$(date): 成功备份: $SUCCESS_COUNT 个数据库" >> $LOG_FILE
echo "$(date): 备份失败: $FAILED_COUNT 个数据库" >> $LOG_FILE

if [ $SUCCESS_COUNT -gt 0 ]; then
    # 🧹 清理7天前的备份文件
    for DB_NAME in "${DB_LIST[@]}"; do
        DELETED_COUNT=$(find $BACKUP_DIR -name "${DB_NAME}_backup*.sql.gz" -mtime +7 -delete -print | wc -l)
        if [ $DELETED_COUNT -gt 0 ]; then
            echo "$(date): 🗑️ 清理了 $DB_NAME 的 $DELETED_COUNT 个过期备份" >> $LOG_FILE
        fi
    done
fi

# 📊 显示所有备份文件统计
TOTAL_BACKUPS=$(find $BACKUP_DIR -name "*_backup_*.sql.gz" 2>/dev/null | wc -l)
TOTAL_DIR_SIZE=$(du -sh $BACKUP_DIR 2>/dev/null | cut -f1)
echo "$(date): 📁 备份目录总大小: ${TOTAL_DIR_SIZE:-0}" >> $LOG_FILE
echo "$(date): 📈 备份目录总文件数: $TOTAL_BACKUPS" >> $LOG_FILE

echo "$(date): ${TIME_LABEL}多数据库备份任务完成" >> $LOG_FILE
echo "========================================" >> $LOG_FILE

🔓 方案二：无密码版本#

适用于：MySQL root用户无密码，直接回车即可登录的情况

无密码版本备份脚本：#

#!/bin/bash

# ===========================================
# MySQL多数据库备份脚本 - 无密码版本
# 脚本位置：/leonsong/script/mysql_backup.sh
# ===========================================

# 📝 数据库配置
DB_USER="root"
DB_HOST="localhost"
# 无密码配置，不需要密码参数

# 🎯 要备份的数据库列表
DB_LIST=(
    "crm"                       # CRM数据库
    "fim_aps_system"           # FIM APS系统数据库
    # "其他数据库"               # 如需添加更多数据库，取消注释
)

# 📁 备份配置
BACKUP_DIR="/leonsong/backup/mysql"
DATE=$(date +"%Y%m%d_%H%M")
LOG_FILE="/var/log/mysql_backup.log"

# 🏷️ 确定备份时间标签
HOUR=$(date +"%H")
TIME_LABEL=""
case $HOUR in
    "06") TIME_LABEL="早晨" ;;
    "13") TIME_LABEL="中午" ;;
    "21") TIME_LABEL="晚上" ;;
    *) TIME_LABEL="其他时间" ;;
esac

# 📝 记录开始日志
echo "========================================" >> $LOG_FILE
echo "$(date): 开始执行${TIME_LABEL}多数据库备份..." >> $LOG_FILE
echo "$(date): 备份目录: $BACKUP_DIR" >> $LOG_FILE
echo "$(date): 准备备份 ${#DB_LIST[@]} 个数据库: ${DB_LIST[*]}" >> $LOG_FILE

# 🗂️ 确保备份目录存在
mkdir -p $BACKUP_DIR

# 📊 统计变量
SUCCESS_COUNT=0
FAILED_COUNT=0

# 💾 逐个备份数据库
for DB_NAME in "${DB_LIST[@]}"; do
    echo "$(date): 开始备份数据库: $DB_NAME" >> $LOG_FILE

    BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"

    # 无密码MySQL备份命令（不使用-p参数）
    if mysqldump --single-transaction --routines --triggers \
        -u$DB_USER -h$DB_HOST $DB_NAME 2>>$LOG_FILE | gzip > "${BACKUP_FILE}.gz"; then

        # ✅ 单个数据库备份成功
        BACKUP_SIZE=$(du -h "${BACKUP_FILE}.gz" | cut -f1)
        echo "$(date): ✅ $DB_NAME 备份成功 (大小: $BACKUP_SIZE)" >> $LOG_FILE
        SUCCESS_COUNT=$((SUCCESS_COUNT + 1))

    else
        # ❌ 单个数据库备份失败
        echo "$(date): ❌ $DB_NAME 备份失败！可能数据库不存在" >> $LOG_FILE
        FAILED_COUNT=$((FAILED_COUNT + 1))
        # 删除可能的空文件
        rm -f "${BACKUP_FILE}.gz"
    fi
done

# 📈 备份总结
echo "$(date): ========== 备份总结 ==========" >> $LOG_FILE
echo "$(date): 成功备份: $SUCCESS_COUNT 个数据库" >> $LOG_FILE
echo "$(date): 备份失败: $FAILED_COUNT 个数据库" >> $LOG_FILE

if [ $SUCCESS_COUNT -gt 0 ]; then
    # 🧹 清理7天前的备份文件
    for DB_NAME in "${DB_LIST[@]}"; do
        DELETED_COUNT=$(find $BACKUP_DIR -name "${DB_NAME}_backup*.sql.gz" -mtime +7 -delete -print | wc -l)
        if [ $DELETED_COUNT -gt 0 ]; then
            echo "$(date): 🗑️ 清理了 $DB_NAME 的 $DELETED_COUNT 个过期备份" >> $LOG_FILE
        fi
    done
fi

# 📊 显示所有备份文件统计
TOTAL_BACKUPS=$(find $BACKUP_DIR -name "*_backup_*.sql.gz" 2>/dev/null | wc -l)
TOTAL_DIR_SIZE=$(du -sh $BACKUP_DIR 2>/dev/null | cut -f1)
echo "$(date): 📁 备份目录总大小: ${TOTAL_DIR_SIZE:-0}" >> $LOG_FILE
echo "$(date): 📈 备份目录总文件数: $TOTAL_BACKUPS" >> $LOG_FILE

echo "$(date): ${TIME_LABEL}多数据库备份任务完成" >> $LOG_FILE
echo "========================================" >> $LOG_FILE

📋 选择适合的版本#

生产环境强烈建议使用有密码版本
定期更换MySQL密码

第四步：设置脚本权限#

# 设置执行权限
chmod +x /leonsong/script/mysql_backup.sh

# 确保文件所有者正确
sudo chown ubuntu:ubuntu /leonsong/script/mysql_backup.sh

第五步：测试脚本运行#

🔐 有密码版本测试：

# 首先测试MySQL连接
mysql -u root -p -e "SHOW DATABASES;"

# 或者如果使用了配置文件
mysql -u root -e "SHOW DATABASES;"

# 手动测试脚本
/leonsong/script/mysql_backup.sh

# 查看执行结果
cat /var/log/mysql_backup.log

# 检查备份文件
ls -la /leonsong/backup/mysql/

🔓 无密码版本测试：

# 首先测试MySQL连接（直接回车，无需密码）
mysql -u root -e "SHOW DATABASES;"

# 手动测试脚本
/leonsong/script/mysql_backup.sh

# 查看执行结果
cat /var/log/mysql_backup.log

# 检查备份文件
ls -la /leonsong/backup/mysql/

第六步：配置定时任务#

# 编辑定时任务
crontab -e

# 首次使用会提示选择编辑器，选择 1 (nano)

在打开的编辑器中添加以下内容：

# MySQL数据库自动备份任务
# 每天早晨6点备份
0 6 * * * /leonsong/script/mysql_backup.sh >/dev/null 2>&1

# 每天中午1点备份
0 13 * * * /leonsong/script/mysql_backup.sh >/dev/null 2>&1

# 每天晚上9点备份
0 21 * * * /leonsong/script/mysql_backup.sh >/dev/null 2>&1

保存并退出：

按 Ctrl + X
按 Y 确认保存
按 Enter 确认文件名

第七步：验证定时任务#

# 查看已设置的定时任务
crontab -l

# 检查cron服务状态
sudo systemctl status cron

# 如果服务未启动，启动它
sudo systemctl start cron
sudo systemctl enable cron

🛠️ 管理工具#

创建备份管理脚本#

# 创建管理工具
nano /leonsong/script/backup_manager.sh

管理脚本内容：

#!/bin/bash

# 备份管理工具
BACKUP_DIR="/leonsong/backup/mysql"
SCRIPT_PATH="/leonsong/script/mysql_backup.sh"

show_menu() {
    echo "============================================"
    echo "        MySQL备份管理工具"
    echo "============================================"
    echo "1. 查看备份文件"
    echo "2. 查看备份日志"
    echo "3. 手动执行备份"
    echo "4. 检查磁盘使用"
    echo "5. 测试MySQL连接"
    echo "6. 退出"
    echo "============================================"
}

while true; do
    show_menu
    echo "请选择操作 (1-6):"
    read choice

    case $choice in
        1) find $BACKUP_DIR -name "*.sql.gz" -mtime -7 -exec ls -lht {} \; ;;
        2) tail -20 /var/log/mysql_backup.log ;;
        3) $SCRIPT_PATH ;;
        4) df -h /leonsong/backup; du -sh $BACKUP_DIR ;;
        5)
            echo "请选择您的MySQL配置:"
            echo "1) 有密码版本"
            echo "2) 无密码版本"
            read mysql_type
            case $mysql_type in
                1) mysql -u root -p -e "SELECT 'MySQL连接正常' as status;" ;;
                2) mysql -u root -e "SELECT 'MySQL连接正常' as status;" ;;
                *) echo "无效选择" ;;
            esac
            ;;
        6) exit 0 ;;
        *) echo "❌ 无效选择" ;;
    esac

    echo ""
    echo "按任意键继续..."
    read -n 1
    clear
done

# 设置管理脚本权限
chmod +x /leonsong/script/backup_manager.sh

创建权限修复脚本#

# 创建权限修复脚本
nano /leonsong/script/fix_permissions.sh

权限修复脚本内容：

#!/bin/bash

echo "🔧 开始修复MySQL备份脚本权限..."

# 修复日志文件权限
sudo touch /var/log/mysql_backup.log
sudo chown ubuntu:ubuntu /var/log/mysql_backup.log
sudo chmod 644 /var/log/mysql_backup.log

# 修复备份目录权限
sudo mkdir -p /leonsong/backup/mysql
sudo chown -R ubuntu:ubuntu /leonsong/backup
sudo chmod -R 755 /leonsong/backup

# 修复脚本目录权限
sudo chown -R ubuntu:ubuntu /leonsong/script
sudo chmod -R 755 /leonsong/script
sudo chmod +x /leonsong/script/*.sh

echo "✅ 权限修复完成！"

# 设置权限
chmod +x /leonsong/script/fix_permissions.sh

📊 日常维护#

常用检查命令#

# 查看最新备份文件
ls -la /leonsong/backup/mysql/

# 查看备份日志
tail -f /var/log/mysql_backup.log

# 查看定时任务
crontab -l

# 检查磁盘使用
df -h /leonsong/backup

# 使用管理工具
/leonsong/script/backup_manager.sh

备份文件命名规则#

数据库名_backup_年月日_时分.sql.gz

示例：
- crm_backup_20240115_0600.sql.gz
- fim_aps_system_backup_20240115_1300.sql.gz

数据恢复方法#

# 查看可用备份
ls -la /leonsong/backup/mysql/

🔐 有密码版本恢复：

# 方式一：使用配置文件（如果设置了 ~/.my.cnf）
gunzip -c /leonsong/backup/mysql/crm_backup_20240115_0600.sql.gz | mysql -u root crm

# 方式二：手动输入密码
gunzip -c /leonsong/backup/mysql/crm_backup_20240115_0600.sql.gz | mysql -u root -p crm

🔓 无密码版本恢复：

# 直接恢复（无需密码）
gunzip -c /leonsong/backup/mysql/crm_backup_20240115_0600.sql.gz | mysql -u root crm

🚨 故障排除#

常见问题#

1. 权限拒绝错误

# 运行权限修复脚本
/leonsong/script/fix_permissions.sh

2. MySQL连接失败

🔐 有密码版本排查：

# 检查MySQL服务
sudo systemctl status mysql
sudo systemctl restart mysql

# 测试连接（手动输入密码）
mysql -u root -p -e "SHOW DATABASES;"

# 检查配置文件（如果使用）
ls -la ~/.my.cnf
cat ~/.my.cnf

# 验证密码是否正确
mysql -u root -p

🔓 无密码版本排查：

# 检查MySQL服务
sudo systemctl status mysql
sudo systemctl restart mysql

# 测试连接（无密码）
mysql -u root -e "SHOW DATABASES;"

# 检查是否意外设置了密码
mysql -u root

3. 定时任务不执行

# 检查cron服务
sudo systemctl status cron
sudo systemctl restart cron

# 查看cron日志
sudo tail -f /var/log/syslog | grep CRON

4. 磁盘空间不足

# 检查磁盘使用
df -h

# 手动清理旧备份
find /leonsong/backup/mysql -name "*.sql.gz" -mtime +3 -delete

紧急备份#

# 立即执行备份脚本
sudo ./mysql_backup.sh

🔐 有密码版本手动备份：

# 使用配置文件方式
mysqldump -u root crm | gzip > /leonsong/backup/mysql/emergency_crm_$(date +%Y%m%d_%H%M).sql.gz

# 手动输入密码方式
mysqldump -u root -p crm | gzip > /leonsong/backup/mysql/emergency_crm_$(date +%Y%m%d_%H%M).sql.gz

🔓 无密码版本手动备份：

# 直接备份（无需密码）
mysqldump -u root crm | gzip > /leonsong/backup/mysql/emergency_crm_$(date +%Y%m%d_%H%M).sql.gz

📝 注意事项#

定期检查: 建议每周检查一次备份状态和日志
磁盘监控: 定期监控备份目录的磁盘使用情况
恢复测试: 建议定期在测试环境测试数据恢复过程
权限维护: 如果更改用户或权限，需要重新运行权限修复脚本
数据库变更: 如果添加新数据库，需要更新脚本中的DB_LIST数组

📈 监控建议#

备份成功率: 通过日志监控备份成功率
备份文件大小: 监控备份文件大小变化趋势
执行时间: 关注备份执行时间，避免影响业务
存储空间: 设置磁盘使用率警报

🎯 快速选择指南#

如何确定您的MySQL配置类型？#

测试方法：

# 尝试无密码登录
mysql -u root

# 如果上面命令直接进入MySQL，说明是无密码配置
# 如果提示需要密码，说明是有密码配置

版本选择建议#

环境类型	推荐版本	安全级别	配置难度
生产环境	🔐 有密码版本	⭐⭐⭐⭐⭐	中等
测试环境	🔓 无密码版本	⭐⭐⭐	简单
开发环境	🔓 无密码版本	⭐⭐	简单
演示环境	🔐 有密码版本	⭐⭐⭐⭐	中等

配置完成后，您的MySQL数据库将自动在每天6点、13点、21点进行备份，并保留最近7天的备份文件，确保数据安全。