MySQL自动备份脚本

MySQL 自动备份脚本详解

MySQL 备份是保障数据安全的核心手段，mysqldump 工具结合 shell 脚本可实现自动化备份，避免人工操作的疏漏。本文详细解析备份原理、脚本及还原方法，帮助构建可靠的备份策略。

`mysqldump` 核心参数解析

mysqldump 是 MySQL 官方备份工具，支持全库、单库、单表备份，核心参数如下：

参数	作用说明
`-u <user>`	指定数据库用户名。
`-p <password>`	指定用户密码（不建议明文写在命令中，可省略让系统交互输入）。
`-h <host>`	数据库主机地址（默认 localhost）。
`--default-character-set=utf8`	指定字符集（避免中文乱码）。
`--databases <db1 db2>`	备份多个数据库（需列出数据库名）。
`--all-databases`	备份所有数据库（包括系统库如 `mysql`）。
`--ignore-table=<db.table>`	排除指定表（如 `--ignore-table=mysql.events`）。
`--lock-all-tables`	备份前锁定所有表（读锁），确保数据一致性（会阻塞写操作，适合停机备份）。
`--single-transaction`	对 InnoDB 表开启事务备份（不锁表，适合在线备份，保证一致性读）。
`--opt`	启用优化选项（默认开启），包括 `--add-drop-table`、`--add-locks` 等。

关键参数对比（锁表策略）：

场景	推荐参数	优点	缺点
在线业务（不停机）	`--single-transaction`	不阻塞写操作，适合生产环境	仅支持 InnoDB 表，MyISAM 仍会锁表
停机维护	`--lock-all-tables`	支持所有存储引擎，数据绝对一致	锁表期间阻塞所有写操作，影响业务

自动备份脚本

解决密码明文、日志记录、过期清理等问题：

#!/bin/bash
# MySQL 自动备份脚本（优化版）
# 配置参数
DB_USER="root"                   # 数据库用户名
DB_PASS=""                       # 密码（留空让系统交互输入，或用配置文件）
DB_HOST="localhost"              # 数据库地址
BACK_DIR="/opt/mysql_backup"     # 备份文件存放目录
RETENTION_DAYS=7                 # 备份保留天数（自动删除超期文件）
BIN_DIR="/usr/local/mysql/bin"   # mysqldump 路径（默认可省略，需确保在环境变量中）
DATE=$(date +%Y%m%d_%H%M%S)      # 备份文件名时间戳（精确到秒）

# 创建备份目录（若不存在）
mkdir -p ${BACK_DIR} || { echo "创建备份目录失败"; exit 1; }

# 日志文件路径
LOG_FILE="${BACK_DIR}/backup_${DATE}.log"

# 记录开始时间
echo "===== 备份开始于 ${DATE} =====" >> ${LOG_FILE}

# 备份所有数据库（使用 --single-transaction 适合在线环境）
echo "开始备份所有数据库..." >> ${LOG_FILE}
${BIN_DIR}/mysqldump \
  -u${DB_USER} -p${DB_PASS} \
  -h${DB_HOST} \
  --default-character-set=utf8 \
  --single-transaction \
  --opt \
  --events \
  --ignore-table=mysql.events \
  --all-databases | gzip > ${BACK_DIR}/all_dbs_${DATE}.sql.gz

# 检查全库备份是否成功
if [ $? -eq 0 ]; then
  echo "全库备份成功：${BACK_DIR}/all_dbs_${DATE}.sql.gz" >> ${LOG_FILE}
else
  echo "全库备份失败！" >> ${LOG_FILE}
  exit 1
fi

# 单独备份核心业务库（可选，根据需要添加）
# BUSINESS_DBS=("user_db" "order_db")  # 业务数据库列表
# for DB_NAME in "${BUSINESS_DBS[@]}"; do
#   ${BIN_DIR}/mysqldump \
#     -u${DB_USER} -p${DB_PASS} \
#     -h${DB_HOST} \
#     --default-character-set=utf8 \
#     --single-transaction \
#     ${DB_NAME} | gzip > ${BACK_DIR}/${DB_NAME}_${DATE}.sql.gz
#   [ $? -eq 0 ] && echo "备份 ${DB_NAME} 成功" >> ${LOG_FILE} || echo "备份 ${DB_NAME} 失败" >> ${LOG_FILE}
# done

# 删除过期备份（保留最近 RETENTION_DAYS 天）
echo "开始清理 ${RETENTION_DAYS} 天前的备份..." >> ${LOG_FILE}
find ${BACK_DIR} -name "*.sql.gz" -type f -mtime +${RETENTION_DAYS} -delete >> ${LOG_FILE} 2>&1

# 记录结束时间
echo "===== 备份结束于 $(date +%Y%m%d_%H%M%S) =====" >> ${LOG_FILE}
echo "-----------------------------------------" >> ${LOG_FILE}

说明：

密码安全：不直接写明文密码，可通过 ~/.my.cnf 配置文件存储（推荐）：
1
2
3
4
# ~/.my.cnf 内容
[mysqldump]
user=root
password=你的密码
脚本中可省略 -p 参数，自动读取配置。
过期清理：添加 find 命令删除超期备份（-mtime +7 表示删除 7 天前的文件），避免磁盘占满。
日志细化：每个备份生成独立日志，记录备份时间、文件名及结果，便于排查问题。
可选单独备份：保留业务库单独备份的模板（注释部分），适合需要区分系统库和业务库的场景。

脚本使用与自动化

脚本权限配置

# 赋予执行权限
chmod +x mysql_backup.sh

# 测试运行（首次执行建议手动运行，确认无错误）
./mysql_backup.sh

定时任务（`crontab`）

通过 crontab 实现每日自动备份（如每天凌晨 2 点执行）：

# 编辑定时任务
crontab -e

# 添加如下内容（路径替换为实际脚本路径）
0 2 * * * /opt/mysql_backup/mysql_backup.sh

0 2 * * * 表示 “每天凌晨 2 点” 执行（分钟小时日月周）。
可通过 tail -f /var/log/cron 查看定时任务执行日志。

数据还原方法

备份文件为 *.sql.gz（压缩）或 *.sql（未压缩），还原步骤如下：

压缩文件还原（`.sql.gz`）

1 2	# 解压并还原到数据库（需先创建目标数据库，若备份包含建库语句可省略） gunzip < all_dbs_20231001_020000.sql.gz \| mysql -u root -p

未压缩文件还原（`.sql`）

# 直接还原
mysql -u root -p 目标数据库名 < backup.sql

# 或登录 MySQL 后还原
mysql -u root -p
Enter password: 
mysql> use 目标数据库名;
mysql> source /path/to/backup.sql;  # 输入备份文件绝对路径

还原注意事项：

还原前建议停止业务写入（避免数据冲突）。
若备份包含 DROP TABLE 语句，还原会删除现有表，需提前确认。
大文件还原可添加 --force 参数忽略错误（mysql --force -u root -p < backup.sql）。

备份策略建议

备份频率：
- 核心业务库：每日全量备份 + 每小时增量备份（结合 binlog）。
- 非核心库：每日全量备份。
存储安全：
- 备份文件异地存储（如 FTP 服务器、云存储），避免本地磁盘损坏丢失。
- 定期校验备份文件完整性（如用 gunzip -t 检查压缩文件）。
应急演练：
- 每月至少一次还原测试，确保备份可用（避免 “备份成功但无法还原” 的情况）