MySQL数据库（八）—— MySQL全量+增量备份方案：从脚本开发到连锁餐饮场景落地

原创已于 2025-10-06 16:39:36 修改 · 800 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #运维 #云计算

于 2025-10-05 11:58:59 首次发布

Shell编程同时被 2 个专栏收录

11 篇文章

订阅专栏

mysql数据库

8 篇文章

订阅专栏

文章目录

前言
一、备份方案核心设计
- 1.1 方案整体架构
- 1.2 关键技术选型
二、核心脚本解析
三、场景化部署与运维
四、结果展示
五、数据恢复流程
- 5.1 恢复全量备份（回到基准状态）
- 5.2 恢复增量备份（补全最新数据）
总结

前言

在企业级应用中，数据库备份是保障数据安全的最后一道防线，尤其对于数据量大、业务连续性要求高的场景，一套高效的备份策略至关重要。本文以连锁餐饮品牌为实际案例（总数据量150GB，每日新增1GB），详细讲解基于mysqldump和binlog的MySQL全量+增量备份方案，包含3个核心脚本的开发逻辑、自动化部署流程、故障排查及数据恢复操作，帮助运维人员快速落地可靠的备份体系。

本文适用场景：MySQL 5.7.x版本、中等数据量（100-500GB）、需要平衡备份效率与存储成本的业务，如零售、餐饮、中小型电商等。

一、备份方案核心设计

1.1 方案整体架构

基于连锁餐饮场景的业务特点（夜间为业务低峰期），设计“全量备份+增量备份”混合策略，兼顾数据完整性和备份效率：

备份类型	执行周期	执行时间	耗时预估	存储占用（压缩后）	核心依赖
全量备份	每周1次	周日凌晨2点	40-60分钟	80GB/次（150GB原始数据）	mysqldump
增量备份	每日1次	周一至周六凌晨3点	2-5分钟	1.2GB/次（每日新增1GB）	MySQL binlog
备份监控	每日1次	凌晨4点（自动触发）	1-2分钟	忽略（日志文件）	自定义监控脚本

1.2 关键技术选型

全量备份工具：选用mysqldump，支持InnoDB表无锁备份（--single-transaction参数），避免影响业务读写；
增量备份依赖：启用MySQL binlog（行格式binlog_format = row），记录所有数据变更，确保增量数据不丢失；
完整性校验：所有备份文件生成MD5校验值，避免备份文件损坏导致恢复失败；
自动化调度：使用Linuxcrontab实现定时任务，无需人工干预；
告警机制：通过mail命令发送备份失败/校验失败告警，及时发现问题。

二、核心脚本解析

2.1 自动化部署脚本（setup_cron.sh）

2.1.1 脚本功能

一键完成备份环境初始化，包括启用MySQL binlog、创建备份用户、配置定时任务、生成备份监控脚本，避免手动操作遗漏或错误。

2.1.2 执行流程

参数初始化：统一配置备份用户、密码、MySQL配置文件路径（/etc/my.cnf）、告警邮箱等，支持根据实际环境修改；
启用binlog：检查my.cnf中是否已配置log_bin，若未配置则添加binlog相关参数（log_bin、server-id、binlog_format等），并重启MySQL服务使配置生效；
创建备份用户：通过MySQL root账户创建backup_user，并授予备份所需权限（SELECT、RELOAD、REPLICATION CLIENT等），确保备份脚本有足够权限执行操作；
目录与权限配置：创建全量备份目录、增量备份目录、日志目录，设置目录权限为700（仅root可读写），避免备份文件或日志泄露；
定时任务配置：通过crontab添加全量备份（周日2点）和增量备份（周一至周六3点）任务，先删除旧任务避免重复，再添加新任务；
生成监控脚本：创建/etc/cron.daily/check_mysql_backup.sh脚本，每日自动检查备份文件是否存在、MD5校验是否通过，若失败则发送告警邮件到指定邮箱；
邮件功能校验：检查系统是否安装mail客户端（如未安装则提示安装命令），确保告警功能正常；
部署完成提示：输出所有配置路径（备份目录、日志目录等），便于运维人员后续查看或维护。

2.1.3 核心代码片段

# 启用MySQL binlog（关键配置）
if ! grep -q -E "^[[:space:]]*log_bin[[:space:]]*=" "$MYSQL_CONF"; then
    sed -i '/\[mysqld\]/a log_bin = /var/lib/mysql/mysql-bin\nserver-id = 1\nbinlog_format = row\nmax_binlog_size = 1G\nexpire_logs_days = 14' "$MYSQL_CONF"
    systemctl restart mysqld  # 重启MySQL生效
