Mysqlbinlog (3)

Posted on Jan 17, 2024

write_rows_event

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

EventHead

filed bytes desc
timestamp 4 自1970-01-01 00:00:00以来的秒级数
eventType 1 当前event的类型
serverID 4 mysql server id
eventLength 4 当前even的字节长度
nextEventPos 4 下一个event的位置
flag 2

EventBody

filed bytes desc
tableid 6 table id
flags 2 flags
extra_info variable
column_count variable column_count
columns_image int((column_count + 7) / 8)
null_map int((column_count + 7) / 8)
values variable list of value for each column
CRC32 4 event 校验码

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
filed type desc
c1 smallint 04 00
c2 int 04 00 00 00
c3 bigint 0400 0000 0000 0000
c4 datetime 99B2 60F4 27
c5 varchar(20) 05 34 34 34 34 34
c6 text 1B00 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

filed bytes desc
tableid 6 table id
flags 2 flags
extra_info variable
column_count variable column_count
columns_before_image int((column_count + 7) / 8)
columns_after_image int((column_count + 7) / 8)
before_null_map int((column_count + 7) / 8)
before_values variable list of value for each column
after_null_map int((column_count + 7) / 8)
after_values variable list of value for each column
CRC32 4 event 校验码