MySQL binlog 时间点恢复实战：从凌晨备份精确重放到下午误删前一秒，位点定位与恢复演练脚本

Author： zz1984
发布时间：September 28, 2026
1 views
No comments
9112 字数
Categories：技术教程

为什么「每天备份」不等于「数据安全」

个人站长做备份，绝大多数人停在「每天凌晨 mysqldump 一次」。这套方案能挡住磁盘损坏，但挡不住一类更常见的事故：下午三点误删了某张表的数据，而备份是凌晨两点的。这时候你手里最完整的一份数据就是 13 小时前的，中间注册的用户、写下的文章、产生的订单全部丢失。如果站上有付费内容，这 13 小时的缺口就是实打实的损失。

要补上这个缺口，靠的就是 binlog（二进制日志，binary log）。它按时间顺序记录了所有会改变数据的语句（或者行变更），本质是一盘「数据库录像带」。备份负责给你一个起点，binlog 负责把你从起点「快进」到任意一个瞬间——这就是 PITR，Point-In-Time Recovery，时间点恢复。

先确认 binlog 开着没

MySQL 8 默认就开启了 binlog（MariaDB 也是），但老配置或某些面板装出来的实例可能是关的。一条命令确认：

mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW VARIABLES LIKE 'server_id';"

关注四个值：

log_bin 必须是 ON。如果是 OFF，需要改配置重启（log-bin = /var/lib/mysql/mysql-bin），重启后才会开始记录，之前的数据没有 binlog 可补。
binlog_format：MySQL 8 默认 ROW，这是 PITR 场景下最可靠的格式，能精确重放行的变化，不会出现 NOW()、RAND() 这类不确定函数导致主从不一致的问题。不要改成 STATEMENT，除非你明确知道自己在做什么。
binlog_expire_logs_seconds：默认 30 天（2592000 秒）。你说得出口的恢复窗口就只能到这儿。如果要留 90 天，把它设成 7776000。
server_id：单机 PITR 也必须是非 0 的唯一值，否则某些版本会拒绝写 binlog。

看一眼 binlog 长什么样

恢复之前，先学会「读」。两个工具：SHOW BINARY LOGS 看有哪些文件，mysqlbinlog 看文件里有什么。

mysql -uroot -p -e "SHOW BINARY LOGS;"
# +------------------+-----------+-----------+
# | Log_name         | File_size | Encrypted |
# +------------------+-----------+-----------+
# | mysql-bin.000017 | 117245024 | No        |
# | mysql-bin.000018 |   1048581 | No        |
# +------------------+-----------+-----------+

# 按时间窗口查看，只列出「有数据变化的库」的语句摘要
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  --start-datetime="2026-09-28 14:00:00" \
  --stop-datetime="2026-09-28 15:00:00" \
  /var/lib/mysql/mysql-bin.000018 | grep -A 20 "### DELETE"

--base64-output=DECODE-ROWS -vv 是把 ROW 格式的二进制事件翻译成人能读的伪 SQL，排查误操作时极其有用。你会看到类似 ### DELETE FROM zz1984.typecho_contents 加上具体的主键值——这就能精确定位「误删发生在哪一秒」。

⚠️ 注意：绝对不能直接改 /var/lib/mysql 里的 binlog 文件（那是 SQLite 之外的另一类「别乱动」的东西），要处理必须先 cp 到 /tmp 再操作。

完整 PITR 演练：从凌晨备份恢复到下午 15:00

下面是我实际演练过的流程。假设事故场景：凌晨 02:00 有一次全量备份，下午 14:52 有人误执行了 DELETE FROM orders WHERE status=0，需要在保持 14:52 之前所有数据的前提下，跳过这一条误操作。

第 1 步：确认起点位置

# 在备份文件里找到当时的 binlog 位置（mysqldump 会写在文件头部）
head -30 /backup/zz1984_20260928_020000.sql | grep -i "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=4;