fi

# 创建备份用户并授权
mysql -u root -p"$ROOT_PASS" << EOF
CREATE USER IF NOT EXISTS '$BACKUP_USER'@'$BACKUP_HOST' IDENTIFIED BY '$BACKUP_PASS';
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, EVENT, TRIGGER, SHOW DATABASES ON *.* TO '$BACKUP_USER'@'$BACKUP_HOST';
FLUSH PRIVILEGES;
EOF

# 配置定时任务
(crontab -l 2>/dev/null | grep -v -F -- "$FULL_BACKUP_SCRIPT"; echo "0 2 * * 0 /bin/bash $FULL_BACKUP_SCRIPT") | crontab -
(crontab -l 2>/dev/null | grep -v -F -- "$INCR_BACKUP_SCRIPT"; echo "0 3 * * 1-6 /bin/bash $INCR_BACKUP_SCRIPT") | crontab -

2.2 全量备份脚本（full_backup.sh）

2.2.1 脚本功能

每周日凌晨2点执行，完成MySQL全量数据导出、压缩、binlog位置记录、MD5校验及过期备份清理，核心目标是为增量备份提供“基准数据”。

2.2.2 执行流程

参数初始化：定义备份用户、密码、备份目录、日志目录等配置，生成带时间戳的备份文件名（如full_20251005_020000.sql.gz）；
目录创建：自动创建备份目录（/data/backup/mysql/full）和日志目录（/var/log/mysql_backup），避免目录不存在导致脚本报错；
全量导出：使用mysqldump执行全量备份，关键参数说明：
- --single-transaction：InnoDB表无锁备份，不阻塞业务读写；
- --master-data=2：在备份文件中以注释形式记录binlog位置（供增量备份使用）；
- --flush-logs：备份前切割binlog，确保后续增量备份从新binlog开始；
- --all-databases：导出所有数据库，适合全量备份场景；
binlog位置提取：从压缩后的备份文件中提取binlog位置信息，保存到binlog_pos_xxx.txt文件（增量备份的起点）；
完整性校验：生成备份文件的MD5值（如full_20251005_020000.sql.gz.md5），供后续监控脚本校验；
过期清理：删除30天前的全量备份文件（含备份文件、MD5文件、binlog位置文件），避免存储溢出；
日志记录：全程记录执行日志（如full_backup_20251005_020000.log），便于故障排查。

2.2.3 核心代码片段

# 执行全量备份（关键命令）
mysqldump -h${BACKUP_HOST} -u${BACKUP_USER} -p${BACKUP_PASS} \
    --single-transaction \
    --master-data=2 \
    --flush-logs \
    --hex-blob \
    --routines \
    --triggers \
    --events \
    --all-databases \
    --add-drop-database \
    --default-character-set=utf8mb4 \
    --max-allowed-packet=512M \
    2>> ${LOG_DIR}/full_backup_${DATE}.log \
    | gzip > ${FULL_BACKUP_FILE} 2>> ${LOG_DIR}/full_backup_${DATE}.log

# 提取binlog位置（供增量备份使用）
gunzip -c "${FULL_BACKUP_FILE}" | grep -m 1 "CHANGE MASTER TO" > "${BINLOG_POS_FILE}"

# 生成MD5校验值
md5sum "${FULL_BACKUP_FILE}" > "${FULL_BACKUP_MD5}"

# 清理30天前过期备份
find "${BACKUP_DIR}" -maxdepth 1 \( \
    -name "full_*.sql.gz" -o \
    -name "full_*.md5" -o \
    -name "binlog_pos_*.txt" \
\) -type f -mtime +30 -exec rm -f {} \;

2.3 增量备份脚本（incr_backup.sh）

2.3.1 脚本功能

周一至周六凌晨3点执行，基于全量备份的binlog起点，导出新增的binlog日志，实现“小范围、高效率”的增量数据备份。

2.3.2 执行流程

