干运维的尤其是MySQL逃不开binlog。不管你是排查数据异常、恢复误删记录还是做增量同步最后都得回到“查binlog日志”这件事上。很多新手只知道binlog是二进制日志真到需要看的时候要么用错命令要么解析出来一堆看不懂的乱码。这篇文章就按我日常排查的思路一步步讲清楚怎么查看、解析、过滤和利用binlog附带几个实战中踩过的坑。1. 先搞清楚binlog到底是什么为什么你非看它不可1.1 binlog在MySQL体系里的位置MySQL的binlog全称Binary Log也就是二进制日志它记录的是数据库层面所有改变数据的操作比如INSERT、UPDATE、DELETE、CREATE TABLE、ALTER TABLE等。注意SELECT查询不会写binlog因为它没有改变数据。但如果你手动执行了FLUSH LOGS之类的命令也会产生日志切换记录。为什么说它重要因为binlog是MySQL数据恢复、主从复制的底层依赖。你可以把它理解成飞机的黑匣子每一笔数据变更都按时间顺序记在里面。一旦数据库崩溃、误删数据或者从库落后我第一反应就是打开binlog找线索。很多刚接触MySQL的人会把binlog和redo log搞混。这里我用大白话区分一下redo log是InnoDB存储引擎自己用的它记录的是物理页面的修改主要解决崩溃恢复问题是引擎层的东西binlog是Server层生成的记录的是逻辑操作主要解决数据恢复、主从复制、审计等问题。简单说redo log是为了“不丢已提交事务”binlog是为了“能回放所有已提交操作”。1.2 什么场景下需要查看binlog实际工作中我碰得最多的场景就这四类误操作恢复比如一条UPDATE没带WHERE整张表全被改了。这时候你要是没开binlog基本只能靠备份硬扛如果开启了就可以用binlog把出事的那个时间点之前的数据找回来。主从同步排查从库报错同步卡住了主库的binlog位点Position对不对这必须通过查看binlog文件列表和当前位置来判断。定位大事务和慢操作一条大SQL执行了很长时间影响到线上业务。通过binlog文件大小、事件时间戳可以反推到底是哪段时间出了大事务。数据审计和追溯领导让你查某个账号在某个时间段改过哪些数据binlog就是最直接的证据。让我具体说说binlog对系统设计的意义。每一个binlog文件都有一段连续的编号MySQL启动、执行FLUSH LOGS或文件达到max_binlog_size时都会生成新文件。每个文件内部又由若干个“事件Event”组成每个事件前面都有固定的格式头记录时间戳、事件类型、server_id、end_log_pos等信息。所以查看binlog不只是“cat一下”而是要会解析这些事件。2. 准备工作先确认你的MySQL到底开没开binlog2.1 查看当前binlog状态与关键参数如果你连binlog有没有开都不知道后面的操作全是空中楼阁。执行下面这条SQLSHOW VARIABLES LIKE log_bin;如果结果是ON说明开启了如果是OFF你需要修改配置文件后重启MySQL才能开启这个后面讲。另外几个参数也要一起确认SHOW VARIABLES LIKE binlog_format; SHOW VARIABLES LIKE max_binlog_size; SHOW VARIABLES LIKE expire_logs_days; SHOW VARIABLES LIKE binlog_expire_logs_seconds;顺便说一句从MySQL 8.0开始expire_logs_days已经被binlog_expire_logs_seconds代替了。如果你还在用expire_logs_days在8.0里会收到弃用警告。老版本里expire_logs_days默认是0表示永不过期这个非常危险建议生产环境一定要设置自动清理策略。2.2 binlog格式选不好查看时白费功夫binlog_format有三种STATEMENT、ROW、MIXED。这个直接决定你查看binlog时看到的是SQL语句还是“数据行变化”。STATEMENT格式记录的是SQL原文比如UPDATE t SET namex WHERE id1。优点是日志量小缺点是某些函数、不确定操作在主从复制时可能产生不一致。ROW格式记录的是每一行数据的变更前后值比如“第3行旧值是多少、新值是多少”。优点是复制安全缺点是非常占空间。MIXEDMySQL自己判断大部分用STATEMENT遇到不确定操作自动切换成ROW。我个人强烈推荐生产环境用ROW格式。虽然文件大一点但排查问题的时候真的省心——你能看到具体的行数据变化而不是一句模棱两可的SQL。如果你用的是MariaDB或者老版本那另说但主流MySQL 5.7/8.0我都建议ROW。查看当前格式SHOW VARIABLES LIKE binlog_format;如果生产库里已经跑起来了临时改格式SET GLOBAL binlog_format ROW;注意GLOBAL级别的修改只对后续新事务生效而且不能完全解决历史日志的问题想要永久生效还得改配置文件my.cnf建议放在[mysqld]段下[mysqld] log-binmysql-bin binlog_formatROW server-id1 max_binlog_size512M binlog_expire_logs_seconds604800这里的server-id很重要特别是有主从复制的时候每台机器server-id必须唯一。如果没设置MySQL 8.0某些情况下会拒绝启动binlog因为事务里没有server_id标记就可能导致复制链路混乱。3. 四步走把binlog从“黑盒”变成“白盒”3.1 第一步列出所有binlog文件进入MySQL命令行执行SHOW BINARY LOGS;它会返回类似这样的结果----------------------------- | Log_name | File_size | ----------------------------- | mysql-bin.000001 | 1024 | | mysql-bin.000002 | 2048 | -----------------------------如果你用的是老版本命令是SHOW MASTER LOGS;效果一样。这里有个细节File_size是多少不重要重要的是文件名最后的序号它意味着日志的连续性。000001是第一个之后会递增。当你做数据恢复时往往要从某一个点开始按顺序回放多个binlog文件顺序绝对不能乱。3.2 第二步查看当前正在写的binlog和位点在线查看当前正在写的binlog文件SHOW MASTER STATUS;结果示例------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 154 | | | | -------------------------------------------------------------------------------这里Position154表示“到目前为止已经写到了这个偏移量”。什么概念每个binlog文件开头都有一个固定的文件头通常是120字节所以正常第一个事务开始会在120以后。如果你看到154说明已经写了一些事件。对于从库排查还会用到SHOW SLAVE STATUS\G其中Master_Log_File和Read_Master_Log_Pos表示从库IO线程已经读到主库的哪个位置Exec_Master_Log_Pos表示SQL线程已经执行到哪个位置。如果这两个值差距很大说明从库在追赶binlog的传输或锁等待很可能有问题。3.3 第三步用mysqlbinlog工具解析二进制日志这一步是查看binlog的核心。直接从MySQL命令行是没法SELECT出binlog内容的必须用自带的mysqlbinlog工具或者通过SHOW BINLOG EVENTS查询。先看最简单的用法在操作系统命令行执行mysqlbinlog /var/lib/mysql/mysql-bin.000003如果文件路径不对可以先通过SHOW BINARY LOGS;看到文件名然后去MySQL数据目录找。默认数据目录在/var/lib/mysql/Linux、C:\ProgramData\MySQL\MySQL Server 8.0\DataWindows具体可用SHOW VARIABLES LIKE datadir;确认。解析出来的内容长这样# at 154 #220623 10:23:45 server id 1 end_log_pos 258 CRC32 0x... Query thread_id10 exec_time0 error_code0 SET TIMESTAMP1655861025/*!*/; BEGIN /*!*/; # at 258 #220623 10:23:45 server id 1 end_log_pos 370 CRC32 0x... Table_map: test.user mapped to number 89 #220623 10:23:45 server id 1 end_log_pos 470 CRC32 0x... Write_rows: table id 89 flags: STMT_END_F你可能看到一堆以# at开头的注释行注意这些不是注释而是事件的位置标记。每个事件的开始位置用# at 154表示然后紧跟end_log_pos代表下个事件的开始位置。这个偏移量在做恢复时非常重要START-POSITION和STOP-POSITION就是靠它指定的。 如果你觉得输出太多看着累可以加-v参数让输出更详细。-v会把ROW格式的事件解码成伪SQL方便你直观看到插入的行值。例如 bash mysqlbinlog -v /var/lib/mysql/mysql-bin.000003再加一个-vv会额外显示列类型和元数据大多数情况下-v足够。3.4 第四步精准过滤指定表和时间的日志生产环境的binlog动辄几十GB你不能从头看到尾。mysqlbinlog提供了一组过滤参数非常实用。按时间范围过滤mysqlbinlog --start-datetime2024-01-01 00:00:00 --stop-datetime2024-01-01 23:59:59 /var/lib/mysql/mysql-bin.000003按偏移量过滤mysqlbinlog --start-position154 --stop-position470 /var/lib/mysql/mysql-bin.000003按库名数据库过滤mysqlbinlog --databasetest /var/lib/mysql/mysql-bin.000003这里要提醒一个坑--database过滤是“基于当前默认库”的语义也就是说它判断的是事件写入时所在的连接默认库而不是完全按语句中显式出现的库名去匹配。如果你用了USE test执行INSERT INTO test.t1会被过滤进来但如果你什么USE都没执行直接写了INSERT INTO other.t1它可能不会被--databasetest匹配到。所以做精确恢复时我更推荐直接用--start-position结合前面SHOW BINLOG EVENTS来定位而不是单纯依赖--database。如果想看某个文件里有哪些事件可以用SQL命令快速浏览SHOW BINLOG EVENTS IN mysql-bin.000003 FROM 0 LIMIT 10;这个命令在远程维护、不方便登录系统执行mysqlbinlog时非常救命。4. 顺着binlog做故障排查与数据恢复4.1 误删数据回放把表救回来这是binlog最典型的应用场景。举个例子我在凌晨不小心执行了DELETE FROM orders WHERE created_at 2024-01-01;结果条件写错了删掉了所有历史订单。如果没有备份怎么办用binlog。第一步先确认误删操作发生的时间。假设是凌晨02:15:00。第二步确定binlog文件和位置。可以这样看SHOW BINLOG EVENTS IN mysql-bin.000005 LIMIT 20;找到Query或Delete_rows事件前后的end_log_pos。第三步用mysqlbinlog把从变动前到误操作前的日志导出成SQLmysqlbinlog --start-position120 --stop-position10000 /var/lib/mysql/mysql-bin.000005 /tmp/recover_before.sql然后把这个SQL导入数据库注意先备份当前库就能回放到误删除之前的状态。如果要把“误操作之后的正常操作”也精确过滤可以拼接多个binlog文件mysqlbinlog本身就支持传入多个文件按顺序解析mysqlbinlog mysql-bin.000005 mysql-bin.000006 all.sql需要注意的是如果你用的是ROW格式回放时其实执行的是Delete_rows事件对应的逆向操作不是mysqlbinlog输出的是原始删除事件直接执行还会删数据。真正要恢复你得把DELETE事件对应的行重新INSERT回去。通常我们会先用-v解析人工把DELETE改成INSERT再执行。这也是为什么很多DBA在恢复时更依赖专门工具如binlog2sql但本文主要讲查看所以不做工具扩展。4.2 主从同步position是核心查看binlog还有个大用处就是排查主从同步中断。当从库报错Error executing row event你经常需要知道从库当前执行到哪个binlog位置以及主库现在写到哪个位置。执行SHOW SLAVE STATUS\G重点关注Relay_Log_File、Relay_Log_Pos以及Last_SQL_Error。如果Last_SQL_Error提示你某个GTID事务冲突那就要去主库查看对应binlog。如果是基于Position的复制老方案你需要确定从库下次应该从哪个Master_Log_File、哪个Master_Log_Pos继续。这个Position正来自binlog事件头所以你能看懂binlog的end_log_pos对复制原理的理解就上一大台阶。新版的GTID复制虽然不用position但binlog里也有GTID事件查看起来同样依赖binlog解析。4.3 定位大事务和慢SQL有时候线上突然慢但没有慢查询日志此时binlog也能帮忙。因为binlog每个事务事件前面都有时间戳你可以通过统计相邻事务的事件大小、时间间隔来推测大事务。比如mysqlbinlog -v --base64-outputDECODE-ROWS mysql-bin.000002 | awk /# at/{pos$3} /exec_time/{time$5} /end_log_pos/{print pos, time, $0} | tail -100用select看每个事件的exec_time数值越大说明这个事务执行耗时越久。如果某个事件后面跟着几百行Write_rows大概率就是批量刷数据把线程卡住了。定位到具体大事务后下一步就是优化SQL或分批提交。5. 实战中躲不开的坑与注意事项5.1 看到“乱码”先别慌加上base64-output参数用mysqlbinlog解析默认输出是base64编码的ROW数据很多没经验的人一看就懵以为日志坏了。实际上那是行数据的二进制表示。想要看人话必须加mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/mysql-bin.000003--base64-outputDECODE-ROWS告诉工具把行事件解码成基于文本的伪SQL配合-v就会显示类似### INSERT INTO test.user ### SET ### 11 ### 2张三这才是正常状态。不加这个参数你看到的就是一大串BINLOG ...。5.2 远程查看binlog别把整个文件下载下来当MySQL实例不在本地你可能会想着把binlog文件scp下来慢慢看。如果文件几GB网络带宽会先崩。正确做法是用mysqlbinlog的--read-from-remote-server参数直接在远程MySQL上读取mysqlbinlog --read-from-remote-server -h 192.168.1.10 -P 3306 -u dba -p --start-position154 mysql-bin.000003注意这种方式需要账号有REPLICATION SLAVE权限否则会报权限不足。命令格式里的最后一个参数是binlog文件名不要带路径。远程模式同样支持--base64-outputDECODE-ROWS -v。如果你没有操作系统权限但能连MySQL也可以用SHOW BINLOG EVENTS把事件内容捞出来但输出内容是经过加工的没有mysqlbinlog那么精细。只有mysqlbinlog工具支持--start-datetime这种时间过滤而SHOW BINLOG EVENTS只能用FROM position。5.3 文件被你删了怎么办回天乏力binlog是可以删除的但删除方式有严格讲究。绝对不要在操作系统层面直接rm文件否则MySQL可能不知道哪些文件已经不存在导致主从复制直接崩溃。正确方式PURGE BINARY LOGS TO mysql-bin.000010;这条命令会删除000010之前的binlog文件。或者按时间删除PURGE BINARY LOGS BEFORE 2024-06-01 00:00:00;自动删除则依赖binlog_expire_logs_seconds配置。很多新手问我“binlog日志可以删除吗”答案是可以但前提是当前binlog正在使用不能删。从库还没读取完的文件不能删。没有备份只能靠binlog找回数据的业务建议多留几天再删。你可以通过以下命令查看正在使用的binlog这时你一定要避开它SHOW MASTER STATUS;File字段指向的就是当前正在写的文件。PURGE BINARY LOGS TO不会删除当前文件只会删它之前的这个设计很安全。5.4 写恢复脚本时注意时间和时区binlog里记录的SET TIMESTAMPxxx用的是Unix时间戳回放时会改变当前会话时间。如果你把binlog导出的SQL直接导入另一个库需要在导入前设置会话时区否则可能导致时间字段差8小时。经验做法是在导入前先执行SET time_zone 08:00;尤其是在云数据库、Docker容器之间迁移时时区问题非常常见。5.5 Docker环境的binlog路径我踩过的坑很多人在Docker里跑MySQL安装完却找不到binlog。原因多半是容器默认没有开启log-bin或者没有把日志目录挂载出来。进入容器确认docker exec -it mysql_container mysql -uroot -p登录后执行SHOW VARIABLES LIKE log_bin;如果是OFF就需要在容器启动时加参数或在宿主机配置挂载的my.cnf。启动示例docker run -d --name mysql8 -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDxxx \ -v /data/mysql/conf:/etc/mysql/conf.d \ -v /data/mysql/logs:/var/log/mysql \ mysql:8.0然后在/data/mysql/conf/my.cnf里写下开启binlog的配置。注意容器内路径和宿主机路径不一致查看日志时要用docker exec到容器里看或者把日志目录挂载出来到宿主机查看。我刚开始就吃过“在宿主机找不到binlog文件”的亏。5.6 网络安全视角binlog可能暴露敏感数据因为ROW格式的binlog会记录每一行数据的完整值相当于把数据明文写进日志。如果你的binlog文件被无关人员读取等于脱裤。所以生产环境一定要注意对binlog文件所在目录设置严格文件权限建议700。用mysqlbinlog远程读取的账号只授予所需权限不要给ALL PRIVILEGES。如果业务有合规要求建议binlog不要长时间保留并定期PURGE。这算是运维人员的底线意识不只是功能“能查看”的问题。6. 高频问题速查表我把日常被问得最多的几个问题整理成下表方便你直接对照解决。问题一句答案对应命令/参数怎么知道binlog开没开查变量log_binSHOW VARIABLES LIKE log_bin;怎么看到所有binlog文件查binary logsSHOW BINARY LOGS;当前写到哪个文件哪个位置查master statusSHOW MASTER STATUS;怎么看binlog文件内容用mysqlbinlog解析mysqlbinlog -v --base64-outputDECODE-ROWS 文件名只看某段时间的日志按时间过滤--start-datetime/--stop-datetime只看某偏移区间按位置过滤--start-position/--stop-position远程读取binlog用远程模式mysqlbinlog --read-from-remote-server -h IP --start-position154 文件名binlog可以删除吗可以但用PURGEPURGE BINARY LOGS TO mysql-bin.000010;当前文件能删吗绝对不能先查SHOW MASTER STATUS;确认解析出来是乱码加DECODE-ROWS--base64-outputDECODE-ROWS -vROW格式怎么看具体行值加-v或-vvmysqlbinlog -v 文件名查看某个库的操作按库过滤--database库名注意默认库语义主从同步卡住了怎么看查slave statusSHOW SLAVE STATUS\G结合master status比较position日志文件太大怎么办调整最大大小SET GLOBAL max_binlog_size1073741824;log_bin开启后想关闭影响复制不建议需改配置重启且清理复制关系这个表不是万能答案但覆盖了我日常80%的问题。最后再分享一个小技巧。排查binlog时不要一上来就输出整份文件先执行SHOW BINLOG EVENTS IN mysql-bin.000003 LIMIT 20;观察事件类型和end_log_pos分布再决定用哪个position区间导出。等你操作得多了会发现这套“先定位、再过滤、最后解析”的习惯比盲目下载日志再翻半天高效得多。