binlog(M):Ln45L2CtfODyMyfYu/eS/FDmWB8iOC14qM5gNLz3/d5Ec7IqCLzXTEMwV0DqAjNC7CKsXQwMrB2iS5h7KeKMKYLZMB/x5X9qgfdRZIrR9MbHGby4hcTGNGD9Sqq8XPIcUQsZe2nq3fKw在座各位凡是从运维一线爬过来的兄弟大概都有过这样的经历某天业务方火急火燎跑来说数据库里的一批数据莫名其妙没了或者某行数据被改成了奇怪的值让你赶紧查查到底什么时候发生的、谁干的。这时候你第一个想到的多半就是binlog。binlog这玩意儿平时安安静静躺在数据目录里但真到了数据追查、主从同步、闪回恢复这类场景它就是你手里最硬的底牌。这篇博文我就把查看binlog这件事从头到尾捋一遍从原理到实操从常用命令到折腾现场尽量让看完的兄弟能直接照着干活。说实话网上讲binlog的命令一大堆但很多教程只丢两句SHOW BINLOG EVENTS和mysqlbinlog就完事了遇到真实环境照样抓瞎。比如binlog刷得太快怎么定位大事务比如ROW格式下怎么把人眼读不懂的base64解码成能看的SQL比如磁盘要爆了能不能删binlog、怎么删才不会坑到从库——这些才是真正的实战点。下面我按自己平时排查问题时的思路一层层拆开来讲。1. 先弄清楚binlog到底是什么1.1 用一个生活例子理解binlogbinlog全称Binary Log也就是二进制日志。你可以把MySQL想象成一个记账先生平时每做一笔交易他的习惯是先在小本子上逐笔写下“某年某月某日谁在某张表上把哪条记录的哪个字段从什么值改成了什么值”写完才动手落账。这个小本子就是binlog。关键点在于它记的是“变更过程”而不是“结果快照”。比如你执行UPDATE把100行数据的余额都加了10块binlog里记录的是这一条更新动作的完整描述不是更新后的100行新数据。这个区别很重要决定了binlog的主要用途一是用来做主从复制从库拿到binlog里记录的操作自己在本地重放一遍就能变成和主库一样的数据二是用来做数据恢复和审计比如误删了数据可以通过binlog把当时执行过的操作反向推出来或者直接重放到某个时间点之前。1.2 binlog能做什么、不能做什么先说能做的数据闪回删错数据、改错数据之后用binlog可以把操作倒推回去恢复现场。这个我后面专门演示。主从同步一主一从或者一主多从的架构里从库全靠拉取主库binlog来保持同步。增量备份全量备份binlog增量这是MySQL最经典的备份组合。全量备份恢复之后再重放从备份时间点到故障时间点的binlog能把数据追到几乎零丢失。慢查询定位和审计通过分析binlog里的事务提交时间、语句内容可以反推某个时间窗口里数据库到底执行了什么大操作。再说不能做的它不记录SELECT和SHOW这类查询操作因为查询不会改变数据所以你想通过binlog审计“谁查了什么数据”是查不到的。这点很多刚接触的人会误解。它只记录“数据变更”层面的操作不记录OS层面的文件系统变化更别指望它帮你追查Linux系统里某个文件什么时候被删了。所以binlog是MySQL逻辑层面的变更日志定位要准确别指望它包打天下。binlog不是默认开启的。我遇到过不少开发环境的朋友以为MySQL自带这个功能结果真到要查的时候一翻配置发现log_bin根本没开日志文件一个都没有。所以接下来先讲讲怎么确认环境和参数。2. 查看binlog之前先确认环境是否就绪2.1 快速检查binlog是否开启连接到MySQL之后最直接的一步是执行SHOW VARIABLES LIKE log_bin;返回的Value如果是ON说明binlog已经开启如果是OFF后面所有查看命令都会报错或者说查不到任何文件。还有一个更全的参数可以看当前正在写的binlog文件名和位置SHOW MASTER STATUS;在老版本里这个命令叫SHOW MASTER STATUSMySQL 8.4之后改成了SHOW BINARY LOG STATUS两个别名现在都还能用。它输出的File字段就是当前正在写的binlog文件名Position是当前写入到的偏移量位置。如果log_bin是OFF别急生产环境改参数要谨慎但测试环境可以动态开MySQL 8.0里binlog是支持动态开启的SET GLOBAL log_bin ON;不过需要注意即使动态打开了已经在运行的实例也从当前时间点开始记录历史没开的窗口内是没有日志的。另外有些版本和参数组合下动态开关不一定生效最稳妥的方式还是改配置文件my.cnf加上[mysqld] log_bin /var/lib/mysql/mysql-bin server_id 1改完重启MySQL服务才能完全生效。server_id在主从环境里是必须的即使在单机上有些版本也要求配置server_id才能开启binlog否则启动报错。2.2 binlog的三种日志格式怎么选binlog有三种格式STATEMENT、ROW、MIXED。理解它们各自的脾性直接决定你后面查看日志时看到的内容长什么样。STATEMENT记录的是原始SQL语句。比如UPDATE t SET a1 WHERE id100binlog里就存这句话。优点是日志量小可读性强缺点是有些语句在不同库上执行结果可能不一样例如用了UUID()、NOW()这种非确定性函数主从重放时会产生数据不一致。ROW记录的是每一行数据的具体变化包括前镜像before image和后镜像after image。优点是主从复制最安全、最一致缺点是日志量明显变大而且人直接看是看不懂的默认还带base64编码需要用工具解码。MIXEDMySQL自动判断默认像STATEMENT那样记录SQL遇到可能产生不确定结果的语句时自动切成ROW。现在主流生产环境普遍用ROW格式。官方也在往这个方向推MySQL 8.0默认就是ROW。我个人的建议是除非你有特别强的理由比如日志量敏感、对可读性要求极高否则不要轻易用STATEMENT。ROW格式虽然日志大但它才是数据一致性的底线。查看当前格式SHOW VARIABLES LIKE binlog_format;2.3 几个和binlog有关的参数在讲查看命令之前有几个参数需要先有个概念因为后面排查问题的时候大概率会用到expire_logs_days老版本的日志过期天数参数。MySQL 8.0已经废弃改用binlog_expire_logs_seconds。binlog_expire_logs_secondsbinlog自动清理的秒数默认2592000也就是30天。设为0表示永不过期一般不建议。max_binlog_size单个binlog文件的最大体积默认1GB。超过后会自动滚动切换到下一个文件。binlog_cache_size事务在内存中缓存binlog的缓冲区大小如果事务很大这个值设小了会产生磁盘临时文件。sync_binlog控制binlog多久刷一次磁盘。设为1最安全表示每次提交都刷盘性能开销大一点但不会丢日志设为0性能好但数据库崩溃时可能丢最近的binlog。这些参数用一条命令就能全部看SHOW VARIABLES LIKE binlog%;输出结果会有一长串建议结合上面几个重点项去看。环境看完之后下面进入正题讲讲实际查看binlog的两种手段。我在公司排查问题的时候基本就这两条路线在线看或者离线看。3. 上手实操查看binlog的两种核心方式3.1 用SQL命令直接查第一种方式是在MySQL客户端里直接执行SQL命令适合快速看一眼某个文件里有哪些事件。最常用的是SHOW BINLOG EVENTS IN mysql-bin.000005;这条命令会把指定binlog文件里记录的所有事件列出来包括事件的起始位置、事件类型、所属库表、耗时、SQL原文等。不加IN参数时默认显示第一个binlog文件的内容。如果只想看某个文件的最后部分或者想看新产生的日志可以先找到当前正在写的文件SHOW MASTER STATUS;然后查看这个文件的事件SHOW BINLOG EVENTS IN mysql-bin.000012;但SHOW BINLOG EVENTS有个硬伤它只显示“事件描述”对于ROW格式的binlogSQL语句列显示的是base64编码人没法直接读。而且输出内容一多终端直接刷屏也不方便过滤。所以更实用的SQL命令是先列出所有binlog文件SHOW BINARY LOGS;这个结果里能看到每个文件的文件名和大小方便判断哪些文件比较“可疑”——比如某个文件短时间内涨到快1GB说明里面可能有大批量操作。如果想看指定位置范围内的事件可以配合FROM和LIMITSHOW BINLOG EVENTS IN mysql-bin.000005 FROM 120 LIMIT 10;但说实话SQL命令这种查看方式只适合快速确认“有没有、大概在哪”真正要拿binlog干精细活还得靠下面的mysqlbinlog工具。3.2 用mysqlbinlog工具离线分析mysqlbinlog是MySQL自带的命令行工具专门用来解析binlog文件。它会读取二进制文件把它翻译成可读的文本甚至可以还原成可执行的SQL。这个工具是查看binlog最核心的武器强烈建议熟练掌握。基本用法mysqlbinlog /var/lib/mysql/mysql-bin.000005这样会把整个文件的解析结果打到屏幕上。文件太大时根本看不完通常我们会配合参数mysqlbinlog --start-datetime2024-01-15 10:00:00 --stop-datetime2024-01-15 11:30:00 /var/lib/mysql/mysql-bin.000005按时间范围过滤。或者按位置范围mysqlbinlog --start-position1000 --stop-position3500 /var/lib/mysql/mysql-bin.000005这两种过滤方式几乎覆盖了所有排查场景。比如你想看某个精确时间段的误操作直接按时间过滤最方便如果你想接着某个备份的恢复点往后重放按位置过滤更准。还有一个超级常用的参数mysqlbinlog -d testdb /var/lib/mysql/mysql-bin.000005-d参数表示只输出指定数据库的日志跨库分析时特别好用。ROW格式的binlog解析出来之后默认会输出类似这样的内容### INSERT INTO testdb.user ### SET ### 1100 ### 2张三 ### 313800138000这是行数据的变化但没有还原成真正的SQL语句。如果需要生成可以直接执行的SQL加参数mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/mysql-bin.000005-v参数会输出按行解析后的注释内容--base64-outputDECODE-ROWS会把base64的伪装层剥掉让它变成人能读懂的字段值。注意这里生成的还是带###注释的伪SQL要拿回去执行还需要进一步处理不是直接就能跑的。如果要把解析出的SQL真正重放到从库或恢复实例可以跳过伪SQL的部分直接把binlog用管道灌给mysql客户端mysqlbinlog /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p这是主从复制出问题手动补数据时最常用的方式之一等于把binlog里记录的操作原封不动在新库上又执行一遍。3.3 实操案例从日志中找回一条被误删的数据前面讲了一堆命令下面结合一个真实场景把它串起来。假设testdb库的user表里有一行数据被误删了我们要从binlog里把它捞回来。先定位误删发生的时间点在binlog目录下看文件列表ls -lh /var/lib/mysql/mysql-bin.*如果业务方告诉你大概在下午2点到3点之间直接解析这个时间段的日志mysqlbinlog --base64-outputDECODE-ROWS -v --start-datetime2024-01-15 14:00:00 --stop-datetime2024-01-15 15:00:00 /var/lib/mysql/mysql-bin.000012 /tmp/recover.sql打开/tmp/recover.sql搜索DELETE相关的记录。ROW格式下删除操作的伪SQL会以### DELETE FROM开头。找到之后后面跟着的1、2就是被删掉那行数据的各个字段值。这时候有两种恢复思路第一种手工拼INSERT。从binlog里把字段值拿出来自己写一条INSERT语句插回去。适合数据量小的情况。第二种完整重放。如果误删的只是某个时间段内的少量操作可以把这个binlog文件里从误删前的某个位置开始到误删结束后某个位置为止的日志重放到数据库里。但这样有一个问题binlog里不仅包含DELETE也包含误删前后的其他正常操作盲目重放可能产生重复插入或主键冲突。更安全的做法是先把binlog解析出来手动把DELETE那条语句改成INSERT然后单独执行。我在实际恢复中踩过一个大坑直接用mysqlbinlog管道重放整个文件结果因为误删之后的同一时间段里还有UPDATE操作UPDATE基于旧数据去做条件匹配重放后根本匹配不到行不仅没恢复成功还把表里的其他数据搞乱了。正确的做法是解析出来后单独提取DELETE对应的行数据转为INSERT再手工执行或者在一个临时实例上做精确重放确认无误后再导回生产。这个教训值得每个做恢复的人记住。所以这里给个实操建议尽量把binlog文件归档保存到独立磁盘不要长期堆在数据库数据目录里。真要出大事时原始binlog还在你就有反复尝试的余地。我见过磁盘满了binlog被自动清理的情况那种情况下数据丢失想追都无从追起。看完核心命令和恢复案例下面集中聊聊实际运维中跟binlog打交道的那些高频坑。这些坑每一个我都亲自踩过写出来希望大家能少走弯路。4. binlog常见问题与排查技巧4.1 日志文件增长太快怎么办binlog刷得太快一天就生成几十个GB文件这个问题经常遇到。最常见的原因有两个一是大批量操作比如一次性UPDATE或DELETE几百万行数据二是频繁提交小事务比如循环里一条条执行UPDATE。排查步骤先按时间线看binlog文件的产生速度SHOW BINARY LOGS;如果某个文件在极短时间内写了接近max_binlog_size说明这个文件里有大批量操作。然后把有问题的文件解析出来看具体内容mysqlbinlog /var/lib/mysql/mysql-bin.000020 | grep -E UPDATE|DELETE|INSERT | wc -l如果某个文件里UPDATE特别多再精确定位是哪个库哪张表mysqlbinlog -d testdb /var/lib/mysql/mysql-bin.000020 | grep -B 5 UPDATE通常定位到具体SQL之后就能发现要么是有人跑了一次全表UPDATE要么是代码里没做批处理一条条update。解决方式也很直白大事务拆小事务分批提交代码逻辑改成批量SQL临时大批量操作尽量安排在业务低峰期。这里还要注意一个参数binlog_row_image。默认值是FULL表示记录完整的前后镜像可以改成MINIMAL只记录被修改的字段和能识别行的主键日志体积会明显下降。但有代价某些审计场景下看不到完整字段值改之前要想清楚。4.2 binlog文件损坏或无法解析遇到过mysqlbinlog解析到一半报错提示“Could not find first log file name in binary log index”或者“Found invalid event”的情况。这种通常有三个原因第一binlog文件本身在传输或拷贝过程中损坏了文件字节不完整。 第二数据库崩溃导致正在写入的binlog文件没有正常收尾末尾事件不完整。 第三磁盘写入异常binlog物理文件损坏。遇到这种情况先用mysqlbinlog试一下能不能解析从报错位置判断损坏程度mysqlbinlog /var/lib/mysql/mysql-bin.000023 /dev/null如果报错但之前的大半段能解析出来可以用--stop-never这种流式解析模式分段捞数据也可以从损坏位置截断只恢复损坏点之前的日志。在MySQL 8.0中还可以用binlog校验工具来检测文件完整性mysqlbinlog --verify-binlog-checksum /var/lib/mysql/mysql-bin.000023强烈提醒binlog的备份要及时做做好异地备份。我见过从库拉取binlog时网络中断导致半截文件落地的也见过磁盘坏道导致binlog文件有个洞的这些时候如果原始文件在别的机器上还有一份恢复难度就低很多。4.3 从库同步延迟和binlog的关联从库同步追不上主库很多情况要从binlog的文件大小和内容上去分析。主库的binlog写的都是大事务比如一次DELETE一万行从库执行同样的事务需要时间主库提交是秒级从库重放可能要几十秒延迟自然就上来了。查看从库状态SHOW SLAVE STATUS\G重点关注Seconds_Behind_Master这个字段。但这里要说一个容易被忽视的细节如果主库长时间没有写入操作从库的Seconds_Behind_Master会显示0这没问题但主库持续写入小事务时这个字段会频繁跳动要看趋势而不是盯瞬间值。如果延迟持续累积先看主库当前binlog位置和从库已经执行到的位置SHOW MASTER STATUS; SHOW SLAVE STATUS\G对比Master_Log_File和Exec_Master_Log_Pos就能算出从库落后了多少个文件。落后时间太长文件又大直接重放可行性很差不如重新做一次从库同步从最新的全量备份恢复加上binlog回放更实际。别死磕拉日志。4.4 磁盘空间被binlog占满的抢救方法这是最紧急的场景binlog直接把磁盘写满数据库直接进入只读或者干脆hang住。遇到别慌按步骤来。先确认是不是binlog占的空间du -sh /var/lib/mysql/mysql-bin.*如果确实是binlog把磁盘吃满了最直接的做法是清理过期文件。但注意一个关键点如果配置了从库不能随意删任意的binlog必须有章法地删除从库已经同步过的文件。官方推荐的做法是用PURGE命令PURGE BINARY LOGS TO mysql-bin.000030;这个命令会把mysql-bin.000030之前的文件全部删掉。前提是你确认所有从库都已经同步到了这个文件。如果不确定先看从库状态确认它在读哪个文件再决定删除边界。还有一种做法是按时间删PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;会删除3天前的binlog。适合时间敏感、可以接受只保留最近几天的场景。注意千万不要手贱直接去数据目录里rm binlog文件。用rm删文件不会同步更新binlog.index索引文件之后MySQL启动或者滚动binlog时就会因为索引里指向不存在的文件而报错折腾到哭。我在实验环境里干过这事当时整个人都不好了老老实实用PURGE才是正途。如果磁盘已经满了连执行PURGE都困难可以先临时把binlog过期时间调小让系统自动清理一部分SET GLOBAL binlog_expire_logs_seconds 3600;等磁盘缓过来之后再调回正常值。这个操作在紧急情况下的确管用但要意识到这是应急处理别把这个参数长期设得很小否则binlog保留时间太短后续想追查旧数据就没得查了。还有一个小技巧如果清完binlog磁盘空间还是紧看看relay log是不是也在膨胀。从库的relay log是同步过程中产生的中转日志也能占不少空间可以手动清理RESET SLAVE;但这个操作要慎用做完之后从库同步关系可能要重新配置别在业务高峰期乱动。另外提一句binlog和磁盘空间的关系binlog是可以删除的但删除要讲方式方法。这一点很多新手会问“binlog日志可以删除吗”答案当然是可以但不是简单地rm文件而是用PURGE命令或者调整expire参数让MySQL自己管理生命周期。4.5 看binlog时最常用的一批命令速查把上面讲到的命令集中整理一张表方便各位直接收藏使用场景命令查看binlog是否开启SHOW VARIABLES LIKE log_bin;查看所有binlog文件SHOW BINARY LOGS;查看当前正在写的binlogSHOW MASTER STATUS;查看指定文件的所有事件SHOW BINLOG EVENTS IN mysql-bin.000005;查看指定位置范围的事件SHOW BINLOG EVENTS IN mysql-bin.000005 FROM 100 LIMIT 5;解析整个binlog文件mysqlbinlog /var/lib/mysql/mysql-bin.000005按时间范围解析mysqlbinlog --start-datetime2024-01-15 10:00:00 --stop-datetime2024-01-15 11:00:00 /var/lib/mysql/mysql-bin.000005按位置范围解析mysqlbinlog --start-position100 --stop-position500 /var/lib/mysql/mysql-bin.000005只解出指定库mysqlbinlog -d testdb /var/lib/mysql/mysql-bin.000005ROW格式转为可读输出mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/mysql-bin.000005将binlog重放给MySQLmysqlbinlog /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p删除指定文件之前的日志PURGE BINARY LOGS TO mysql-bin.000030;按时间删除日志PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;这张表基本能覆盖日常90%以上的binlog操作场景建议存下来。4.6 两个容易被忽略的细节第一个细节查看binlog事件时事件类型里常见的有Query、Xid、Table_map、Write_rows、Update_rows、Delete_rows等。其中Table_map事件记录了接下来行操作对应的表结构映射Write_rows就是插入行Update_rows就是更新行。理解这些事件类型在手动解析时能快速判断binlog内容对应的是什么操作。第二个细节ROW格式的日志里位置信息非常关键。误删恢复时我们往往关注某个操作的起始pos和结束pos因为这两个值决定了在恢复时从哪里开始重放、到哪里停下来。举个例子如果你备份恢复到了误删前的某个时间点想精确跳过误删的那条DELETE只需要在重放时用--stop-position定位到DELETE之前的位置然后从DELETE结束之后的位置重新开始。这套操作在整个恢复流程里极其常用。5. 写在最后个人经验与扩展建议说白了binlog查看这件事本质考验的不是你会不会敲命令而是你遇到问题时能不能准确判断该看哪个文件、用什么姿势看、看完怎么用。我从最早傻乎乎地打开SHOW BINLOG EVENTS盯着base64发愣到现在能快速定位误操作、辅助恢复数据中间踩了不少坑。每次从binlog里翻出关键证据的时候那种感觉还是挺踏实的。最后分享一点个人习惯一是我在每个MySQL实例上都会设置binlog过期时间保留7到15天左右太长浪费磁盘太短追查不了历史二是我会定期抽查binlog的解析结果确认日志格式和内容没有异常三是每次做大的数据变更之前我会先记录当时的SHOW MASTER STATUS位置这样万一变更出问题可以精确从那个位置往后重放binlog来恢复数据。binlog这个功能的扩展空间也很大。比如你可以把binlog接入Canal这类组件实现对MySQL变更的实时监听再同步到其他存储系统完成异构数据同步。我在项目里就用Flink接MySQL binlog同步数据到ClickHouse整体链路跑得很稳定。如果你已经能熟练查看binlog顺着这条线继续往数据同步、实时计算方向探索会打开更大的世界。