带你了解MySQL binlog event | 黑微狗的技术博客

URL

date

AI summary

slug

status

本地实验

我们通过本地尝试一条更新1000条数据的DML，来看看binlog和kafka里的消息

准备表


CREATE TABLE `user` (
  `id` bigint NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `ins_tm` datetime DEFAULT NULL,
  `upd_tm` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

准备数据

使用navicat的Data Generation功能给`user`表生成1000条数据

更新数据

我们写一条不带where条件的语句来更新user表全表数据


update user set name = CONCAT(name, '-');

观测数据

浅看binlog events

我们先来浅看一眼 binlog events


# 拿到当前的binlog文件列表
show binary logs;
# 去最新的binlog文件里查找binlog events，并指定了起始位置
show binlog events in 'binlog.000018' from 82856;

可以看到，一条更新语句产生了一系列binlog events，我们看看它是怎么构成的：

设置gtid

事务头也就是BEGIN

表结构信息 table_map

行变更

事务尾 xid commit

除了行变更事件之外，其余的事件不会受变更行数的影响，大小相对固定，且在一个事务内应该也就只有1条

而对于行变更事件的数量，会根据DML产生的行变更事件大小和参数 binlog_row_event_max_size 决定。假设binlog_row_event_max_size足够大，那么一行更新10w条数据，也可能就只是一个Event。而如果要更新的数据产生的binlog大小超过了binlog_row_event_max_size，那么会按照binlog_row_event_max_size的大小做拆分。但是如果1条数据大小已经超过binlog_row_event_max_size，那么最小也是一条数据变更拆成1个事件。

💡

binlog_row_event_max_size：对行事件的最大大小设置了软限制。默认值为8192字节，并且只能在服务器启动时更改该值。如果可能，将二进制日志中存储的行分组为大小不超过此设置值的事件。如果事件无法拆分，则可以超过最大大小。

💡

细心的你可能还发现了，我们漏掉了其中叫做Rows_query的event，这个event的info里显示了我们当时执行的SQL，其实一般Row模式只记录行变更的信息，在一些特殊场景下，我们可以通过设置参数 binlog_rows_query_log_events=ON，那么在事务开始之后会先记录当时变更的SQL，就像上图一样

💡

为什么一个update在ROW模式下需要分解成两个event：一个Table_map，一个Update_rows? 我们想象一下，一个update如果更新了10000条数据，那么对应的表结构信息是否需要记录10000次?其实是对同一个表的操作，所以这里binlog只是记录了一个Table_map用于记录表结构相关信息,而后面的Update_rows记录了更新数据的行信息。他们之间是通过table_id来联系的。【table_id不是固定的，是一个变量，占用的是table_definition_cache和table_open_cache空间（因此flush tables会造成table_id的增长）】

解析binlog events

通过show binlog events命令查到的只有事件类型，并没有里面的明细。所以我们通过mysql官方自带的mysqlbinlog命令来解析对应的binlog文件，更深度的感受一下binlog


./mysqlbinlog --verbose --base64-output=decode-rows --print-table-metadata  "/xxx/mysql_local/binlog.000018"

解释一下上面两个参数的含义：

--verbose --base64-output=decode-rows：row模式对应的行变更事件默认情况下是base64编码过的，这里是指定把这些行变更事件解码掉

--print-table-metadata：这个是把 table_map 时间里具体的表结构打印出来

我们来看看解析出来的具体结果

可以看到解析出来的结果在position上和前面浅看的都是能对应得上的。并且每一部分我都标红了。由于行变更太长了，所以这里没有截到commit。我们来看看这个结果和前面浅看的几点重要的不同点

dml sql 在这里没有显示出来，不知道是为什么，也没有找到相关参数

table_map显示出了当时的表结构信息，但是这个表结构里只有字段类型，但是没有字段名

update_rows里显示了所有影响的行的变更前后的数据，但是只有对应字段的位置信息，没有具体的字段名

补全字段信息

从这里可以看出，binlog里应该是没有字段名称信息的，并且原生的mysqlbinlog命令也没有提供补全字段名的。聪明的你肯定想到了可以借助当时的表结构信息来还原，Github上也有开源的第三方工具，不过都不是根据“当时”的表结构，而是根据解析binlog时的实时表结构来补全的。（因为”当时“的表结构并不是这个工具能根据一段binlog自主还原出来的）这里我在Github上找了一个Stars比较多开源的项目——

bingo2sql

hanchuanchuan • Updated Jul 9, 2024

使用起来也比较简单，直接根据README就可以了，这里就不赘述了

其实canal也提供了解析本地binlog文件并补全字段名的功能，不过使用起来貌似有点问题，等我调试一番没问题之后再把这种方式补上。

转存到Kafka的消息

终于到了最后一个环节，我们来看看监听到binlog转存到kafka的消息是怎样组织的。

可以看到，Rows_query事件和行变更事件都一一对应到了Kafka的消息。也就是说，消息和event是一一对应的。

💡

但是如gtid、事务头、事务尾等event，都没有在kafka里体现，那么是在哪里丢掉了呢？其中，只有事务头、事务尾、行变更和Rows_query会被加入到store里，其余都在这一层被过滤掉了。而MQClient在发消息到MQ的时候会过滤掉事务头和事务尾。

根据上文我们知道，一条行变更的event里面默认可以装8192byte的数据，所以里面是包含了多行数据变更的。我们来看看消息里是不是也是一样的

问题记录

这是生产环境使用过程中碰到过的两个问题，跟今天的话题相关，我们来看看

这两个问题的错误提示都很清晰：RecordTooLarge，记录过大

并且看堆栈信息都是在发送到kafka的时候报的，应该消息体太大了，超出了kafka对于消息大小的限制。

两个问题唯一的区别就是，第一张图是kafka-client发送消息的限制，而第二张图是kafka-server端接收消息做的限制。这两端的限制的默认值都是1M。

那么阅读到这里你是否会产生一个疑问：不是说好了行变更会按照binlog_row_event_max_size拆分事件么，并且binlog_row_event_max_size的默认值为8192bytes，这个离1M还远着呢？为什么这里消息体会超？

还记得前面说了，binlog_row_event_max_size这个是个软限制，比如当一行记录的变更产生的binlog已经超过了binlog_row_event_max_size，那么也不会再拆分了。因为行变更的event最小的粒度也只能是一行数据的变更。

One more thing

本篇文章写到结尾的时候突然想到如果一个事务里修改了多张表，那么binlog event是怎么组织的，于是又去做了个实验

看一眼心里应该就有数了，就不再做过多的分析了

扩展阅读

这里再补充介绍两种常见的event类型

Rotate Event
Stop Event

这两个event都不会出现在当前的binlog文件，只有历史binlog文件里才有。我们知道，重启mysql、binlog写满了或者手动 flush logs 都会触发新建一个binlog文件。那么在新建binlog文件之前，会在老的binlog文件里写一个事件。如果是停机的场景，会写Stop Event。其他两个场景，会写Rotate Event

binlog event有序性 http://mysql.taobao.org/monthly/2014/12/05/

MySQL的XID是什么 https://blog.51cto.com/u_13874232/5457530

MySQL binlog event 详解 https://cloud.tencent.com/developer/article/1508857