Binlog：MySQL 主从复制和数据恢复的基础

binlog 是什么binlog 是 Server 层的归档日志记的是数据发生了哪些变更。和 redo log 摆在一起看差别最明显的是三点redo log 是循环写的空间固定写满就覆盖binlog 是追加写的写完一个文件换下一个历史都在redo log 属于 InnoDBMyISAM 根本没有binlog 属于 Server 层用什么引擎都有redo log 是物理逻辑日志绑死在页和偏移上binlog 是逻辑日志记的是语句或者行前两点决定了 binlog 能长期保留、能跨引擎第三点决定了它能跨版本、跨机器用。主从复制和按时间点恢复这两件事只有 binlog 干得了。三种格式binlog_format有三个取值这是 binlog 最需要搞清楚的地方。STATEMENT记原始的 SQL 语句。UPDATE t SET name b WHERE id 1记的就是这条 SQL 本身。好处是日志小。一条UPDATE改了 100 万行也只需要记一条语句带来的几十个字节。坑在于不确定的语句会让主从不一致UPDATEtSETcreate_timeNOW()WHEREid1;UPDATEtSETtokenUUID()WHEREid1;UPDATEtSETnn1WHEREid1LIMIT1;主库执行的时候NOW()是 10:00:00binlog 里记的是NOW()这个函数调用从库回放的时候是 10:00:05写进去的时间就不一样了。UUID()、RAND()同理LIMIT不带ORDER BY的时候取哪一行也不确定。这类语句在主从环境里是隐藏的雷等到发现数据对不上往往已经过了很久。ROW不记 SQL记每一行改前和改后的值。UPDATE t SET name b WHERE id 1在 ROW 格式下会变成两个事件Table_map_event 记录涉及的表结构表 id、列数、每列的类型 Update_rows_event 记录 id 1 这一行name 从 a 变成 bTable_map_event是给从库看的因为从库要按列的位置来解析行数据得先知道表长什么样。好处是结果确定。不管 SQL 里写了NOW()还是UUID()binlog 里存的是最终写进去的那个值从库照着写就行不可能不一致。代价是日志大。一条UPDATE改 100 万行就是 100 万行的前后值日志体积可能比 STATEMENT 大几十倍。所以 ROW 格式下max_binlog_size会经常触发文件切换磁盘占用也涨得快。MySQL 5.7.7 之后默认就是 ROW 格式这也是现在的主流选择。ROW 格式还有个参数binlog_row_image控制记多少列full默认记所有列的前后值minimal只记被改的列加上主键noblob是full的变体BLOB 和 TEXT 列只在真的被改时才记。写多读少的场景调成minimal能省不少空间。MIXEDMySQL 自己判断默认用 STATEMENT碰到不确定的函数就切到 ROW。听起来两全其美实际用的人不多。因为哪些语句算不确定由 MySQL 判断判断的边界不透明出了问题不好排查不如直接上 ROW 求个确定。怎么选格式日志体积主从一致性说明STATEMENT小有隐患除非有明确的体积压力不建议ROW大确定现在的主流5.7.7 之后是默认值MIXED中依赖判断判断边界不透明用得少一句话用磁盘空间换确定性选 ROW。写盘的时机binlog 的写入路径比 redo log 简单一层事务执行 → binlog cache线程私有→ binlog 文件 → 磁盘注意binlog cache是每个线程一份的不像 redo log buffer 是全局共享的。原因是一个事务的 binlog 必须连续完整地放在一起如果所有线程共用一个缓冲区两个事务的 binlog 会交织在一起从库没法按事务回放。binlog_cache_size控制这个缓冲区大小不够用的时候会溢写到磁盘上的临时文件事务结束后删掉。提交的时候分两步把binlog cache里的内容写进 binlog 文件这一步是write还没落盘按sync_binlog决定要不要fsync0不主动 fsync交给操作系统决定机器掉电会丢1每次提交都 fsync默认值N累积 N 个事务后 fsync 一次sync_binlog 0和innodb_flush_log_at_trx_commit 2有点像都是数据交给 OS 了但没落盘进程崩溃不丢、机器掉电会丢。线上最严的配置是sync_binlog 1加上innodb_flush_log_at_trx_commit 1俗称双 1含义是提交返回了就一定不会丢。还有一条规则一个事务的 binlog 不能被拆到两个文件里。binlog 文件按max_binlog_size默认 1GB切换但如果切换的时候有事务正在写会等这个事务写完再切。所以实际的文件大小可能略超过 1GB。主从复制靠它复制的整个机制就是把主库的 binlog 搬到从库执行一遍中间靠三个线程接力主库 从库 ┌─────────────┐ ┌──────────────────┐ │ binlog │ │ relay log │ └─────────────┘ └──────────────────┘ ↑ ↑ ↓ dump 线程 ──── 推 binlog ────→ IO 线程 SQL 线程 ↓ 执行到从库从库的 IO 线程连上主库告诉主库我要从某个 binlog 文件的某个位置开始主库的 dump 线程从那个位置开始读 binlog推给从库从库的 IO 线程收到后写进本地的 relay log从库的 SQL 线程读 relay log在从库上把事件重放一遍为什么中间要落一份 relay log不直接从 IO 线程交给 SQL 线程为了让两个线程解耦。SQL 线程回放慢的时候不会卡住 IO 线程继续拉取主库挂了从库手里也有存货能继续回放。另外 SQL 线程可以并行IO 线程只能串行拉分开才好做并行复制。数据恢复靠它有了全量备份加上 binlog就能恢复到任意一个时间点这个叫PITR( Point-In-Time Recovery )。流程是两步先把最近一次全量备份恢复回去再把备份之后到目标时间点之间的 binlog 重放一遍。mysqlbinlog\--start-datetime2026-09-23 10:00:00\--stop-datetime2026-09-23 10:30:00\--databasetest\mysql-bin.000001|mysql-uroot-p举例凌晨 2 点做了全量备份上午 10 点有人误删了一张表想恢复到 10 点之前。那就恢复备份然后把凌晨 2 点到 9 点 59 分之间的 binlog 重放一遍。这也是为什么误删数据之后第一件事不是着急操作而是先把 binlog 保护起来因为它是唯一能找回数据的凭据。--start-datetime和--stop-datetime可以换成--start-position和--stop-position用精确的位点比时间更准。时间点恢复的时候要找误操作之前的那个位点通常用--stop-position卡住。ROW 格式的 binlog 直接看是二进制要用mysqlbinlog -vv解码。-v会把行变更转成伪 SQL-vv还会带上列的类型注释排查问题的时候基本都用-vv。为什么有了 redo log 还要 binlog这个问题在 Redo LogMySQL 为什么能够实现崩溃恢复那篇里提过一半这里补全。两个日志谁也替代不了谁有四个原因redo log 是循环写的空间固定写满就覆盖。它只够支撑崩溃之后把数据补回来撑不起保留过去一个月所有变更redo log 属于 InnoDBMyISAM 之类的引擎没有。复制和恢复是 Server 层的需求不能绑在某一个引擎上redo log 是物理逻辑日志绑死页结构和偏移。换个 MySQL 大版本、换个平台页的格式可能就变了日志没法通用从库和主库不一定完全一样页大小、数据文件布局都可能不同物理日志搬过去对不上binlog 是逻辑的、归档的、Server 层的这三条才是复制和恢复需要的性质。