一次误删的数学题上一篇把备份的三种形态和时间账立好了全量备份给你一个过去的完整账面binlog 归档给你账面之间所有变化。PITRPoint-in-Time Recovery就是把两者接起来的函数恢复(T) 全量(T0) 重放 binlog(T0→T)其中 T 选在灾难发生前一刻。听起来一行公式工程上魔鬼藏在两处截断点的原子性——binlog 是按事件组织的、事务是按边界提交的按时间一刀切下去很容易把事务砍成两半可闪回性——行格式日志带前后镜像理论上能把误改误删倒放回去但前提条件相当苛刻。这一篇用一个账户表把这两个魔鬼都模拟出来。机制从全量到目标时刻之间发生了什么标准 PITR 流程六步止血误操作还在扩大就先把应用写流量摘掉确认事故只污染了目标表则原库保持运行、另起恢复流程→定时刻用审计 SQL、变更工单、SHOW PROCESSLIST历史或 binlog 时间戳锁定 T_bad→临时实例恢复全量永远不要在原库上直接回滚原库是人质的持有者误删后的增量数据还在它身上→重放 binlog 到 T_bad−εmysqlbinlog --start-position/--stop-position或--start-datetime/--stop-datetimeGTID 环境用--exclude-gtids精准剔除坏事务更快→校验守恒对账、抽样比对、业务只读验证→回灌把恢复实例里的目标表导回生产全程SET sql_log_bin0防止回灌本身被复制污染再恢复应用写流量。重放阶段的语义必须掰开binlog 里的时间戳属于事件而是否该被重放属于事务。一个事务的语句可能持续几十秒大 UPDATE、批处理它的开始事件、行事件、XID 提交事件各带各的时间戳--stop-datetime的截断是以事务为原子的——落在截断点内开始的完整事务会被重放到它的 XID 为止没等到 XID 的事务整体丢弃。理解这一条就能理解为什么停在半路的恢复比没恢复更糟半截的大事务比数据缺失更难向业务解释。另一个实用分支是闪回flashback不恢复整库只把误操作那几分钟倒带。它的原料是行事件的 before/after 镜像——DELETE倒放成INSERT、UPDATE倒放成用 before 图像的反向更新、并且要按事件逆序应用。前提条件三条binlog_formatROW、binlog_row_imageFULL第 1 篇就埋的伏笔、binlog 还在binlog_expire_logs_seconds未冲掉。DDL 和 statement 格式不在能力范围内INSERT的倒放变删除技术上可行但风险高——误插入可能已被下游消费倒放等于二次事故。实验一按事件时间戳截断把大事务砍成残废构造一张守恒的账户表50 个账户总额恒为 500030 笔小转账事务、一笔横跨目标时刻的批量作业先扣 6 家、再入账 6 家事件时间戳分居两侧、315 秒一次DROP TABLE。对比按事件截断与按提交边界截断两种重放。rows{i:[100,u%d%i]foriinrange(50)}# 全量备份恢复出的账户表, 守恒总额 5000txns[]forkinrange(30):# 常规转账小事务a,b(7*k)%50,(7*k13)%50txns.append((10*k1,10*k9,[(10*k9,a,-1),(10*k9,b,1)]))batch[(3022*j,(3*j)%50,-1)forjinrange(6)]# 批量作业: 先扣 6 家batch[(3162*j,(3*j7)%50,1)forjinrange(6)]# 再入账 6 家txns.append((301,326,batch))txns.append((315,315,DROP_TABLE))# DDL 不走事务, 315s 直接落盘forkinrange(33,40):a,b(7*k)%50,(7*k13)%50txns.append((10*k1,10*k9,[(10*k9,a,-2),(10*k9,b,2)]))T_BAD315# DROP TABLE 发生的时刻targetT_BAD-1definvariant(state):returnsum(v[0]forvinstate.values())naive{i:list(v)fori,vinrows.items()}forbegin,commit,eventsintxns:ifeventsDROP_TABLE:continueforts,pk,deltainevents:iftstarget:naive[pk][0]deltaprint(按事件截断: 恢复后总额 %d (基线 %d) - 事务被砍断, 只回放了一半%(invariant(naive),invariant(rows)))good{i:list(v)fori,vinrows.items()}applied0forbegin,commit,eventsintxns:ifeventsDROP_TABLEorcommittarget:continueforts,pk,deltainevents:good[pk][0]delta applied1print(按提交边界截断: 整体重放 %d 笔事务, 总额 %d, 守恒成立: %s%(applied,invariant(good),invariant(good)invariant(rows)))last_okmax(cforb,c,eintxnsife!DROP_TABLEandctarget)print(等效命令: mysqlbinlog --stop-datetime2026-09-06 %02d:%02d 落点最后一笔提交 ts%d%(last_ok//3600,last_ok//60%60,last_ok))print(时间戳非严格单调(组提交可乱序), 目标时刻前后事务边界必须人工复核, 宁可停在更早的提交点)运行输出按事件截断: 恢复后总额 4994 (基线 5000) - 事务被砍断, 只回放了一半 按提交边界截断: 整体重放 30 笔事务, 总额 5000, 守恒成立: True 等效命令: mysqlbinlog --stop-datetime2026-09-06 00:04 落点最后一笔提交 ts299 时间戳非严格单调(组提交可乱序), 目标时刻前后事务边界必须人工复核, 宁可停在更早的提交点批量作业那 12 个事件时间戳横跨 315 秒按事件截断只吃到扣款半边账面凭空蒸发 6 元且没有任何报错——这正是 MySQL 官方手册把 PITR 归到最需要演练的流程的原因。真实mysqlbinlog按提交边界截断所以方向是安全的但组提交让事件时间戳可能倒挂后提交的事务带着更早的时间戳按--stop-datetime挑时刻时目标点前后 1~2 秒的事务要打开 binlog 逐一看过宁可早停一笔、也不要晚停半秒。实验二行镜像闪回——给误操作倒带误改 10 行价格、误删 3 行订单。用 binlog 的行事件before/after 图像在逆序上生成反向 SQL在受灾表上执行与灾前快照逐行核对。orders{i:{item:sku%02d%i,qty:1i%5}foriinrange(1,21)}snapshot{k:dict(v)fork,vinorders.items()}# 灾前状态(等价 binlog 中的 before 图像)events[]# 灾难现场: 坏发布批量改价 误删foriinrange(1,11):afterdict(orders[i]);after[qty]999events.append((150,UPDATE,i,dict(orders[i]),after))orders[i]afterforiin(12,15,18):events.append((160,DELETE,i,dict(orders[i]),None))delorders[i]print(灾后账面: %d 行, sum(qty)%d (灾前 %d 行, sum%d)%(len(orders),sum(v[qty]forvinorders.values()),len(snapshot),sum(v[qty]forvinsnapshot.values())))undo_sql,undo_ops[],[]forts,kind,pk,bimg,aimginreversed(events):# 倒序回放是闪回正确性的根ifkindUPDATE:undo_sql.append(UPDATE orders SET item%s, qty%d WHERE id%d; -- 反做 ts%d%(bimg[item],bimg[qty],pk,ts))undo_ops.append((upd,pk,bimg))elifkindDELETE:undo_sql.append(INSERT INTO orders (id, item, qty) VALUES (%d, %s, %d); -- 反做 ts%d%(pk,bimg[item],bimg[qty],ts))undo_ops.append((ins,pk,bimg))print(\n生成 undo 语句 %d 条(前 4 条):%len(undo_sql))forlineinundo_sql[:4]:print( line)forkind,pk,imginundo_ops:# 在受灾库上执行闪回包ifkindupd:orders[pk]dict(img)else:orders[pk]dict(img)restoredorderssnapshotprint(\n闪回后校验: 与灾前快照逐行一致 %s, 行数 %d, sum(qty)%d%(restored,len(orders),sum(v[qty]forvinorders.values())))print(可闪回: UPDATE/DELETE(before 图像齐全); 不可闪回: DDL、statement 格式、binlog_row_imagemin 下的部分列)运行输出灾后账面: 17 行, sum(qty)10012 (灾前 20 行, sum60) 生成 undo 语句 13 条(前 4 条): INSERT INTO orders (id, item, qty) VALUES (18, sku18, 4); -- 反做 ts160 INSERT INTO orders (id, item, qty) VALUES (15, sku15, 1); -- 反做 ts160 INSERT INTO orders (id, item, qty) VALUES (12, sku12, 3); -- 反做 ts160 UPDATE orders SET itemsku10, qty1 WHERE id10; -- 反做 ts150 闪回后校验: 与灾前快照逐行一致 True, 行数 20, sum(qty)60 可闪回: UPDATE/DELETE(before 图像齐全); 不可闪回: DDL、statement 格式、binlog_row_imagemin 下的部分列三个细节决定闪回能不能进生产预案其一逆序不是可选项——同一个主键先被改 A 再被改 B正序反做会把最早的旧值盖掉最后的其二undo 包执行时也要按恢复流程对待先在影子表/临时实例跑通、核对一致再上生产且sql_log_bin0否则闪回本身又生成一笔新 binlog 传播给全部从库其三闪回只修这次误操作不修这之后的合法更新——误删后业务又新建了数据undo 包里的 UPDATE 会覆盖新值所以工具普遍要求圈定精确的时间窗与主键范围。生产工具化走binlog2sql/MyFlash这条路即可原理就是上面这 30 行。恢复预案清单binlog_formatROWbinlog_row_imageFULL闪回与第 8 篇 gh-ost 的共同前提新建实例默认锁死。binlog 保留时长 ≥ 事故发现定位最坏用时误删场景里决定生死的是 10 分钟前的日志还在不在。每季度抽一个真实事故剧本做 PITR 实操从接到误删报告计时到恢复数据可验证目标写进演练报告。回灌通道预演CREATE TABLE ... LIKE 影子表改名第 8 篇细讲、分区交换、INSERT ... SELECT分批三选一按表大小定。恢复全程留档T_bad 证据、binlog 文件与位点、重放命令原文、校验对账结果——事后复盘与审计都靠它。PITR 是事后救火但有一类误删其实是计划内的亿级历史表的过期数据清理。直接DELETE就是人造事故——下一篇《数据库高可用与容灾实战8亿级大表清理与归档pt-osc 与 gh-ost 原理实战》讲怎么把大表手术做得既不留复制黑洞、又不锁业务。参考来源MySQL 8.0 Reference ManualPoint-in-Time (Incremental) Recoveryhttps://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.htmlMySQL 8.0 Reference Manualmysqlbinlog — Utility for Processing Binary Log Fileshttps://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.htmlGitHubdanfengcao/binlog2sqlhttps://github.com/danfengcao/binlog2sqlGitHubMeituan-Dianping/MyFlashhttps://github.com/Meituan-Dianping/MyFlashMySQL 8.0 Reference ManualBinary Loghttps://dev.mysql.com/doc/refman/8.0/en/binary-log.html
数据库高可用与容灾实战（7）：PITR 时间点恢复：把误删的数据找回来
一次误删的数学题上一篇把备份的三种形态和时间账立好了全量备份给你一个过去的完整账面binlog 归档给你账面之间所有变化。PITRPoint-in-Time Recovery就是把两者接起来的函数恢复(T) 全量(T0) 重放 binlog(T0→T)其中 T 选在灾难发生前一刻。听起来一行公式工程上魔鬼藏在两处截断点的原子性——binlog 是按事件组织的、事务是按边界提交的按时间一刀切下去很容易把事务砍成两半可闪回性——行格式日志带前后镜像理论上能把误改误删倒放回去但前提条件相当苛刻。这一篇用一个账户表把这两个魔鬼都模拟出来。机制从全量到目标时刻之间发生了什么标准 PITR 流程六步止血误操作还在扩大就先把应用写流量摘掉确认事故只污染了目标表则原库保持运行、另起恢复流程→定时刻用审计 SQL、变更工单、SHOW PROCESSLIST历史或 binlog 时间戳锁定 T_bad→临时实例恢复全量永远不要在原库上直接回滚原库是人质的持有者误删后的增量数据还在它身上→重放 binlog 到 T_bad−εmysqlbinlog --start-position/--stop-position或--start-datetime/--stop-datetimeGTID 环境用--exclude-gtids精准剔除坏事务更快→校验守恒对账、抽样比对、业务只读验证→回灌把恢复实例里的目标表导回生产全程SET sql_log_bin0防止回灌本身被复制污染再恢复应用写流量。重放阶段的语义必须掰开binlog 里的时间戳属于事件而是否该被重放属于事务。一个事务的语句可能持续几十秒大 UPDATE、批处理它的开始事件、行事件、XID 提交事件各带各的时间戳--stop-datetime的截断是以事务为原子的——落在截断点内开始的完整事务会被重放到它的 XID 为止没等到 XID 的事务整体丢弃。理解这一条就能理解为什么停在半路的恢复比没恢复更糟半截的大事务比数据缺失更难向业务解释。另一个实用分支是闪回flashback不恢复整库只把误操作那几分钟倒带。它的原料是行事件的 before/after 镜像——DELETE倒放成INSERT、UPDATE倒放成用 before 图像的反向更新、并且要按事件逆序应用。前提条件三条binlog_formatROW、binlog_row_imageFULL第 1 篇就埋的伏笔、binlog 还在binlog_expire_logs_seconds未冲掉。DDL 和 statement 格式不在能力范围内INSERT的倒放变删除技术上可行但风险高——误插入可能已被下游消费倒放等于二次事故。实验一按事件时间戳截断把大事务砍成残废构造一张守恒的账户表50 个账户总额恒为 500030 笔小转账事务、一笔横跨目标时刻的批量作业先扣 6 家、再入账 6 家事件时间戳分居两侧、315 秒一次DROP TABLE。对比按事件截断与按提交边界截断两种重放。rows{i:[100,u%d%i]foriinrange(50)}# 全量备份恢复出的账户表, 守恒总额 5000txns[]forkinrange(30):# 常规转账小事务a,b(7*k)%50,(7*k13)%50txns.append((10*k1,10*k9,[(10*k9,a,-1),(10*k9,b,1)]))batch[(3022*j,(3*j)%50,-1)forjinrange(6)]# 批量作业: 先扣 6 家batch[(3162*j,(3*j7)%50,1)forjinrange(6)]# 再入账 6 家txns.append((301,326,batch))txns.append((315,315,DROP_TABLE))# DDL 不走事务, 315s 直接落盘forkinrange(33,40):a,b(7*k)%50,(7*k13)%50txns.append((10*k1,10*k9,[(10*k9,a,-2),(10*k9,b,2)]))T_BAD315# DROP TABLE 发生的时刻targetT_BAD-1definvariant(state):returnsum(v[0]forvinstate.values())naive{i:list(v)fori,vinrows.items()}forbegin,commit,eventsintxns:ifeventsDROP_TABLE:continueforts,pk,deltainevents:iftstarget:naive[pk][0]deltaprint(按事件截断: 恢复后总额 %d (基线 %d) - 事务被砍断, 只回放了一半%(invariant(naive),invariant(rows)))good{i:list(v)fori,vinrows.items()}applied0forbegin,commit,eventsintxns:ifeventsDROP_TABLEorcommittarget:continueforts,pk,deltainevents:good[pk][0]delta applied1print(按提交边界截断: 整体重放 %d 笔事务, 总额 %d, 守恒成立: %s%(applied,invariant(good),invariant(good)invariant(rows)))last_okmax(cforb,c,eintxnsife!DROP_TABLEandctarget)print(等效命令: mysqlbinlog --stop-datetime2026-09-06 %02d:%02d 落点最后一笔提交 ts%d%(last_ok//3600,last_ok//60%60,last_ok))print(时间戳非严格单调(组提交可乱序), 目标时刻前后事务边界必须人工复核, 宁可停在更早的提交点)运行输出按事件截断: 恢复后总额 4994 (基线 5000) - 事务被砍断, 只回放了一半 按提交边界截断: 整体重放 30 笔事务, 总额 5000, 守恒成立: True 等效命令: mysqlbinlog --stop-datetime2026-09-06 00:04 落点最后一笔提交 ts299 时间戳非严格单调(组提交可乱序), 目标时刻前后事务边界必须人工复核, 宁可停在更早的提交点批量作业那 12 个事件时间戳横跨 315 秒按事件截断只吃到扣款半边账面凭空蒸发 6 元且没有任何报错——这正是 MySQL 官方手册把 PITR 归到最需要演练的流程的原因。真实mysqlbinlog按提交边界截断所以方向是安全的但组提交让事件时间戳可能倒挂后提交的事务带着更早的时间戳按--stop-datetime挑时刻时目标点前后 1~2 秒的事务要打开 binlog 逐一看过宁可早停一笔、也不要晚停半秒。实验二行镜像闪回——给误操作倒带误改 10 行价格、误删 3 行订单。用 binlog 的行事件before/after 图像在逆序上生成反向 SQL在受灾表上执行与灾前快照逐行核对。orders{i:{item:sku%02d%i,qty:1i%5}foriinrange(1,21)}snapshot{k:dict(v)fork,vinorders.items()}# 灾前状态(等价 binlog 中的 before 图像)events[]# 灾难现场: 坏发布批量改价 误删foriinrange(1,11):afterdict(orders[i]);after[qty]999events.append((150,UPDATE,i,dict(orders[i]),after))orders[i]afterforiin(12,15,18):events.append((160,DELETE,i,dict(orders[i]),None))delorders[i]print(灾后账面: %d 行, sum(qty)%d (灾前 %d 行, sum%d)%(len(orders),sum(v[qty]forvinorders.values()),len(snapshot),sum(v[qty]forvinsnapshot.values())))undo_sql,undo_ops[],[]forts,kind,pk,bimg,aimginreversed(events):# 倒序回放是闪回正确性的根ifkindUPDATE:undo_sql.append(UPDATE orders SET item%s, qty%d WHERE id%d; -- 反做 ts%d%(bimg[item],bimg[qty],pk,ts))undo_ops.append((upd,pk,bimg))elifkindDELETE:undo_sql.append(INSERT INTO orders (id, item, qty) VALUES (%d, %s, %d); -- 反做 ts%d%(pk,bimg[item],bimg[qty],ts))undo_ops.append((ins,pk,bimg))print(\n生成 undo 语句 %d 条(前 4 条):%len(undo_sql))forlineinundo_sql[:4]:print( line)forkind,pk,imginundo_ops:# 在受灾库上执行闪回包ifkindupd:orders[pk]dict(img)else:orders[pk]dict(img)restoredorderssnapshotprint(\n闪回后校验: 与灾前快照逐行一致 %s, 行数 %d, sum(qty)%d%(restored,len(orders),sum(v[qty]forvinorders.values())))print(可闪回: UPDATE/DELETE(before 图像齐全); 不可闪回: DDL、statement 格式、binlog_row_imagemin 下的部分列)运行输出灾后账面: 17 行, sum(qty)10012 (灾前 20 行, sum60) 生成 undo 语句 13 条(前 4 条): INSERT INTO orders (id, item, qty) VALUES (18, sku18, 4); -- 反做 ts160 INSERT INTO orders (id, item, qty) VALUES (15, sku15, 1); -- 反做 ts160 INSERT INTO orders (id, item, qty) VALUES (12, sku12, 3); -- 反做 ts160 UPDATE orders SET itemsku10, qty1 WHERE id10; -- 反做 ts150 闪回后校验: 与灾前快照逐行一致 True, 行数 20, sum(qty)60 可闪回: UPDATE/DELETE(before 图像齐全); 不可闪回: DDL、statement 格式、binlog_row_imagemin 下的部分列三个细节决定闪回能不能进生产预案其一逆序不是可选项——同一个主键先被改 A 再被改 B正序反做会把最早的旧值盖掉最后的其二undo 包执行时也要按恢复流程对待先在影子表/临时实例跑通、核对一致再上生产且sql_log_bin0否则闪回本身又生成一笔新 binlog 传播给全部从库其三闪回只修这次误操作不修这之后的合法更新——误删后业务又新建了数据undo 包里的 UPDATE 会覆盖新值所以工具普遍要求圈定精确的时间窗与主键范围。生产工具化走binlog2sql/MyFlash这条路即可原理就是上面这 30 行。恢复预案清单binlog_formatROWbinlog_row_imageFULL闪回与第 8 篇 gh-ost 的共同前提新建实例默认锁死。binlog 保留时长 ≥ 事故发现定位最坏用时误删场景里决定生死的是 10 分钟前的日志还在不在。每季度抽一个真实事故剧本做 PITR 实操从接到误删报告计时到恢复数据可验证目标写进演练报告。回灌通道预演CREATE TABLE ... LIKE 影子表改名第 8 篇细讲、分区交换、INSERT ... SELECT分批三选一按表大小定。恢复全程留档T_bad 证据、binlog 文件与位点、重放命令原文、校验对账结果——事后复盘与审计都靠它。PITR 是事后救火但有一类误删其实是计划内的亿级历史表的过期数据清理。直接DELETE就是人造事故——下一篇《数据库高可用与容灾实战8亿级大表清理与归档pt-osc 与 gh-ost 原理实战》讲怎么把大表手术做得既不留复制黑洞、又不锁业务。参考来源MySQL 8.0 Reference ManualPoint-in-Time (Incremental) Recoveryhttps://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.htmlMySQL 8.0 Reference Manualmysqlbinlog — Utility for Processing Binary Log Fileshttps://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.htmlGitHubdanfengcao/binlog2sqlhttps://github.com/danfengcao/binlog2sqlGitHubMeituan-Dianping/MyFlashhttps://github.com/Meituan-Dianping/MyFlashMySQL 8.0 Reference ManualBinary Loghttps://dev.mysql.com/doc/refman/8.0/en/binary-log.html