记下 mysql-bin.000017 和 4。如果你的 dump 是 --single-transaction 导出的，这个位置是准确的一致性起点；如果是 --lock-all-tables，同样可靠。

第 2 步：先把全量恢复回去

mysql -uroot -p -e "DROP DATABASE IF EXISTS zz1984; CREATE DATABASE zz1984 CHARACTER SET utf8mb4;"
mysql -uroot -p zz1984 < /backup/zz1984_20260928_020000.sql

注意：这一步之后数据库回到了 02:00 的状态。在继续之前先起一个临时的只读展示，确认数据确实是备份时间点的样子，别一口气把 binlog 也灌进去，错了就又要重来。

第 3 步：定位误操作的精确位置

# 找到误删事件所在文件与偏移
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  --start-datetime="2026-09-28 14:50:00" \
  --stop-datetime="2026-09-28 14:55:00" \
  /var/lib/mysql/mysql-bin.000018 > /tmp/scan.txt

grep -n "DELETE FROM" /tmp/scan.txt | head
# 记下这一行上方的 "at 1234567" 偏移 —— 这就是要停下的位置
grep -n "^# at " /tmp/scan.txt | head -50

mysqlbinlog -vv 的输出里，每个事件的头部都有形如 # at 1048576 的偏移标记，紧跟在下面的是 #260928 14:52:41 server id 1 end_log_pos 1048663 这种带时间戳的行。找到第一个匹配到误删 SQL 的 # at 值，那就是恢复的停止位置。

第 4 步：重放 binlog 到误操作前一秒

# 从备份的起点开始，重放到误删事件之前的偏移
mysqlbinlog --start-position=4 \
  --stop-position=1048576 \
  /var/lib/mysql/mysql-bin.000017 \
  /var/lib/mysql/mysql-bin.000018 \
  | mysql -uroot -p zz1984 -v

如果起点在 000017、终点在 000018，就把两个文件名都列出来，mysqlbinlog 会按顺序处理。--start-position 只在第一个文件生效，--stop-position 只在最后一个文件生效，这正是我们想要的跨文件区间语义。

重放完成后 SELECT COUNT(*) FROM orders; 应该等于「02:00 的行数 + 02:00 到 14:52 之间新增的行数」，而 14:52 那条 DELETE 没有执行。

第 5 步：把误删的数据单独补回来

如果误删的是一张独立的表，更省事的办法是只重放「DELETE 事件本身的反向操作」。ROW 格式下 mysqlbinlog -vv 会完整打印被删行的所有列值，可以直接把那批 ### DELETE 块改写成 INSERT 语句插回去。行数少的时候手写几行就够了；行数多的话，用脚本把 ### @1=123 这类字段自动拼成 INSERT，是最稳妥的捞数据方式。

自动化：让恢复不再依赖人肉演练

PITR 最大的风险是「出事那天才发现命令记不住」。建议把恢复流程脚本化，平时每季度用备份在测试库上跑一遍：

#!/bin/bash
set -euo pipefail
DUMP="${1:?用法: pitr.sh /backup/xxx.sql 开始位置 停止位置}"
START_POS="${2:-4}"
STOP_POS="${3:-999999999}"
TESTDB="pitr_test_$(date +%s)"

mysql -uroot -p"$DBPW" -e "CREATE DATABASE $TESTDB CHARACTER SET utf8mb4;"
mysql -uroot -p"$DBPW" "$TESTDB" < "$DUMP"

# 找出 dump 头部的 binlog 文件名，并按名字顺序喂进去
FILES=$(mysql -uroot -p"$DBPW" -N -e "SHOW BINARY LOGS;" | awk '{print "/var/lib/mysql/"$1}' | tr '\n' ' ')
mysqlbinlog --start-position="$START_POS" --stop-position="$STOP_POS" $FILES \
  | mysql -uroot -p"$DBPW" "$TESTDB"

echo "恢复到 $TESTDB，校验后手动 DROP"