参数初始化：定义备份目录、全量备份路径（获取binlog起点）、增量备份目录等，生成带时间戳的增量目录（如incr_20251006_030000）；
binlog起点校验：从全量备份目录（/data/backup/mysql/full）获取最新的binlog_pos_xxx.txt文件，提取上次备份的binlog文件名（如mysql-bin.000012）和位置（如154），若文件不存在则终止备份（需先执行全量备份）；
binlog切割：执行FLUSH BINARY LOGS命令，切割当前正在写入的binlog，确保待备份的binlog文件内容完整（不再写入新数据）；
binlog列表筛选：从MySQL中获取所有binlog文件列表，筛选出“大于等于起点binlog、且排除最新切割后binlog”的目标文件（如只备份mysql-bin.000012），避免重复备份或备份未完成的binlog；
增量导出：使用mysqlbinlog命令导出目标binlog文件到增量目录，保留原始binlog格式（--raw参数），便于后续恢复；
完整性校验：生成所有导出binlog文件的MD5值（如incr_20251006_030000.md5），记录每个binlog文件的校验结果；
终点位置记录：获取切割后新binlog的文件名和位置，保存到binlog_pos_xxx.txt（供下次增量备份使用），并清理7天前的增量备份目录；
日志记录：全程记录执行日志（如incr_backup_20251006_030000.log），包含备份文件列表、校验结果等信息。

2.3.3 核心代码片段

# 提取全量备份的binlog起点
LAST_BINLOG=$(cat "${LAST_BINLOG_POS_FILE}" | grep "CHANGE MASTER TO" | grep -oP "MASTER_LOG_FILE='\K[^']+")
LAST_POS=$(cat "${LAST_BINLOG_POS_FILE}" | grep "CHANGE MASTER TO" | grep -oP 'MASTER_LOG_POS=\K\d+')

# 切割当前binlog
mysql -h"${BACKUP_HOST}" -u"${BACKUP_USER}" -p"${BACKUP_PASS}" -e "FLUSH BINARY LOGS;"

# 筛选目标binlog文件（排除最新切割的binlog）
ALL_LOGS=$(mysql -h"${BACKUP_HOST}" -u"${BACKUP_USER}" -p"${BACKUP_PASS}" -NBe "SHOW BINARY LOGS" | awk '{print $1}')
ALL_LOGS_FILTERED=$(echo "$ALL_LOGS" | awk -v start="$LAST_BINLOG" '$0 >= start' | sort)
TARGET_LOGS=$(echo "$ALL_LOGS_FILTERED" | head -n -1)

# 导出增量binlog
mysqlbinlog -h"${BACKUP_HOST}" -u"${BACKUP_USER}" -p"${BACKUP_PASS}" \
    --start-position="${LAST_POS}" \
    --read-from-remote-server \
    --raw \
    --result-file="${INCR_BACKUP_DIR}/" \
    ${TARGET_LOGS} 2>> "${LOG_DIR}/incr_backup_${DATE}.log"

# 记录下次增量备份的起点
echo "-- CHANGE MASTER TO MASTER_LOG_FILE='${LATEST_BINLOG_NAME}', MASTER_LOG_POS=${LATEST_POS};" > "${FULL_DIR}/binlog_pos_${DATE}.txt"

# 清理7天前过期增量备份
find "${INCR_DIR}" -maxdepth 1 -type d -name "incr_*" -mtime +7 -exec rm -rf {} \;

三、场景化部署与运维

3.1 部署前准备

3.1.1 环境要求

MySQL版本：确保为MySQL 5.7.17（脚本适配该版本权限和参数，其他5.7.x版本可兼容）；
磁盘空间：至少300GB空闲空间（4周全量备份约320GB + 7天增量备份约8.4GB，预留冗余）；
依赖工具：mysqldump（MySQL 5.7自带）、gzip（压缩备份文件）、mail（告警邮件，需安装mailx或mailutils）。

3.1.2 环境检查命令

# 检查MySQL版本
mysql --version

# 检查磁盘空间（备份目录所在分区）
df -h /data/backup

# 检查mysqldump是否可用
mysqldump --version

# 检查mail客户端是否安装
command -v mail

3.2 自动化部署步骤

3.2.1 脚本上传与参数修改

将3个核心脚本（full_backup.sh、incr_backup.sh、setup_cron.sh）上传到服务器/usr/local/mysql/backup/目录；

批量修改脚本中的配置参数（避免手动修改每个脚本）：

