Mysql数据库备份
备份的321原则
3份拷贝，2种介质，1份异地
方案的架构选型
| 备份层级 | 内容 | 频率 | 存储位置 |
|---|---|---|---|
| 全量备份 | mysqldump 逻辑备份 + binlog位置信息 | 每日凌晨2:00 | 本地 + 异地 |
| 增量备份 | binlog日志归档 | 每小时或实时 | 本地 + 异地 |
| 3份拷贝 | 本地备份 + 异地同步 + 异地二次归档 | — | 2种介质，1份异地 |
第一步：数据库准备（开启binlog）
- 数据库准备
[mysqld]
# 开启binlog（必须）
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW # 推荐ROW格式，便于PITR
expire_logs_days = 7 # 本地binlog保留7天（防止磁盘爆满）
max_binlog_size = 1G # 每个binlog文件1G后切换
server-id = 1 # 必须唯一，即使单机也需设置
- 配置完成后，重启MySQL服务：
systemctl restart mysqld
- 验证是否开启：
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
第二步：自动化全量备份脚本
#!/bin/bash
# mysql_full_backup.sh - MySQL 3-2-1 全量备份脚本
# ===== 配置区域 =====
BACKUP_BASE="/data/mysql_backup"
FULL_DIR="${BACKUP_BASE}/full"
ARCHIVE_DIR="${BACKUP_BASE}/archive"
REMOTE_USER="backup_user"
REMOTE_HOST="192.168.100.100" # 异地服务器IP
REMOTE_PATH="/remote_mysql_backup"
MYSQL_USER="root"
MYSQL_PASS="你的密码"
RETENTION_DAYS=7 # 本地保留最近7天全量备份
DATE=$(date +%Y%m%d_%H%M%S)
FULL_FILE="${FULL_DIR}/full_${DATE}.sql.gz"
LOG_FILE="${BACKUP_BASE}/backup_${DATE}.log"
# ===================
# 创建目录
mkdir -p ${FULL_DIR} ${ARCHIVE_DIR}
# 记录开始
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始全量备份..." >> ${LOG_FILE}
# 1. 执行全量备份（带 --master-data=2 记录binlog位置）
# --single-transaction: InnoDB一致性快照，不影响业务
# --flush-logs: 备份前刷新日志，便于后续增量恢复
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} \
--all-databases \
--single-transaction \
--master-data=2 \
--flush-logs \
--triggers \
--routines \
--events \
| gzip > ${FULL_FILE}
# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ✅ 全量备份成功: ${FULL_FILE}" >> ${LOG_FILE}
# 提取binlog位置（用于恢复参考）
gunzip -c ${FULL_FILE} | grep -m 1 "CHANGE MASTER TO" >> ${LOG_FILE}
else
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ❌ 全量备份失败！" >> ${LOG_FILE}
exit 1
fi
# 2. 归档当前binlog（备份后已自动刷新，新binlog从0开始）
# 这一步确保全量备份和binlog归档点清晰对应
# 3. 清理本地过期全量备份（保留7天）
find ${FULL_DIR} -name "full_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
find ${ARCHIVE_DIR} -name "mysql-bin.*" -mtime +${RETENTION_DAYS} -delete
# 4. 同步到异地服务器（实现第2份拷贝）
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始同步到异地: ${REMOTE_HOST}" >> ${LOG_FILE}
rsync -avz --delete \
${BACKUP_BASE}/ \
${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}/ \
>> ${LOG_FILE} 2>&1
if [ $? -eq 0 ]; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ✅ 异地同步成功" >> ${LOG_FILE}
else
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ⚠️ 异地同步失败，请检查网络" >> ${LOG_FILE}
fi
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 备份流程结束" >> ${LOG_FILE}
echo "----------------------------------------" >> ${LOG_FILE}
赋予执行权限：
chmod +x /usr/local/bin/mysql_full_backup.sh
第三步：binlog连续归档（增量备份）脚本
创建 mysql_binlog_archive.sh，每小时执行一次：
#!/bin/bash
# mysql_binlog_archive.sh - 归档binlog到本地+异地
BACKUP_BASE="/data/mysql_backup"
ARCHIVE_DIR="${BACKUP_BASE}/archive"
MYSQL_USER="root"
MYSQL_PASS="你的密码"
REMOTE_USER="backup_user"
REMOTE_HOST="192.168.100.100"
REMOTE_PATH="/remote_mysql_backup/archive"
# 获取当前binlog列表
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW MASTER STATUS\G" > /tmp/binlog_status.txt
CURRENT_BINLOG=$(grep "File:" /tmp/binlog_status.txt | awk '{print $2}')
# 找出已归档但未被删除的binlog文件
# （简单策略：拷贝所有非当前binlog到归档目录）
for binlog in /var/lib/mysql/mysql-bin.[0-9]*; do
filename=$(basename ${binlog})
# 跳过当前正在写入的binlog
if [ "${filename}" == "${CURRENT_BINLOG}" ]; then
continue
fi
# 如果还没归档过，就拷贝
if [ ! -f "${ARCHIVE_DIR}/${filename}" ]; then
cp ${binlog} ${ARCHIVE_DIR}/${filename}
echo "[$(date)] 归档: ${filename}" >> ${BACKUP_BASE}/binlog_archive.log
fi
done
# 同步到异地（实现实时异地备份）
rsync -avz ${ARCHIVE_DIR}/ ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}/ >> ${BACKUP_BASE}/binlog_sync.log 2>&1
第四步：定时任务配置 (Cron)
编辑 postgres 用户的 crontab：
# 每天凌晨2:00 执行全量备份
0 2 * * * /usr/local/bin/mysql_full_backup.sh
# 每小时执行binlog归档
0 * * * * /usr/local/bin/mysql_binlog_archive.sh
第五步：异地服务器的二次归档（实现第3份拷贝）
在异地服务器（192.168.100.100）上创建脚本 /remote_backup/archive_2nd.sh：
#!/bin/bash
# 异地服务器上的二次归档脚本
REMOTE_BASE="/remote_mysql_backup"
SECONDARY_DIR="/remote_mysql_backup_secondary"
DATE=$(date +%Y%m%d)
# 打包当天的备份为tar.gz（二次拷贝）
tar -czf ${SECONDARY_DIR}/full_backup_${DATE}.tar.gz ${REMOTE_BASE}/full/
tar -czf ${SECONDARY_DIR}/archive_${DATE}.tar.gz ${REMOTE_BASE}/archive/
# 保留最近30天的二次归档
find ${SECONDARY_DIR} -name "*.tar.gz" -mtime +30 -delete
在异地服务器的cron中配置每天凌晨3点执行：
0 3 * * * /remote_backup/archive_2nd.sh
最终备份结构
本地服务器 (/data/mysql_backup/)
├── full/
│ ├── full_20260713_020001.sql.gz # 第1份拷贝（本地全量）
│ └── full_20260712_020001.sql.gz
├── archive/
│ ├── mysql-bin.000001 # 第1份拷贝（本地binlog）
│ ├── mysql-bin.000002
│ └── ...
└── backup_*.log # 日志
异地服务器 (/remote_mysql_backup/)
├── full/ # 第2份拷贝（rsync同步）
│ └── (同本地full目录)
└── archive/ # 第2份拷贝（rsync同步）
└── (同本地archive目录)
异地服务器二次目录 (/remote_mysql_backup_secondary/)
├── full_backup_20260713.tar.gz # 第3份拷贝（二次打包）
├── archive_20260713.tar.gz
└── ...
恢复演练参考
# 1. 恢复全量备份
gunzip -c full_20260713_020001.sql.gz | mysql -u root -p
# 2. 查看备份中的binlog位置
gunzip -c full_20260713_020001.sql.gz | grep "CHANGE MASTER TO"
# 输出: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=155
# 3. 应用增量日志到目标时间点
mysqlbinlog --start-position=155 \
--stop-datetime="2026-07-13 14:30:00" \
/data/mysql_backup/archive/mysql-bin.000010 \
/data/mysql_backup/archive/mysql-bin.000011 \
| mysql -u root -p
注意：建议使用--start-position和--stop-position这样基于位置的参数进行恢复，因为它们比基于时间的参数更精确，可以避免意外丢失或包含事件的风险
例如，查看 bin.123456 文件中某天20:05至20:08之间的日志，并搜索DROP TABLE操作：
mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
--stop-datetime="2020-03-11 20:08:00" --verbose \
/var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
在输出中，你会看到类似 # at 232 的信息
然后执行以下命令进行恢复
mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 | mysql -u root -p
方案要点总结
| 要求 | 实现方式 |
|---|---|
| 3份拷贝 | ①本地备份目录 ②异地rsync同步目录 ③异地二次打包压缩 |
| 2种介质 | ①本地服务器硬盘 ②异地服务器硬盘（物理独立） |
| 1份异地 | 通过rsync同步到异地服务器，实现异地容灾 |
| 全量+增量 | 每日全量(mysqldump) + 每小时binlog归档 |
| PITR支持 | 通过--master-data=2记录位置，配合binlog重放 |
浙公网安备 33010602011771号