先不说理论。就说前阵子发生的事某开发同学在测试环境准备清一张订单表的脏数据SQL写好了库也切了回车一按发现连的是生产库业务表一下子少了十几万行。当时所有人的目光都转向我“赶紧看binlog日志能不能把数据捞回来。”mysql查看binlog日志这个技能平时安静地躺在文档里真正要用到的时候就是救命的。这篇文章我打算把“查看binlog”这件事讲透什么时候要看、看之前要确认什么、用什么命令看、看了怎么用以及最容易被忽略的格式细节。适合刚接触MySQL的开发者、需要做数据恢复或接入数据管道binlog同步的工程师。文章里的命令我在通用环境下都验证过MySQL 5.7和8.0均适配个别差异会单独标注。1. 为什么要翻binlog三类必须看日志的运维场景1.1 误删数据后的最后一道防线binlog全称是二进制日志Binary Log它记录的是数据库上发生的每一次写操作包括INSERT、UPDATE、DELETE也包括DDL语句。MySQL主从复制靠它数据恢复靠它很多实时同步工具比如类Canal、类Debezium的组件也靠它。可以说binlog就是MySQL世界里可以“重放历史”的时光机。最常见的翻日志场景就是误删数据。一个DELETE不带WHERE或者UPDATE的WHERE条件写错了只要binlog开着理论上都能把变更前的内容捞出来。关键在于捞出来的过程不是直接打开日志看明文SQL那么简单尤其当binlog_format是ROW格式时你看到的是一堆二进制事件和伪行需要用工具解析成可读的内容再反向构造恢复语句。这篇文章后面会专门用一整章演示这个链路。1.2 主从复制不一致与数据管道排查第二个高频场景是主从复制。从库追不上主库、主从数据不一致、复制链路中断这些问题的排查往往都要翻binlog。举个例子从库报错“Cannot delete or update a parent row”多半是主库先删了父表数据binlog里对应的事件没有在从库正确落地。这时候需要对比主库和从库各自执行的binlog位置定位到具体事务。第三个场景相对新一些做数据管道。很多团队用binlog同步MySQL数据到消息队列、数仓或者搜索引擎一旦同步链路出现乱序、丢数据或者重复消费也要回头分析binlog里的事件内容和GTID范围。说白了binlog不仅是数据库的“账本”还是整个数据系统的“事件源”会看它相当于掌握了所有下游排查的入口。1.3 先纠正一个认知binlog不是拿来定位慢查询的经常有人混淆binlog和慢查询日志。慢查询日志记录的是执行时间超过阈值的SQL语句是用来做SQL优化的binlog记录的是数据变更事件是做复制和恢复的。两者职责完全不同。还有错误日志error log、通用查询日志general log也都不是binlog。搞清楚日志分工能避免在排查问题时走错方向。2. 动刀之前先摸清家底日志开关、格式与文件分布2.1 确认binlog开启状态与存储位置很多自建MySQL默认没开binlog这是一个非常容易被忽略的点。你兴致勃勃地准备翻日志结果发现log_bin是OFF那一刻什么都做不了。所以第一步永远是确认开关状态SHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE log_bin_basename; SHOW VARIABLES LIKE log_bin_index;log_bin显示ON说明binlog已开启。log_bin_basename是binlog文件的完整路径前缀比如/data/mysql/binlog/mysql-bin实际文件就是mysql-bin.000001、mysql-bin.000002这种编号递增的序列。log_bin_index则是索引文件路径里面记录了所有binlog文件清单。如果log_bin是OFF自建实例需要修改配置文件开启然后重启MySQL。这一步没得商量不开binlog的实例在误删场景下基本没有挽救余地。云数据库默认都开着一般不用操心。2.2 搞懂binlog_format三种格式的取舍比开关更关键的是binlog_format它直接决定你“能看到什么”SHOW VARIABLES LIKE binlog_format;格式记录内容日志量一致性与可恢复性适合场景STATEMENT记录原始SQL语句最小弱NOW()、RAND()等不确定函数会导致主从数据不一致对一致性要求不高的历史环境ROW记录行级别的实际变更最大强每一行变更前后都能拿到精确值生产环境首选默认推荐MIXEDMySQL自行判断不确定语句自动转ROW中等较稳需要兼容旧环境的场景为什么我强烈建议生产环境用ROW因为STATEMENT格式记录的是“当时执行的那条SQL”恢复的时候要把SQL重放一遍。如果SQL里用了NOW()、UUID()这类函数重放时得到的结果和原始值可能完全不同数据就恢复不到原样。而ROW格式记录的是每一行实际变化的结果精确到字段值恢复时可以直接用这些值构造INSERT/UPDATE不存在“再算一次”的偏差。MySQL 5.7.7之后默认binlog_format就是ROW8.0也是默认ROW。如果你的环境还是STATEMENT建议尽早评估切换。切换只影响新写入的binlog事件已经产生的日志格式不会变。2.3 日志文件清单与当前写入位置接下来看日志文件池SHOW BINARY LOGS;这条命令列出所有binlog文件和各自大小比如----------------------------------------------- | Log_name | File_size | Encrypted | ----------------------------------------------- | mysql-bin.000046 | 1073741824 | No | | mysql-bin.000047 | 536870912 | No | -----------------------------------------------再有SHOW MASTER STATUS;这条在MySQL 8.0和5.7里都可用8.0也有SHOW BINARY LOG STATUS别名。它显示当前正在写的binlog文件名和当前写入位置----------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ----------------------------------------------------------------- | mysql-bin.000047 | 12345 | | | -----------------------------------------------------------------这个Position对排查很有用。比如你说“刚才14点23分执行了误删”我可以先看时间过滤也可以根据当前Position做范围估算快速锁定应该看哪个文件。binlog文件按大小滚动max_binlog_size默认是1GB满了就切换到下一个编号。过期清理由binlog_expire_logs_seconds控制5.7早期版本是expire_logs_days。这里要特别强调不要为了省磁盘随便删binlog文件更不要手动改索引文件。真需要清理就用PURGE BINARY LOGS BEFORE这类官方命令否则索引和文件对不上复制会直接报错。3. 四类基础查看手法从SHOW命令到mysqlbinlog工具3.1 用SHOW BINLOG EVENTS做快速浏览先介绍一个适合快速浏览的命令SHOW BINLOG EVENTS IN mysql-bin.000046;它会把指定文件里的事件一条条列出来包括事件类型、日志位置、执行时间、server_id、end_log_pos等关键字段。也可以在结尾加LIMIT限制行数SHOW BINLOG EVENTS IN mysql-bin.000046 FROM 1000 LIMIT 20;FROM 1000的意思是跳过文件起始偏移量到1000之前的event从偏移量1000处开始读。这个命令适合什么时候用呢文件不大、事务不多、你只是大概看看最近发生了什么。比如怀疑某条语句写进了binlog但不确定在哪先列一下事件分布。它最大的问题是当文件很大时性能很差因为内部要扫文件的起始部分才能定位。另外ROW格式下它输出的伪行内容也是二进制的肉眼基本读不出业务信息。所以它只能算“预览”主力还是mysqlbinlog工具。3.2 mysqlbinlog查看binlog日志的主力工具mysqlbinlog是MySQL官方自带的命令行工具也是我日常查看binlog日志的主力。最朴素的用法mysqlbinlog --no-defaults /data/mysql/binlog/mysql-bin.000042--no-defaults一定要带因为mysqlbinlog会读取my.cnf里的配置某些客户端配置会导致解析行为异常或者干脆报错。这是很多人踩过的坑。但直接这样执行ROW格式的事件输出是一坨base64编码的二进制根本没法看。要看到可读内容关键参数是mysqlbinlog --no-defaults --base64-outputdecode-rows -vv \ /data/mysql/binlog/mysql-bin.000042--base64-outputdecode-rows告诉工具把ROW事件的二进制内容解码成伪SQL-vv让它输出更详细的行数据注释包括每一列的类型、是否为NULL等信息。加了这两个参数你才能看到类似这样的内容### DELETE FROM app_order.orders ### WHERE ### 110001 /* INT meta0 nullable0 is_null0 */ ### 22024062801 /* BIGINT meta0 nullable0 is_null0 */ ### 32024-06-28 10:00:00 /* DATETIME(0) meta0 nullable1 is_null0 */ ### 4PENDING /* VARSTRING(20) meta20 nullable1 is_null0 */这就是ROW事件解码后的“伪SQL”不是真正能直接执行的语句但人类能读懂了。1、2代表表的第1列、第2列和建表语句的字段顺序一一对应。3.3 按时间范围过滤日志的姿势直接解析整个文件在日志量大的环境里是不可能的必须先缩小范围。mysqlbinlog支持时间过滤mysqlbinlog --no-defaults --base64-outputdecode-rows -vv \ --start-datetime2024-06-28 14:20:00 \ --stop-datetime2024-06-28 14:30:00 \ /data/mysql/binlog/mysql-bin.000042 /tmp/order_delete.sql这条命令把14:20到14:30之间的事件提取出来输出到文件。时间过滤的原理是扫描事件头带的时间戳不是扫描SQL里的时间所以即使事件很靠前只要落在区间内就会输出。不过要提醒一点binlog事件里的时间是服务器当时的时间戳如果服务器time_zone设置和客户端不一致过滤结果可能偏移。生产环境的time_zone、log_timestamps参数建议全局统一否则看日志时容易产生错觉。3.4 远程拉取线上binlog不用登服务器线上binlog通常都在服务器本地如果你没有登录权限或者不想在服务器上做大量解析操作mysqlbinlog还支持远程读取mysqlbinlog --no-defaults \ --read-from-remote-server \ --host10.10.1.20 \ --userbinlog_reader \ --passwordyour_password \ mysql-bin.000042这里--read-from-remote-server是开启远程模式后面直接给binlog文件名。需要说明远程模式通常只是把binlog内容拉取到本地输出不会在远端做解析所以配合--base64-outputdecode-rows -vv使用即可。远程读取账号需要有REPLICATION SLAVE或相关权限具体权限名取决于MySQL版本。这个功能在排查线上问题时很实用不用费劲申请服务器登录直接在你的跳板机上拉日志。4. 误删数据后的现场还原一次真实的问题排查链路4.1 第一步根据时间锁定binlog文件假设场景某天14:23左右一条DELETE FROM app_order.orders WHERE statusPENDING误删了线上订单表的数据。你接手排查第一步是按时间确定该看哪个binlog文件。先查当前日志状态mysql SHOW MASTER STATUS;发现当前在写mysql-bin.000047Position不大。再回忆一下binlog文件每个最大1GB一天大概滚动几个文件那么13点到14点之间的日志大概率在mysql-bin.000045或mysql-bin.000046。拿不准就把怀疑范围内的几个文件都列出来通过文件大小和修改时间辅助判断通常在服务器上ls -lh看文件的mtime是最直观的。这一步不需要很精确只要锁定文件就行后面还有更细的筛选。4.2 第二步grep出误操作位置锁定文件后先解析到文本再通过grep定位关键词。mysqlbinlog --no-defaults --base64-outputdecode-rows -vv \ --start-datetime2024-06-28 14:00:00 \ --stop-datetime2024-06-28 15:00:00 \ /data/mysql/binlog/mysql-bin.000046 /tmp/binlog_scan.sql然后用grep搜表名grep -n app_order.orders /tmp/binlog_scan.sql | head -20假设搜到的结果里有这样一段# at 4198 #240628 14:23:05 server id 1 end_log_pos 4321 CRC32 0x... # Table_map: app_order.orders mapped to number 89 # at 4321 #240628 14:23:05 server id 1 end_log_pos 5217 CRC32 0x... # Delete_rows: table id 89 flags: STMT_END_F这里的at 4198就是DELETE事件在binlog文件里的起始偏移量end_log_pos 5217是事件结束位置。这两个数字是接下来恢复的关键锚点。4.3 第三步锁定并解析DELETE_ROWS_EVENT拿到偏移量后用position参数精确裁剪这段日志再仔细看伪行内容mysqlbinlog --no-defaults \ --start-position4198 \ --stop-position5217 \ --base64-outputdecode-rows -vv \ /data/mysql/binlog/mysql-bin.000046 /tmp/delete_events.sql打开文件你会看到完整的DELETE_ROWS_EVENT输出每一行被删的数据都清晰列出来。这是恢复的数据源。我习惯把这些行整理成一张临时的数据清单先数数有多少行和生产反馈的受影响行数核对确认没有漏数据。这一步相当于“数据盘点”一定要先对数再做后续动作。接下来把DELETE反向构造成INSERT。可以人工根据伪行里的1、2、3等列值拼接INSERT语句也可以写一个小脚本自动转换。注意如果表有自增主键恢复时要把主键值一并写回去保持和其他表的外键关系不变。伪行里1如果是主键通常能看到它的值直接保留即可。提示如果删除操作是在一个事务里binlog里还会看到BEGIN和COMMIT事件。恢复时最好把整个事务的数据作为一个整体处理避免半截恢复导致中间状态不一致。4.4 第四步恢复脚本与导入时的几个易错点恢复SQL准备好之后导入生产库前有几个动作必须做第一先在临时库或者一个备份表中验证。建议建一张结构完全一样的临时表把INSERT语句跑进去比对行数和几个关键字段的合计值确认数据完整再动生产。第二导入生产库时关闭当前会话的binlog记录SET sql_log_bin 0;这样恢复动作本身不会产生binlog避免把“恢复数据”这件事再次复制到从库或下游管道造成二次污染。注意这个变量是会话级的只影响当前连接。第三如果实例启用了GTIDmysqlbinlog解析出来的事件里会带SET SESSION.GTID_NEXT这一类语句。直接把这些语句导入生产或者从库可能会出现GTID重复或者复制中断。我一般建议恢复时给mysqlbinlog加上--skip-gtids让它忽略GTID信息直接用事务本身落地。第四恢复窗口尽量安排在业务低峰。十几万行的UPDATE/INSERT在高峰期执行锁等待和主从延迟都可能放大甚至影响线上稳定性。我通常会在低峰窗口执行并先跑一个行数统计脚本做到心里有数。5. ROW格式日志的深度拆解event结构、伪行技术与常见误读5.1 binlog内部结构事件流与事件头很多人在刚接触binlog时会好奇为什么用文本编辑器打开是一堆乱码因为binlog本质是二进制文件它由一串事件event组成。每个事件都有固定格式的头部主要字段包括字段含义timestamp事件发生的时间戳event_type事件类型比如QUERY_EVENT、TABLE_MAP_EVENT、DELETE_ROWS_EVENTserver_id产生事件的服务器IDlog_pos当前事件在binlog文件中的结束偏移量也是下一个事件的起始位置flags标志位表示该事件是否跨文件等状态理解了事件头就能解释前文里# at 320的含义它表示第320个字节开始是一个新事件。end_log_pos 456表示这个事件在456字节处结束。日志的偏移量说白了就是文件里的字节位置两个事件之间的区域就是你裁剪日志的最小单位。5.2 常见event类型一览我在解码binlog时最常碰到的事件类型整理成一张表event_type作用FORMAT_DESCRIPTION_EVENTbinlog文件开头的格式描述事件定义日志版本和事件头长度PREVIOUS_GTIDS_LOG_EVENT上一份binlog的GTID集合主要用于复制链路衔接ROTATE_EVENT表示binlog滚动到下一个文件GTID_LOG_EVENT标记事务的全局唯一ID仅在开启GTID时出现QUERY_EVENT记录SQL语句常用于BEGIN、COMMIT以及DDLTABLE_MAP_EVENT把表ID映射到具体表名和列结构是ROW事件的前置事件WRITE_ROWS_EVENTROW格式的INSERT事件UPDATE_ROWS_EVENTROW格式的UPDATE事件DELETE_ROWS_EVENTROW格式的DELETE事件XID_EVENT提交一个事务对应COMMIT这里面最核心的逻辑是每一条ROW事件前都会有一个TABLE_MAP_EVENT。它记录了表ID和表结构元数据后续的WRITE_ROWS_EVENT等事件只存行数据不重复记录列名这样压缩日志体积。这也是为什么你看单个DELETE_ROWS_EVENT会一头雾水必须把它的前一个TABLE_MAP_EVENT一起看才能还原出完整的“哪个表、哪几列被改了”。5.3 为什么ROW格式看不到明文SQL只能看到伪行这个问题经常被问。STATEMENT格式的binlog里记录的是SQL语句直接看就能理解。ROW格式记录的是“这一行的值从什么变成了什么”所以展示出来是伪SQL而不是真SQL。伪行的价值在于精确。比如一条UPDATE ... SET amount amount 10STATEMENT格式只能告诉你“有这么一条SQL”至于这一行原来的amount是多少你根本不知道。ROW格式直接给出“旧值是100新值是110”恢复和审计都一目了然。另外要注意binlog_row_image参数它控制ROW事件里记录哪些列常见三档参数值记录策略对恢复的影响full记录所有列的前后镜像恢复最完整日志最大minimal只记录必要列主键、变更列日志最小但部分工具解析后只能拿到部分列noblob记录所有列但BLOB/TEXT只在变化时记录折中方案如果你从云厂商拿到的binlog解析出来发现很多列缺失先检查一下binlog_row_image是不是minimal。这种情况下想完整恢复整行就得借助主键回查原表不能单靠binlog。5.4 binlog_row_image、GTID与查看日志的关系GTID是另一个常见话题。开启GTID后binlog里的每个事务都有一个全局唯一ID格式类似3d5e2e2c-1a2d-11ec-9a3e-00163e00123b:456。排查问题时可以先按时间粗略定位文件再用GTID精确锁定某个事务。比如mysqlbinlog --no-defaults --base64-outputdecode-rows -vv \ --include-gtids3d5e2e2c-1a2d-11ec-9a3e-00163e00123b:456 \ /data/mysql/binlog/mysql-bin.000046--include-gtids可以只拉取指定GTID的事务。--skip-gtids在前面恢复场景里提过作用是在输出时忽略GTID相关语句保证恢复脚本可以在任意实例上执行。最后再说一个常见误读binlog里的时间戳是事件写入binlog的时间不是SQL实际执行的时间。如果某个事务执行了很久比如一个大事务跑了半小时那binlog里事件的时间是事务提交或刷盘的时间点而不是语句开始执行的时间。做时间过滤时心里要留这个余量否则可能漏数据。我个人在实际操作中还有一个习惯拿到一个binlog文件不急着从头解析先看文件大小和事件总数再结合业务时间段快速定位。宁可多跑两遍mysqlbinlog也别在一次大输出里用肉眼找半天。毕竟binlog日志这玩意儿平时安静得像档案室里的老账本真到要翻它的时候往往已经是火烧眉毛的时刻了。平时找个周末开个实例故意删几行数据完整跑一遍解析和回补的流程关键时刻才能真正稳得住。