关键点是在测试库上做，绝不在生产库上直接演练。演练通过 → 删掉测试库 → 心里有底。

五个容易翻车的地方

binlog 文件被提前清理了。默认 30 天，但如果你把 binlog_expire_logs_seconds 设得很小，或者有人手动 PURGE BINARY LOGS 过，恢复窗口就直接断掉了。把「备份保留时长」和「binlog 保留时长」对齐，写进运维文档。
恢复时用 --start-datetime 而不是 --start-position。时间戳恢复看起来直观，但同一条 SQL 可能在多个位置出现，跨文件时边界极难把握。恢复生产库永远用 position，时间戳只用来「找位置」。
忘了 --single-transaction。如果备份时用了 --lock-tables 之外的方式导出且没加这个参数，dump 出来的位置可能不对应一致快照，重放会报主键冲突。检查 dump 头部的 CHANGE MASTER 注释是否存在。
字符集不一致。重放前确认目标库是 utf8mb4，否则中文会变问号。这也是我把 CREATE DATABASE ... utf8mb4 写进脚本的原因。
没关掉重放时的唯一性检查。极端情况下（比如误操作是 UPDATE 而不是 DELETE），可以临时 SET sql_log_bin=0; 避免重放动作本身又写进 binlog 造成二次污染。重放结束记得恢复。

把恢复窗口做扎实：三个配套动作

PITR 能不能用得上，取决于平时有没有把「恢复所需的材料」备齐。下面这三件事做完，才算真正有了可用的恢复能力。

动作一：把 binlog 单独备份出去

binlog 和数据库放在同一块盘上，是这套方案最脆弱的环节——磁盘一坏，录像带和数据一起没了。正确做法是把 binlog 也纳入异地备份范围：

# 每天凌晨在 flush 之后，把已归档的 binlog 同步到备份机
mysql -uroot -p"$DBPW" -e "FLUSH BINARY LOGS;"
rsync -a --remove-source-files /var/lib/mysql/mysql-bin.[0-9]* \
      backup@10.0.0.9:/backup/binlog/
# 备份端保留 90 天
find /backup/binlog -name 'mysql-bin.*' -mtime +90 -delete

FLUSH BINARY LOGS 会立刻关闭当前 binlog 文件并开一个新的，这样被 rsync 走的文件都是「已写完、不会再被追加」的，避免同步到一个正在写入的文件导致内容损坏。

动作二：记录每次备份的「起点坐标」

mysqldump 生成的 CHANGE MASTER 注释藏在文件中间（不在第一行），用 grep 全文件搜一次，把结果落进一张表里，下次恢复直接查：

mysql -uroot -p"$DBPW" -e "
CREATE TABLE IF NOT EXISTS ops.backup_log(
  id INT AUTO_INCREMENT PRIMARY KEY,
  dump_file VARCHAR(255),
  binlog_file VARCHAR(64),
  binlog_pos BIGINT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);"
