MySQL 事务、日志与恢复迁移：崩溃、复制、PITR 与在线 DDL 的真实观察
本页固定 MySQL Community 8.4.7，在独有 mysqld 实例（临时 datadir、随机端口，结束即删）上运行四组实验：崩溃恢复、异步复制延迟、全量备份 + PITR、在线 DDL 代价。事务/MVCC/隔离级别/锁/死锁的机制与双会话实验在 MVCC、隔离级别与锁，慢查询与迁移协议在 慢 SQL、死锁、深分页与大表治理——本页不重写它们，只补“崩溃/复制/恢复/DDL 的真实运行证据”。
入口：python3 labs/mysql-engine/run.py u52，4 组检查、5 条观察行、exit 0，证据在 evidence/u52-2026-10-28.json。实例配置：innodb_redo_log_capacity=100MiB、binlog_format=ROW、innodb_flush_log_at_trx_commit=1、sync_binlog=1。
一、既有事务/锁内容的复核登记
在补新实验之前，先按票面要求登记既有内容的复核状态，避免用“旧完成标记”代替当前审读：
| 既有知识点 | 位置 | 复核状态 | 证据基线 |
|---|---|---|---|
ReadView 可见性（changes_visible、prepare） | MVCC 页，两段原码 | 已讲清，原码与标签一致 | mysql-8.0.39 源码 + 本机 8.4.7 双会话 |
| RR/RC 双会话四种读 | MVCC 页 + labs/mysql/sessions.sql | 已运行（T16，8.4.7） | output/t16-local-tx.json |
| 唯一未命中 15 的间隙锁 | MVCC 页 + observe.sql | 已运行（8.4.7：B 请求 X,GAP,INSERT_INTENTION 被 X,GAP 阻塞） | T16 证据 |
| 写偏差与 guard 协议 | MVCC 页 + 双会话 | 已运行（无 guard 最终 0 在岗；guard 后 1 在岗） | T16 证据 |
| 死锁 1213 与 1205 区别 | 诊断页 + 原码 | 已运行（本次 B 收到 1213，A 继续） | T16 证据 |
| 1205 的服务器侧触发 | 诊断页 | 未运行（本票 DDL 实验的 MDL 等待复现了 1205，但那是 MDL 而非行锁等待超时） | 本页第五节 |
结论：既有事务/MVCC/锁内容不需要重写；本页的增量是日志与恢复侧的四组真实实验。1205 在行锁等待下的行为仍由 MVCC 页的协议讲解承担，本页的 1205 是 MDL 场景，不能混为一谈。
二、崩溃恢复：kill -9 之后哪些事实还在
innodb_flush_log_at_trx_commit=1 与 sync_binlog=1 下，一个已提交事务与一个未提交事务，在 mysqld 被 kill -9 后重启：
U52 crashRecovery=PASS committedKept=true uncommittedRolledBack=true recoveryLog=2三层事实必须分开：
- 已提交事务保留：
committed-ok行仍在——redo 里已有该事务的提交记录，重启时 InnoDB 前滚应用。 - 未提交事务回滚：
uncommitted行消失——重启时 InnoDB 对未提交事务做 undo 回滚。 - 恢复日志：错误日志出现
Starting XA crash recovery.../XA crash recovery finished.——这是本机 8.4.7 的实际输出，不是“典型日志”。
要注意边界：kill -9 杀的是进程，操作系统页缓存与磁盘仍然完好；真正的断电会额外引入“页缓存里的数据还没落盘”的窗口，innodb_flush_log_at_trx_commit、存储设备写缓存、文件系统行为都会影响结果。本实验证明的是进程崩溃恢复，不是断电恢复。
另一个常见误读：redo 恢复的粒度是页，不是重放 SQL。日志中的 LSN、checkpoint 与页刷写策略决定恢复要做多少工作；本实验的数据量小到恢复在秒级完成，不能外推生产恢复时长。
三、复制延迟：SQL 线程停止时 Seconds_Behind_Source 会变成 NULL
同机两个独有实例（server-id 51/52），GTID + SOURCE_AUTO_POSITION=1 的异步复制：
U52 replicationLag=PASS sqlThreadStopped=Yes replicaRowsWhileStopped=1 caughtUpSecondsBehind=0 rows=4时序：主库写入 1 行 → 副本追平（Seconds_Behind_Source=0）→ STOP REPLICA SQL_THREAD → 主库再写 3 行 → 此时副本只有 1 行、Replica_SQL_Running=No → START REPLICA SQL_THREAD → 追平到 4 行、Seconds_Behind_Source=0。
关键观察：SQL 线程停止时 Seconds_Behind_Source 显示 NULL，不是“0”也不是“无限大”。很多监控把 NULL 当 0 处理，于是“复制已停止”被误报成“延迟为零”。正确的告警要同时看 Replica_IO_Running/Replica_SQL_Running（必须是 Yes）与 Seconds_Behind_Source（NULL 视为不可报告），并以副本行数/GTID 位点作为业务一致性证据——本实验用 replicaRowsWhileStopped=1 与追平后的 rows=4 直接证明。
Exec_Source_Log_Pos 与 Read_Source_Log_Pos 的差值反映 relay log 中尚未应用的量：停止 SQL 线程期间前者不动、后者前进（本实验 885 vs 1197），恢复后两者相等（1197/1197）。GTID 的 Auto_Position=1 表示按 GTID 自动定位，不依赖 file+position；生产提升副本时必须确认新主已应用全部 GTID，而不是只看位点数字。
本实验是同机异步复制：没有网络延迟、没有半同步确认、没有组复制选主。“延迟”是人为暂停 SQL 线程制造的，不是网络或负载造成的。跨机房 RTT、半同步等待、并行复制线程数、大事务都会改变延迟形态，这些未运行。
四、备份恢复：全量 + binlog 位点恢复（PITR）
mysqldump --single-transaction --flush-logs --source-data=2 做全量备份，备份后写入 d，然后误删 b。用 dump 恢复到一个新实例，再应用 binlog 到删除前的位点：
U52 backupPitr=PASS pitrRows=a,b,c,d fullReplay=a,c,d对照实验很关键：同一份 dump 在“完整应用 binlog（含 DELETE）”后得到 a,c,d——恢复点选错，b 就真的丢了。PITR 的本质是“全量 + 位点”，位点的语义必须精确：
mysqldump --source-data=2把当时的 binlog 文件与位点写进 dump 头（注释形式）；恢复时从该位点开始应用增量。--stop-position必须落在误操作之前的事件边界上；落在事件中间会得到部分应用的事务（8.4 的 binlog 是事务边界完整的，但跨文件的位点要分别指定）。--skip-gtids用于把 binlog 重放到新实例而不冲突；生产上更常见的是基于 GTID 的恢复与gtid_purged管理。
--single-transaction 用一致性快照做 InnoDB 表的备份，不锁表；但 MyISAM 表不适用。备份期间的长事务会保留 undo，影响 purge——这与 MVCC 页的“两小时导出”是同一个代价。
本实验没有覆盖：物理备份（XtraBackup）、增量备份、binlog 压缩、备份校验、跨版本恢复。“备份成功”不等于“可恢复”——本实验的价值恰恰在于真的恢复了一次并对照了位点。
五、在线 DDL：三种算法的实际代价与拒绝条件
2 万行表上的真实耗时与错误码：
U52 onlineDdl=PASS instantMs=14 inplaceMs=26 copyMs=57 inplaceRefused=1846 mdlWait=1205 rows=20000| 操作 | 算法 | 结果 |
|---|---|---|
加列 flag TINYINT NOT NULL DEFAULT 0 | INSTANT | 成功，14ms，行数据未重写 |
加二级索引 idx_v | INPLACE, LOCK=NONE | 成功，26ms，允许并发 DML |
改列宽 VARCHAR(32)→VARCHAR(128) | INPLACE | 拒绝，1846（Cannot change column type INPLACE） |
| 同上，改用 COPY, LOCK=SHARED | COPY | 成功，57ms，整表重建 |
| 加 FULLTEXT 索引 + INSTANT | INSTANT | 拒绝，1846（Fulltext index creation requires a lock） |
三个结论：
- “在线 DDL”不是一个算法的名字，而是三种代价档位。INSTANT 只改元数据（14ms 与行数无关）；INPLACE 允许并发 DML 但要重建索引/表（26ms，随数据量增长）；COPY 要重建整表并持锁（57ms，2 万行；生产上 5 亿行就是小时级）。
- 显式写
ALGORITHM=让不支持的条件直接失败，而不是静默退化。本实验两次 1846 都是“显式要求了不支持的算法”——这正是安全实践：先失败，再选择降级路径，而不是让 DDL 在高峰期变成意外长事务。 - MDL 等待会以 1205 出现：一个未提交事务持有表元数据锁时，DDL 等待 2 秒后报
ERROR 1205（mdlWaitMs=2042，与lock_wait_timeout=2吻合）。这里的 1205 是元数据锁等待超时，不是行锁等待超时——两者都报 1205，但原因与处理完全不同。排障时要看metadata_locks（MDL）还是data_lock_waits（行锁），诊断页的区分在这里再次得到验证。
DDL 后的数据完整性：20000 行、flag 全为 0（默认值）、v=VARCHAR(128) 后原值保留（v12345）。结构变更成功不等于数据正确——本实验至少验证了行数与默认值。
六、证明边界：独有实例能证明什么
| 类别 | 本页有什么 | 不能推出什么 |
|---|---|---|
| 真实组件 | 独有 mysqld 8.4.7（真实崩溃/复制/备份/DDL） | 生产配置、跨机房、组复制 |
| 崩溃注入 | kill -9 进程 | 断电（页缓存丢失）、磁盘故障、文件系统损坏 |
| 复制 | 同机异步复制 + 人为暂停 SQL 线程 | 网络延迟、半同步、组复制选主、并行复制调优 |
| 备份 | mysqldump + mysqlbinlog PITR | 物理备份、增量备份、跨版本恢复、备份校验 |
| DDL | 2 万行的三种算法与两个错误码 | 5 亿行生产耗时、全部 DDL 类型、复制 DDL 传播 |
| 未运行 | 8.0.39 镜像、组复制/半同步/选主、XtraBackup、跨机房、生产负载 | 不能标为已通过 |
单次计时不是基准；2 万行的 14/26/57ms 不能外推 5 亿行。教材完成不等于学习者掌握：下面的题目检查改变条件后的裁决。
闭卷验收
本页 12 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。