write_rows_event

每一次执行insert操作,都会产生一个write_rows_event事件。用于记录插入的数据。

EventHead

filedbytesdesc
timestamp4自1970-01-01 00:00:00以来的秒级数
eventType1当前event的类型
serverID4mysql server id
eventLength4当前even的字节长度
nextEventPos4下一个event的位置
flag2

EventBody

filedbytesdesc
tableid6table id
flags2flags
extra_infovariable
column_countvariablecolumn_count
columns_imageint((column_count + 7) / 8)
null_mapint((column_count + 7) / 8)
valuesvariablelist of value for each column
CRC324event 校验码

extra_info

保存额外的信息.

<tr>
   <td>extra_row_info</td>
   <td>An object of class Extra_row_info</td>
   <td>The class Extra_row_info will be storing the information related
       to m_extra_row_ndb_info and partition info (partition_id and
       source_partition_id). At any given time a Rows_event can have both, one
       or none of ndb_info and partition_info present as part of Rows_event.
       In case both ndb_info and partition_info are present then below will
       be the order in which they will be stored.

       @verbatim
       +----------+--------------------------------------+
       |type_code |        extra_row_ndb_info            |
       +--- ------+--------------------------------------+
       | NDB      |Len of ndb_info |Format |ndb_data     |
       | 1 byte   |1 byte          |1 byte |len - 2 byte |
       +----------+----------------+-------+-------------+

       In case of INSERT/DELETE
       +-----------+----------------+
       | type_code | partition_info |
       +-----------+----------------+
       |   PART    |  partition_id  |
       | (1 byte)  |     2 byte     |
       +-----------+----------------+

       In case of UPDATE
       +-----------+------------------------------------+
       | type_code |        partition_info              |
       +-----------+--------------+---------------------+
       |   PART    | partition_id | source_partition_id |
       | (1 byte)  |    2 byte    |       2 byte        |
       +-----------+--------------+---------------------+

       source_partition_id is used only in the case of Update_event
       to log the partition_id of the source partition.

       @endverbatim
       This is the format for any information stored as extra_row_info.
       type_code is not a part of the class Extra_row_info as it is a constant
       values used at the time of serializing and decoding the event.
  </td>
 </tr>

column_count

column_count 表示event中包含的列数。所占空间大小计算

uint net_field_length_size(const uchar *ptr) {
 if (*ptr <= 251) return 1;
 if (*ptr == 252) return 3;
 if (*ptr == 253) return 4;
 return 9;
}

uint64_t net_field_length_ll(uchar **packet) {
 const uchar *pos = *packet;
 if (*pos < 251) {
   (*packet)++;
   return (uint64_t)*pos;
 }
 if (*pos == 251) {
   (*packet)++;
   return (uint64_t)NULL_LENGTH;
 }
 if (*pos == 252) {
   (*packet) += 3;
   return (uint64_t)uint2korr(pos + 1);
 }
 if (*pos == 253) {
   (*packet) += 4;
   return (uint64_t)uint3korr(pos + 1);
 }
 (*packet) += 9; /* Must be 254 when here */
 return (uint64_t)uint8korr(pos + 1);
}

column_count = net_field_length_ll

column_image

column_image 表示event中使用的列.

如一个有4个字段的表. 占1byte. 0xf 1111 . 表示使用了所有字段.

以为是insert 指定字段时的map,但是这两条sql的 column_image 都是0xf.

insert into t(a,b,c,d) values(1,2,3,4) 
insert into t(a,c,d) values(1,3,4) 

## null_map
null_map 表示event中哪些字段为null.

如一个有4个字段的表. 占1byte. 0xf 1111 . 表示insert所有字段均不为null.

insert into t(a,b,c,d) values(1,2,3,4) null_map = 0xf 1111
insert into t(a,c,d) values(1,3,4) null_map = 0xb 1011

## values
values 存储了insert时的所有字段值.

如像该表insert一行数据

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | smallint    | YES  |     | NULL    |       |
| c2    | int         | YES  |     | NULL    |       |
| c3    | bigint      | YES  |     | NULL    |       |
| c4    | datetime    | YES  |     | NULL    |       |
| c5    | varchar(20) | YES  |     | NULL    |       |
| c6    | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

数据如下.

|    4 |    4 |    4 | 2024-01-16 15:16:39 | 44444 | 111111111122222233333444444 |

binlog中所有二进制数据如下

         04 00 04 00 00 00 04 00 00 00 00 00 00 00 99 B2 60 F4 27 05 34 34 34
34 34 1B 00 31 31 31 31 31 31 31 31 31 31 32 32 32 32 32 32 33 33 33 33 33 34
34 34 34 34 34
filedtypedesc
c1smallint04 00
c2int04 00 00 00
c3bigint0400 0000 0000 0000
c4datetime99B2 60F4 27
c5varchar(20)05 34 34 34 34 34
c6text1B00 313131313131313131313232323232323333333333343434343434

datetime 由于没有秒级以下的精度.所以只占5bytes

字段类型存储格式详解

delete_rows_event

每一次执行delete操作,都会产生一个delete_rows_event事件。用于记录删除的数据。在binlog中的二进制存储与write_rows_event一样,只是事件类型不同。

update_rows_event

每一次执行update操作,都会产生一个update_rows_event事件。用于记录更新前后的数据。在binlog中的二进制存储与write_rows_event大体一致.

EventBody

filedbytesdesc
tableid6table id
flags2flags
extra_infovariable
column_countvariablecolumn_count
columns_before_imageint((column_count + 7) / 8)
columns_after_imageint((column_count + 7) / 8)
before_null_mapint((column_count + 7) / 8)
before_valuesvariablelist of value for each column
after_null_mapint((column_count + 7) / 8)
after_valuesvariablelist of value for each column
CRC324event 校验码