MySQL数据库定时全量备份+binlog增量备份恢复演练
MySQL 数据库的定时全量备份加上 binlog 增量备份，是目前中小企业最常用的数据保护方案。
本文会从零开始，带你完成全量备份脚本、binlog 开启、crontab 定时执行，并模拟一次删除数据后的恢复演练。
整个过程都用命令实测，适合刚接手服务器运维、希望建立可验证备份体系的读者直接照做。
为什么全量备份要配合 binlog
全量备份相当于给数据库拍一张完整快照，只能恢复到备份那一刻的数据状态。
如果备份之后又有新数据写入，仅靠全量备份会丢失这部分变更。
binlog（二进制日志）会按顺序记录所有导致数据变化的 SQL，比如 INSERT、UPDATE、DELETE，因此只要保留从全量备份开始到故障发生前的 binlog，就能把数据库恢复到任意时间点。
全量负责基础，binlog 负责补全差异，两者结合才能把数据丢失窗口压到最小。
第一步：开启 binlog 并确认备份环境
binlog 默认在很多 MySQL 版本里并不开启，需要手动配置。
编辑 MySQL 配置文件，常见路径是 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，在 [mysqld] 段下加入：
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROWserver-id必须设置，否则 MySQL 无法启动 binlog 功能。log-bin指定 binlog 文件存放路径和前缀，MySQL 会自动生成类似mysql-bin.000001的文件。binlog_format推荐使用 ROW，能够更精细地记录每一行变更，恢复时更可控。
保存后重启 MySQL 服务，并确认日志已开启：
systemctl restart mysql
mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';"如果看到 log_bin 的值为 ON，说明 binlog 生效。
也可以用 SHOW BINARY LOGS; 查看当前已有的 binlog 文件列表。
第二步：编写全量备份脚本并加入 crontab 定时任务
全量备份用 mysqldump 最直接。
为了不影响线上读写，推荐使用 --single-transaction 参数，对 InnoDB 表可以在不锁表的情况下做一致快照。
创建一个备份脚本 /opt/backup/backup_mysql.sh：
#!/bin/bash
BACKUP_DIR=/opt/backup/mysql
DATE=$(date +%F_%H%M%S)
MYSQL_USER=root
MYSQL_PASS='你的数据库密码'
mkdir -p "${BACKUP_DIR}"
mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" --all-databases --single-transaction --flush-logs --master-data=2 > "${BACKUP_DIR}/full_${DATE}.sql"
find "${BACKUP_DIR}" -type f -name "*.sql" -mtime +7 -exec rm {} \;这个脚本做了两件关键事：--flush-logs 会刷新 binlog，
生成新的日志文件，
相当于把全量备份点之前的日志断开；--master-data=2 会在备份文件中记录全量备份时刻对应的 binlog 文件名和位置，
恢复时靠它找到增量应用的起点。
脚本写好后赋予执行权限，并加入 crontab：
chmod +x /opt/backup/backup_mysql.sh
crontab -e加入下面这行，表示每天凌晨 2 点执行一次：
0 2 * * * /opt/backup/backup_mysql.sh需要注意，把数据库密码写在脚本里存在安全隐患，建议将脚本权限设置为 700，本机仅 root 可读。
生产环境也可以改用 ~/.my.cnf 保存密码。
第三步：模拟误删数据，执行全量+增量恢复演练
恢复演练的目的是验证备份文件与 binlog 是否真的可用，不要等到出故障才临时抱佛脚。
先记录当前 binlog 和位置，然后模拟误删：
mysql -uroot -p -e "SHOW MASTER STATUS;"
# 记下 File 和 Position，比如 mysql-bin.000013, 1074接着删除一张测试表的数据，比如：
DELETE FROM orders WHERE id > 100;此时已经造成数据丢失，开始恢复。
第一步先恢复最近一次全量备份：
mysql -uroot -p < /opt/backup/mysql/full_20250118_020001.sql全量恢复后，数据仍然是备份时刻的状态，备份之后写入和删除的操作都还没恢复。
第二步把删除前的 binlog 增量日志补回去。
先找到全量备份记录的 binlog 坐标，打开备份文件头部能看到类似：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;这里说明全量备份是基于 mysql-bin.000012 的 154 位置。
之后 binlog 文件滚动到了 mysql-bin.000013，我们需要把 000012 从 154 位置之后的部分，以及 000013 整个文件，都应用上去。
使用 mysqlbinlog 导出 SQL 并导入：
mysqlbinlog --start-position=154 /var/log/mysql/mysql-bin.000012 /var/log/mysql/mysql-bin.000013 > /tmp/binlog_restore.sql
mysql -uroot -p < /tmp/binlog_restore.sql恢复完成后，查询刚才误删的数据是否回来了：
SELECT COUNT(*) FROM orders WHERE id > 100;如果返回正常数据，说明全量+增量恢复成功。
常见问题与避坑要点
binlog 没开启就无法做增量。 如果 SHOW VARIABLES LIKE 'log_bin'; 输出 OFF，需要按本文第一步修改配置并重启后再做全量备份。
注意，开启 binlog 后，之前没有 binlog 的时间段已经无法追加恢复。
恢复时时间点不好把握。 如果不需要恢复到具体时间点，只想恢复到误删操作之前，可以使用 mysqlbinlog --stop-datetime='2025-01-18 10:30:00' 精确控制截止时间。
前提是删除操作发生时 binlog 完整保存在本地。
不要忽略全量备份的文件权限。 mysqldump 生成的 SQL 文件可能包含敏感数据，建议将备份目录设置为 700，并定期检查磁盘空间，避免 binlog 和备份文件把磁盘写满。
定期做恢复演练比备份本身更重要。 建议每季度至少执行一次本文的第三步，把恢复命令固化到文档里。
只有真正跑通一次恢复流程，才能在故障发生时心里有底。
如果你正在处理自己的 MySQL 备份恢复方案，建议先按本文步骤完整执行一遍，再根据自己的数据量、备份频率和业务容忍度做微调。
遇到备份或恢复异常时，优先回看 binlog 文件列表、全量备份坐标和磁盘剩余空间这三项。