# 备份脚本里追加一行：
POS=$(grep -m1 "CHANGE MASTER" "$DUMP" | sed -E "s/.*MASTER_LOG_FILE='([^']+)'.*MASTER_LOG_POS=([0-9]+).*/\1 \2/")
mysql -uroot -p"$DBPW" -e "INSERT INTO ops.backup_log(dump_file,binlog_file,binlog_pos)
  VALUES('$DUMP','${POS% *}',${POS#* });"

有了这张表，凌晨三点被叫起来恢复的时候，不需要再去翻文件头找坐标，查一下就能动手，这是实实在在的减负。

动作三：给恢复设一个「只读复核对拍」

恢复完最容易出的错是「以为成功了」。建议恢复后立刻做三项对拍：

# 1) 表数量是否与预期一致
mysql -uroot -p"$DBPW" -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='zz1984';"

# 2) 关键表的行数与最大 ID（能反映恢复到了哪个时间点）
mysql -uroot -p"$DBPW" zz1984 -e "SELECT COUNT(*) AS c, MAX(id) AS max_id FROM orders;"

# 3) 抽样一条业务上能人工判断的记录
mysql -uroot -p"$DBPW" zz1984 -e "SELECT id,created FROM orders ORDER BY id DESC LIMIT 5;"

第三条最重要：行数对不上太正常了（可能还有其它写入），但「最新一条业务记录的时间戳是否落在你预期的区间内」是业务人能一眼判断的。把这个时间告诉业务方，让他们确认，比你自己盯着 SQL 猜要可靠得多。

常见问题

Q：binlog 占空间吗？
A：占，而且增长可能比你想象得快。ROW 格式下一次 UPDATE t SET x=1 更新 10 万行，binlog 里就是 10 万条行变更。用 du -sh /var/lib/mysql/mysql-bin.* 定期看，配合 30~90 天的保留策略，个人站的量级一般每月几个 GB，可以接受。

Q：能只恢复一张表吗？
A：binlog 是整库级别的录像，没有「只重放某张表」的原生选项。实践中要么全量重放到临时库后 mysqldump 单表导回生产，要么用 --database=zz1984 过滤掉其它库（注意这个选项在 ROW 格式下是能用的，但同一库内的多表过滤需要 -vv 输出后人工筛）。

Q：主从复制会用到 binlog 吗？
A：会。从库就是靠拉取主库 binlog 重放来保持同步的，所以主库的 binlog 保留时长决定了从库掉线后能追多久的进度。如果从库断开超过保留期，就必须重做全量同步。这一点在做主从架构时特别重要。

把每天一次的全量备份升级成「全量 + binlog PITR」，是这个工作里性价比最高的一次改造：加的东西不多（确认参数、写一个恢复脚本、季度演练），但换来的能力是从「只能回到昨天」变成「可以回到任意一秒」。对个人站长而言，这种「有底」的感觉比任何性能优化都更值钱。

Last modification：September 28th, 2026 at 09:24 pm

MySQL binlog 时间点恢复实战：从凌晨备份精确重放到下午误删前一秒，位点定位与恢复演练脚本

zz1984 • 2026 年 09 月 28 日

为什么「每天备份」不等于「数据安全」

先确认 binlog 开着没

MySQL 8 默认就开启了 binlog（MariaDB 也是），但老配置或某些面板装出来的实例可能是关的。一条命令确认：

mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW VARIABLES LIKE 'server_id';"

关注四个值：

log_bin 必须是 ON。如果是 OFF，需要改配置重启（log-bin = /var/lib/mysql/mysql-bin），重启后才会开始记录，之前的数据没有 binlog 可补。
binlog_format：MySQL 8 默认 ROW，这是 PITR 场景下最可靠的格式，能精确重放行的变化，不会出现 NOW()、RAND() 这类不确定函数导致主从不一致的问题。不要改成 STATEMENT，除非你明确知道自己在做什么。
binlog_expire_logs_seconds：默认 30 天（2592000 秒）。你说得出口的恢复窗口就只能到这儿。如果要留 90 天，把它设成 7776000。
server_id：单机 PITR 也必须是非 0 的唯一值，否则某些版本会拒绝写 binlog。

看一眼 binlog 长什么样

恢复之前，先学会「读」。两个工具：SHOW BINARY LOGS 看有哪些文件，mysqlbinlog 看文件里有什么。

mysql -uroot -p -e "SHOW BINARY LOGS;"
# +------------------+-----------+-----------+
# | Log_name         | File_size | Encrypted |
# +------------------+-----------+-----------+
# | mysql-bin.000017 | 117245024 | No        |
# | mysql-bin.000018 |   1048581 | No        |
# +------------------+-----------+-----------+

# 按时间窗口查看，只列出「有数据变化的库」的语句摘要
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  --start-datetime="2026-09-28 14:00:00" \
  --stop-datetime="2026-09-28 15:00:00" \
  /var/lib/mysql/mysql-bin.000018 | grep -A 20 "### DELETE"

⚠️ 注意：绝对不能直接改 /var/lib/mysql 里的 binlog 文件（那是 SQLite 之外的另一类「别乱动」的东西），要处理必须先 cp 到 /tmp 再操作。

完整 PITR 演练：从凌晨备份恢复到下午 15:00

第 1 步：确认起点位置

# 在备份文件里找到当时的 binlog 位置（mysqldump 会写在文件头部）
head -30 /backup/zz1984_20260928_020000.sql | grep -i "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=4;

记下 mysql-bin.000017 和 4。如果你的 dump 是 --single-transaction 导出的，这个位置是准确的一致性起点；如果是 --lock-all-tables，同样可靠。

第 2 步：先把全量恢复回去

mysql -uroot -p -e "DROP DATABASE IF EXISTS zz1984; CREATE DATABASE zz1984 CHARACTER SET utf8mb4;"
mysql -uroot -p zz1984 < /backup/zz1984_20260928_020000.sql

第 3 步：定位误操作的精确位置

# 找到误删事件所在文件与偏移
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  --start-datetime="2026-09-28 14:50:00" \
  --stop-datetime="2026-09-28 14:55:00" \
  /var/lib/mysql/mysql-bin.000018 > /tmp/scan.txt

grep -n "DELETE FROM" /tmp/scan.txt | head
# 记下这一行上方的 "at 1234567" 偏移 —— 这就是要停下的位置
grep -n "^# at " /tmp/scan.txt | head -50

第 4 步：重放 binlog 到误操作前一秒

# 从备份的起点开始，重放到误删事件之前的偏移
mysqlbinlog --start-position=4 \
  --stop-position=1048576 \
  /var/lib/mysql/mysql-bin.000017 \
  /var/lib/mysql/mysql-bin.000018 \
  | mysql -uroot -p zz1984 -v

重放完成后 SELECT COUNT(*) FROM orders; 应该等于「02:00 的行数 + 02:00 到 14:52 之间新增的行数」，而 14:52 那条 DELETE 没有执行。

第 5 步：把误删的数据单独补回来

自动化：让恢复不再依赖人肉演练

PITR 最大的风险是「出事那天才发现命令记不住」。建议把恢复流程脚本化，平时每季度用备份在测试库上跑一遍：

#!/bin/bash
set -euo pipefail
DUMP="${1:?用法: pitr.sh /backup/xxx.sql 开始位置 停止位置}"
START_POS="${2:-4}"
STOP_POS="${3:-999999999}"
TESTDB="pitr_test_$(date +%s)"

mysql -uroot -p"$DBPW" -e "CREATE DATABASE $TESTDB CHARACTER SET utf8mb4;"
mysql -uroot -p"$DBPW" "$TESTDB" < "$DUMP"

# 找出 dump 头部的 binlog 文件名，并按名字顺序喂进去
FILES=$(mysql -uroot -p"$DBPW" -N -e "SHOW BINARY LOGS;" | awk '{print "/var/lib/mysql/"$1}' | tr '\n' ' ')
mysqlbinlog --start-position="$START_POS" --stop-position="$STOP_POS" $FILES \
  | mysql -uroot -p"$DBPW" "$TESTDB"

echo "恢复到 $TESTDB，校验后手动 DROP"

关键点是在测试库上做，绝不在生产库上直接演练。演练通过 → 删掉测试库 → 心里有底。

五个容易翻车的地方

binlog 文件被提前清理了。默认 30 天，但如果你把 binlog_expire_logs_seconds 设得很小，或者有人手动 PURGE BINARY LOGS 过，恢复窗口就直接断掉了。把「备份保留时长」和「binlog 保留时长」对齐，写进运维文档。
恢复时用 --start-datetime 而不是 --start-position。时间戳恢复看起来直观，但同一条 SQL 可能在多个位置出现，跨文件时边界极难把握。恢复生产库永远用 position，时间戳只用来「找位置」。
忘了 --single-transaction。如果备份时用了 --lock-tables 之外的方式导出且没加这个参数，dump 出来的位置可能不对应一致快照，重放会报主键冲突。检查 dump 头部的 CHANGE MASTER 注释是否存在。
字符集不一致。重放前确认目标库是 utf8mb4，否则中文会变问号。这也是我把 CREATE DATABASE ... utf8mb4 写进脚本的原因。
没关掉重放时的唯一性检查。极端情况下（比如误操作是 UPDATE 而不是 DELETE），可以临时 SET sql_log_bin=0; 避免重放动作本身又写进 binlog 造成二次污染。重放结束记得恢复。

把恢复窗口做扎实：三个配套动作

PITR 能不能用得上，取决于平时有没有把「恢复所需的材料」备齐。下面这三件事做完，才算真正有了可用的恢复能力。

动作一：把 binlog 单独备份出去

binlog 和数据库放在同一块盘上，是这套方案最脆弱的环节——磁盘一坏，录像带和数据一起没了。正确做法是把 binlog 也纳入异地备份范围：

# 每天凌晨在 flush 之后，把已归档的 binlog 同步到备份机
mysql -uroot -p"$DBPW" -e "FLUSH BINARY LOGS;"
rsync -a --remove-source-files /var/lib/mysql/mysql-bin.[0-9]* \
      backup@10.0.0.9:/backup/binlog/
# 备份端保留 90 天
find /backup/binlog -name 'mysql-bin.*' -mtime +90 -delete

动作二：记录每次备份的「起点坐标」

mysqldump 生成的 CHANGE MASTER 注释藏在文件中间（不在第一行），用 grep 全文件搜一次，把结果落进一张表里，下次恢复直接查：

mysql -uroot -p"$DBPW" -e "
CREATE TABLE IF NOT EXISTS ops.backup_log(
  id INT AUTO_INCREMENT PRIMARY KEY,
  dump_file VARCHAR(255),
  binlog_file VARCHAR(64),
  binlog_pos BIGINT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);"
# 备份脚本里追加一行：
POS=$(grep -m1 "CHANGE MASTER" "$DUMP" | sed -E "s/.*MASTER_LOG_FILE='([^']+)'.*MASTER_LOG_POS=([0-9]+).*/\1 \2/")
mysql -uroot -p"$DBPW" -e "INSERT INTO ops.backup_log(dump_file,binlog_file,binlog_pos)
  VALUES('$DUMP','${POS% *}',${POS#* });"

有了这张表，凌晨三点被叫起来恢复的时候，不需要再去翻文件头找坐标，查一下就能动手，这是实实在在的减负。

动作三：给恢复设一个「只读复核对拍」

恢复完最容易出的错是「以为成功了」。建议恢复后立刻做三项对拍：

# 1) 表数量是否与预期一致
mysql -uroot -p"$DBPW" -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='zz1984';"

# 2) 关键表的行数与最大 ID（能反映恢复到了哪个时间点）
mysql -uroot -p"$DBPW" zz1984 -e "SELECT COUNT(*) AS c, MAX(id) AS max_id FROM orders;"

# 3) 抽样一条业务上能人工判断的记录
mysql -uroot -p"$DBPW" zz1984 -e "SELECT id,created FROM orders ORDER BY id DESC LIMIT 5;"

为什么「每天备份」不等于「数据安全」

先确认 binlog 开着没

看一眼 binlog 长什么样

完整 PITR 演练：从凌晨备份恢复到下午 15:00

第 1 步：确认起点位置

第 2 步：先把全量恢复回去

第 3 步：定位误操作的精确位置

第 4 步：重放 binlog 到误操作前一秒

第 5 步：把误删的数据单独补回来

自动化：让恢复不再依赖人肉演练

五个容易翻车的地方

把恢复窗口做扎实：三个配套动作

动作一：把 binlog 单独备份出去

动作二：记录每次备份的「起点坐标」

动作三：给恢复设一个「只读复核对拍」

常见问题

相关文章