# 替换备份用户密码（将"your_secure_password"改为实际密码）
sed -i 's|your_secure_password|MyBackup@2025|' /usr/local/mysql/backup/*.sh

# 替换脚本路径（确保setup_cron.sh指向正确的备份脚本路径）
sed -i 's|/path/to/|/usr/local/mysql/backup/|' /usr/local/mysql/backup/setup_cron.sh

# 替换告警邮箱（将"admin@example.com"改为实际邮箱）
sed -i 's|admin@example.com|devops@xxx-restaurant.com|' /usr/local/mysql/backup/setup_cron.sh

监控脚本需配置邮箱发送功能，可参考Linux系统中配置邮箱的发送功能

3.2.2 执行部署脚本

# 赋予脚本执行权限
chmod +x /usr/local/mysql/backup/*.sh

# 执行部署脚本（需输入MySQL root密码，会重启MySQL）
sudo sh /usr/local/mysql/backup/setup_cron.sh

3.2.3 部署结果验证

检查binlog是否启用：

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin'"
# 预期结果：Value列显示"ON"

检查定时任务是否添加：

crontab -l
# 预期结果：包含全量备份（0 2 * * 0）和增量备份（0 3 * * 1-6）任务

手动执行一次全量备份（增量备份基于全量备份）：

sh /usr/local/mysql/backup/full_backup.sh
# 执行后检查备份文件是否生成
ls -lh /data/backup/mysql/full/
# 预期结果：存在full_xxx.sql.gz和binlog_pos_xxx.txt文件

3.3 常见故障与解决方案

3.3.1 全量备份超时

现象：备份日志显示“mysqldump: Error writing file”，备份文件未生成或不完整；
原因：150GB数据量较大，单线程导出受I/O或CPU瓶颈影响；
解决方案：
1. 在full_backup.sh中增加--quick参数（减少内存占用，分块读取数据）；
2. 增加--max_allowed_packet=512M参数，避免大字段导出失败
3. 备份期间临时关闭非必要服务（如报表生成、日志分析），释放服务器资源；
4. 若服务器支持，可将备份目录挂载到SSD磁盘，提升I/O速度。

3.3.2 增量备份binlog不连续

现象：增量备份日志显示“ERROR: Got error reading packet from server”，无法找到指定binlog文件；
原因：binlog文件被MySQL自动清理（默认expire_logs_days=7）或手动删除，导致增量备份起点丢失；
解决方案：
1. 立即执行全量备份，重建增量备份的基准起点：
```
sh /usr/local/mysql/backup/full_backup.sh
```
2. 修改my.cnf延长binlog保留时间（覆盖2个全量周期，如15天）（初始化脚本已添加）：
```
expire_logs_days = 14
```
3. 重启MySQL使配置生效：systemctl restart mysqld。

3.3.3 备份用户权限不足

现象：备份日志显示“Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s)”；
原因：setup_cron.sh创建用户时权限授予不完整，或后续权限被误删除；

解决方案：

# 手动授予备份用户完整权限
mysql -u root -p << EOF
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, EVENT, TRIGGER, SHOW DATABASES ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EOF

3.3.4 增量备份binlog筛选错误

现象：增量备份日志显示“Could not find first log file name in binary log index file”；
原因：早期脚本使用mysql-bin.0*通配符匹配binlog，导致备份已被清理的早期binlog文件；
解决方案：采用“动态筛选binlog列表”逻辑（如incr_backup.sh中的ALL_LOGS_FILTERED和TARGET_LOGS变量），仅备份当前需要的binlog文件，避免通配符匹配错误。

四、结果展示

4.1 自动化部署脚本

在这里插入图片描述

4.2 全量备份脚本

在这里插入图片描述

4.3 增量备份脚本

在这里插入图片描述

五、数据恢复流程

当数据库发生数据丢失（如误删表、数据损坏）时，需通过“全量备份+增量备份”恢复数据，步骤如下：

5.1 恢复全量备份（回到基准状态）

找到最新的全量备份文件（如full_20251005_020000.sql.gz）；

执行恢复命令（全程记录日志，便于排查问题）：

gunzip -c /data/backup/mysql/full/full_20251005_020000.sql.gz | mysql -u root -p -v > /var/log/mysql_backup/full_restore_log.log 2>&1

恢复完成后，数据库回到全量备份时的状态（如2025-10-05 02:00:00）。

5.2 恢复增量备份（补全最新数据）

确定增量备份范围：找到全量备份之后的所有增量备份目录（如incr_20251006_030000、incr_20251007_030000），按时间顺序排序；
获取全量备份的binlog起点：从全量备份的binlog_pos_20251005_020000.txt文件中查看起点，例如：
```
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;
```

按顺序恢复增量binlog：

# 恢复2025-10-06的增量备份
mysqlbinlog /data/backup/mysql/incremental/incr_20251006_030000/mysql-bin.000012 | mysql -u root -p

# 恢复2025-10-07的增量备份（若有）
mysqlbinlog /data/backup/mysql/incremental/incr_20251007_030000/mysql-bin.000013 | mysql -u root -p