从事MySQL运维和架构设计的人迟早要跟日志打交道。很多人被问到MySQL里有哪些日志时第一反应是binlog再想想可能还有error log。但真正决定事务能否“不丢、不重、不乱”的是binlog、redo log、undo log这三兄弟。我之前排查过几次夜间数据异常最后都回到日志链路本身谁在什么时候写了什么日志谁在崩溃后根据日志做了哪些操作。这篇文章就把这三种日志的分工、写日志顺序、事务配合、参数配置和常见坑一起梳理清楚适合正在理解MySQL事务原理的开发者也适合被线上日志问题折磨的运维同学参考。1. 三种日志的分工谁在记账谁在备份谁在后悔1.1 BinlogMySQL Server层的“流水账本”Binlog是MySQL Server层生成的逻辑日志记录的是“发生了什么变更”某条语句把某行数据从旧值改成了新值或者某个表结构被修改了。它跟具体存储引擎无关MyISAM、InnoDB都可以用所以承担了两个非常重要的任务主从复制和基于时间点的数据恢复。我在实际排查问题时习惯把binlog理解成记账本。它的特点是可以完整回放只要能从某个全量备份往后重放binlog就能把数据库恢复到任意一个历史时刻。记账本记的是逻辑操作所以记录的是SQL描述或行变更前后镜像而不是磁盘页上具体字节的变化。binlog有三种格式。statement格式记录原始SQL日志量小但遇到NOW()、UUID()这类不确定函数时主从执行结果可能不一致row格式记录每行变更前、变更后的镜像最安全但日志量明显变大mixed格式由MySQL判断可能混用在某些场景下仍存在不确定性。除非有非常明确的理由否则我建议线上用row格式虽然日志会膨胀但换来了可预测的复制结果和更可靠的数据恢复能力。确认binlog是否开启时检查log_bin参数实际文件通常在datadir下的binlog.*文件中也可以单独指定目录存放。1.2 Redo LogInnoDB引擎的“损坏保险单”Redo log是InnoDB存储引擎层的物理日志记录的是“某个数据页在某个偏移量发生了什么修改”比如页号1000、偏移量28处写入了某个值。它的作用不是给人看的而是给崩溃恢复用的提交成功的变更即使数据页还没来得及刷到磁盘只要redo log里留下了记录实例重启后就可以重新应用修改保证事务不丢。这里必须理解WALWrite-Ahead Logging的核心思想。把随机写入数据页的操作转换成顺序追加写日志文件先把这次变更加在日志上等一个合适的时机再去更新数据页。顺序写比随机写快得多这是InnoDB选择WAL而不是每次直接刷数据页的原因。redo log是固定大小循环写的可以理解成一块环形缓冲写满后覆盖最旧的、已经保证落盘的部分所以它不是一个无限保留的归档而是“最近一段时间所有变更的收据”。早期版本里redo log是ib_logfile0、ib_logfile1等文件现代MySQL 8.0.30之后改为#innodb_redo目录下动态管理文件但本质没变。如果你发现实例参数中innodb_log_file_size和innodb_log_files_in_group仍然存在那是旧版参数新版本更推荐直接关注innodb_redo_log_capacity。1.3 Undo Log事务回滚和MVCC的“后悔药”Undo log也是InnoDB引擎层的日志但它记录的是“修改之前的数据版本”。事务执行中如果需要回滚InnoDB就根据undo log里记录的旧值把数据页恢复到事务开始前的样子。同时多版本并发控制MVCC也依赖undo log在一个事务执行过程中另一个事务做普通读时看不到尚未提交的新改动只能通过undo log回溯到旧版本。所以说undo log不是“日志坏了才用”它本身是事务并发能力的地基。它跟redo log有一个非常明确的分工redo log保证已提交事务不丢undo log保证未提交事务或被回滚的事务能撤得干净。崩溃恢复时InnoDB会利用redo log重放所有已提交的修改对于崩溃前尚未提交的事务则利用undo log把数据页回滚到之前的状态。正因如此长事务会导致undo log不断累积因为这些旧版本还需要被其他正在运行的事务读取不能立刻清理。2. 日志写入顺序先写日志再写数据还要玩两阶段提交2.1 WAL原理为什么顺序写日志比随机写数据页快我经常用一个很生活化的比喻解释WAL。你去银行办存款柜员不会立刻把账本所有页都重新写一遍而是先在流水单上记一笔“某时刻存入多少钱”然后等夜深人静再统一把总账更新。流水单写入是顺序追加总账更新则可能涉及多个页的随机读写。如果每次操作都立刻把账本更新好性能会差很多而且万一写一半断电账目就乱了。MySQL的redo log就是这个流水单它记录的是即将发生或已经发生的所有物理变更。在InnoDB中数据变更先进入缓存Buffer Pool中的内存页同时生成redo log并写入redo log buffer。事务提交时是否需要把redo log buffer强制刷到磁盘由参数innodb_flush_log_at_trx_commit决定。如果为1每次事务提交都把redo log刷到磁盘性能有损耗但最安全如果为2每次提交只写入操作系统缓存由操作系统稍后落盘性能好但主机断电时会丢最近一小段时间的已提交事务如果为0则是每秒定时刷盘性能最好但MySQL进程崩溃也可能丢最多一秒的事务。线上追求不丢数据时必须把redo log参数和sync_binlog1一起配好。2.2 两阶段提交流程binlog和redo log如何保证一致这是MySQL事务提交里最容易绕晕的部分也是架构面试里最常考的点。为什么需要两阶段提交因为binlog属于Server层redo log属于InnoDB引擎层它们各自维护自己的轨迹如果不做协调就会产生一个可怕的问题某个事务在存储引擎里已经提交了但binlog没写或者写了一半主库重启后引擎数据是新的但从库还在等binlog同步最后两边数据不一致。InnoDB使用两阶段提交Two-Phase Commit解决这个问题。流程大致这样事务执行过程中InnoDB不断写入redo log先不提交。等到事务需要提交时第一步把事务标记为prepare状态并刷redo log第二步Server层把本次事务的binlog写入并刷盘第三步InnoDB把事务标记为commit状态同时把binlog的文件名和写入位置记录到redo log中然后返回成功。为什么要把binlog的位置记进redo log因为崩溃恢复时InnoDB看到了prepare状态的事务需要通过binlog里是否存在对应记录来判断是否需要提交。如果binlog里有这个事务且完整说明Server层已经确认提交InnoDB就应该把这个事务正式提交掉。如果binlog里没有说明事务根本没走到提交这一步应当回滚。这套规则保证了binlog和实际数据永远能对上主从复制才可靠。2.3 崩溃恢复的依据prepare和commit怎么判断实战中如果看到redo log里有一堆prepare状态但不清楚事务是否提交可以这样理清判断逻辑先看对应binlog找到这个事务最后的位置如果事件链完整包括事务边界事件就执行commit如果binlog里没有匹配记录就执行rollback。这就是为什么binlog格式为row时更容易解析statement格式要判断某个事务边界相对复杂但内部机制仍能保证。为了减少磁盘I/OMySQL会把多个事务的提交动作合并成一次fsync这就是组提交group commit。你可以粗略理解为一个班的学生排队交作业老师把所有作业收齐后统一归档比一个学生交一次立刻归档更高效。组提交让高并发下的提交性能提高不少但原理上仍为每个事务维护两阶段日志状态。3. 一条UPDATE语句的日志流水账3.1 从begin到commit日志都经历了什么拿一个具体例子说话UPDATE users SET age30 WHERE id100假设这一行原来的age是20。事务开始后InnoDB不会立刻改数据页而是先做一系列日志动作在undo log中记录旧值即“id100这一行age原来是20”并把这条undo记录加入该事务的回滚段。在Buffer Pool中更新内存里的数据页把age改为30同时标记该页为脏页。生成一条redo log记录“页X上id100这行age字段从20写成30”状态为prepare并写入redo log buffer。如果客户端执行commitServer层先生成该事务的binlog事件写入binlog缓存最终刷盘。binlog刷盘成功后InnoDB再把这个事务标记为commitredo log也刷盘到位事务才算真正提交。这个顺序很关键commit返回给客户端之前binlog和redo log都已经是持久化状态也就是“日志先落盘数据页后落盘”。所以哪怕下一秒操作系统断电重启后InnoDB也能从redo log里把这次修改恢复回来而从库也能从binlog里拿到该事务同步执行。3.2 回滚、脏页刷盘与checkpoint如何推进写完日志后数据页并不是马上落盘。脏页由后台线程按策略异步刷盘刷盘时机受很多因素影响比如脏页比例、空闲内存、系统IO能力等。为了保证redo log循环写时不会覆盖掉还没落盘的修改InnoDB维护了checkpoint机制。checkpoint的位置表示“这个点之前的脏页已经刷盘redo log里对应的修改已经可以覆盖”。redo log中write_pos往前写checkpoint_pos往前追两者之间就是当前还在使用的日志区域。如果业务写入量很大而后台刷脏页跟不上write_pos就会逼近checkpoint最终出现redo log没有空间可写的状态。此时InnoDB会强制加速刷脏页整个系统更新操作就会明显变慢。理解了这点再去看监控里的“redo log空间等待”告警就不会只想着加日志文件而是同时检查刷盘能力是不是瓶颈。未提交事务如果发生回滚InnoDB顺着undo log链往回找旧版本然后把数据页上的值还原。注意回滚也有日志同样会写redo log覆盖旧数据的状态所以回滚本身依然需要WAL保护避免回滚到一半又崩溃。3.3 主从复制时binlog如何被二次消费主库在每次事务提交后binlog里会出现一条或多条事件记录。从库用一个专用线程连接到主库从指定binlog文件和偏移量拉取日志写入到自己的relay log。接着从库的SQL线程读取relay log中的事件在本机执行。整个过程就是把逻辑日志在另一台实例上重放一遍。主从复制最怕的是binlog事件到了从库但执行结果不确定。这也是我坚持用row格式的原因。row格式记录的是变更前后的行镜像从库直接应用镜像即可不会因为SQL语句使用了函数、随机数或非确定性表达式而产生偏差。如果你看到主从延迟或数据不一致优先检查binlog_format和binlog_row_image这些参数是否符合预期。另外binlog文件是有边界的每个binlog文件写满后自动切换到下一个max_binlog_size决定了切换阈值。从库拉取时如果主库把binlog提前清理了就会报“got fatal error 1236”这时候只能考虑重建从库或从备份补数据。4. 三种日志的配置与运维实战4.1 关键参数怎么设从保证不丢到追求性能配置日志参数前先问自己这个业务能不能接受事务提交成功后丢失已提交数据。如果答案是不能那binlog侧必须保证sync_binlog1redo log侧必须保证innodb_flush_log_at_trx_commit1。这两个参数同时为1才能做到事务提交后binlog和redo log都已经落盘。只开一个不行比如redo刷盘但binlog没刷从库复制可能延迟或丢失binlog刷了但redo没刷主库崩溃可能丢提交。追求性能时可以牺牲一些安全性比如把redo log的刷盘参数改成2再配合sync_binlog的批量提交策略。但改之前要想清楚如果机房断电最近约1秒的已提交事务可能全部丢失主从也可能从此不一致。很多团队在测试环境用0生产环境用1个人建议除非有专门的硬件或异地容灾兜底否则生产环境老老实实用1。binlog还要关注保存时长。老版本参数是expire_logs_days新版本推荐binlog_expire_logs_seconds单位是秒。建议保留时间至少要覆盖一次完整备份周期加上容错时间比如每天全备就保留至少48小时如果要做基于时间点恢复最好保留到备份任务确认上传成功之后。redo log容量方面8.0.30以前的版本要同时看innodb_log_file_size和innodb_log_files_in_group两者相乘是总日志容量。8.0.30以后直接用innodb_redo_log_capacity默认100MB很多写入密集的小型业务会发现默认值太小建议先观察写入峰值再设置成1GB左右避免频繁触发脏页强制刷盘。4.2 日志故障排查损坏、写满、复制中断日志相关的故障一般分几类按现场常见程度列一下。第一类是binlog空间写满或清理不及时导致磁盘满。binlog增长极快时先看是否有大事务或row格式下整表更新产生巨大日志同时检查保留时长参数。如果磁盘已经满了可以手工purge旧的binlog但前提是确认已经没有从库依赖这些文件否则主从复制会中断。第二类是redo log写满业务更新卡住。处理思路是先找到阻塞源头如果是某个大事务持有大量脏页等它提交如果写入量持续超过刷盘能力则考虑增大redo log容量并检查磁盘IO能力。不要一看到日志写满就反复重启重启会把问题带到恢复阶段反而更慢。第三类是复制中断。从库上执行SHOW REPLICA STATUS查看Last_IO_Error和Last_SQL_Error。IO线程报1236说明主库binlog已经被清理或者从库偏移量不连续。SQL线程报1062说明主库执行过同一个事务而从库又应用了一次常见于手动介入后从库位点被改乱。处理前先备份现场信息再判断是跳过错误还是重建从库。一旦决定重建用全量备份加binlog重新追平是最稳妥的路线。4.3 误删数据后用binlog做时间点恢复误删数据是binlog最能体现价值的时候。前提是binlog开着、格式是row、备份链完整。恢复思路是先找到事故时间点把所有binlog从最近一次可靠全备的位置重放到误操作前的位置导出成临时实例或直接导入到目标库。常用命令大致是这样找到最近全量备份将其恢复到临时实例或直接恢复数据文件。用mysqlbinlog把备份点之后的binlog解析成SQL比如mysqlbinlog --start-position1534 --stop-position2687 /data/binlog/binlog.000015 recover.sql检查recover.sql内容确认没有误删语句再执行导入。这里有个关键坑如果误操作之后后续还有很多正常写入直接把“最近全量备份后续binlog”恢复到原库会使备份时间点之后的正常业务写入全部被覆盖造成更大损失。更稳妥的做法是用临时实例先恢复出误操作前的数据把需要恢复的表导出再倒回线上库。恢复操作一定要在变更审批和允许停服的窗口内进行。5. 常见问题与踩坑实录5.1 binlog能不能替代redo log不能。虽然两者都记录变更但角度完全不同。binlog是Server层的逻辑日志记录了完整的SQL或行镜像可以保留很久、用于复制和恢复。redo log是引擎层的物理日志循环写、容量有限、主要用于快速崩溃恢复。如果只有binlog实例崩溃时磁盘上数据页可能停留在很早的状态InnoDB没办法只靠逻辑重放把所有数据页恢复到内存和磁盘一致的最近状态因为崩溃恢复需要按页物理修补。反过来如果只有redo log数据库就没法做时间点恢复也没法向从库提供持续的逻辑变更流。所以它们不是互相替代的关系而是缺一不可的配合关系。补充一点事务提交时binlog和redo log都需要落盘但数据页不需要。因此你可以在系统正常运行时看到binlog文件的生成时间和实际数据文件更新时间明显不同。这个不同正是WAL设计的结果。5.2 redo log写满时更新卡住怎么办前面提到redo log是环形空间如果write_pos追到checkpointInnoDB必须强制刷脏页以推进checkpoint才能继续写日志。此时几乎所有更新都会阻塞表现为监控里磁盘IO等待上升、更新线程堆积。处理流程是先查是否有超长事务或一次性变更大量数据的大事务这些事务占用大量redo log空间且迟迟不提交再查脏页比例是否过高刷盘线程是否被其他IO操作拖住最后才是考虑增大日志容量。我曾经遇到一个案例某个业务每天凌晨批量跑一个回写任务update语句一下子命中几千万行事务迟迟不提交redo log被基本填满其他小事务也一起堵住。后来优化方式是把大事务拆成多个小事务分批提交降低单个事务产生的redo log和undo log量同时在凌晨把刷盘策略调得激进一些问题才缓解。比起单纯把redo log调大控制事务大小往往更有效。5.3 undo log暴涨背后的长事务undo log无限增长最终会造成undo表空间膨胀也会导致历史版本链过长查询变慢。最常见的元凶就是一个没有提交的长事务。开启事务后一直不提交也不回滚它看到的“快照”就像给历史版本链加了一把锁purge线程无法清理那些旧版本。排查方法很简单SELECT trx_id, trx_started, trx_mysql_thread_id, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx WHERE trx_state RUNNING;如果trx_started时间非常早那基本就是它了。再用trx_mysql_thread_id去processlist里找到对应会话确认后kill掉。长事务结束后undo log清理也需要时间不需要频繁重启。如果是MySQL 8.0undo表空间自动truncate机制会逐步回收旧版本可能需要手动收缩但没有安全的在线收缩方案风险较大不推荐随意操作。我排查时还会顺手看performance_schema里的statement事件确认这个长事务是不是在反复执行同一条SQL比如在同一个条件下循环update每次只改少量行但事务一直不提交。这种场景比单纯“select放着一小时”更容易踩中undo暴涨。最后分享一个个人习惯。每次遇到和日志相关的线上问题我不会急着改参数而是在白板上把三条链路画出来redo log从记录物理变更到崩溃恢复binlog从事务提交到主从重放undo log从旧版本到回滚和MVCC。把“谁在什么时机写、谁在异常时读取、谁负责清理”标清楚再对照SHOW ENGINE INNODB STATUS里的日志序号、从库复制位点、事务列表绝大多数问题都能快速定位。这个思路也适用于刚入门的人先理解日志的关系再去记那些参数细节会轻松很多。