binlog-protocol

本文总阅读量次

生成新的binlog

1
flush logs;

清空binlog

1
reset master;

格式

Binlog files start with a Binlog File Header followed by a series of Binlog Event

Header + Event + Event + Event

Header

A binlog file starts with a Binlog File Header [ fe 'bin' ]

Event

EventHeader + Event

Binlog Event header

The binlog event header starts each event and is either 13 or 19 bytes long, depending on the binlog version.

1
2
3
4
5
6
7
4              timestamp
1              event type
4              server-id
4              event-size
   if binlog-version > 1:
4              log pos
2              flags
1
2
3
4
+---------+---------+---------+------------+-------------+-------+
|timestamp|type code|server_id|event_length|next_position|flags  |
|4 bytes  |1 byte   |4 bytes  |4 bytes     |4 bytes      |2 bytes|
+---------+---------+---------+------------+-------------+-------+

Fields

  • timestamp (4) – seconds since unix epoch
  • event_type (1) – see Binlog Event Type
  • server_id (4) – server-id of the originating mysql-server. Used to filter out events in circular replication.
  • event_size (4) – size of the event (header, post-header, body)
  • log_pos (4) – position of the next event
  • flags (2) – see Binlog Event Flag

Table 14.5 Binlog Versions

Binlog version MySQL Version
1 MySQL 3.23 - < 4.0.0
2 MySQL 4.0.0 - 4.0.1
3 MySQL 4.0.2 - < 5.0.0
4 MySQL 5.0.0+

目前市面上都是V4了，故EventHeader的长度可以默认为19

Binlog::FORMAT_DESCRIPTION_EVENT:

FORMAT_DESCRIPTION_EVENT 是binlog文件的第一个 Event

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
//https://sourcegraph.com/github.com/mysql/mysql-server/-/blob/libbinlogevents/src/binlog_event.cpp#L84:1
/**
   The method returns the checksum algorithm used to checksum the binary log.
   For MySQL server versions < 5.6, the algorithm is undefined. For the higher
   versions, the type is decoded from the FORMAT_DESCRIPTION_EVENT.

   @param buf buffer holding serialized FD event
   @param len netto (possible checksum is stripped off) length of the event buf

   @return  the version-safe checksum alg descriptor where zero
            designates no checksum, 255 - the orginator is
            checksum-unaware (effectively no checksum) and the actuall
            [1-254] range alg descriptor.
*/
enum_binlog_checksum_alg Log_event_footer::get_checksum_alg(const char *buf,
                                                            unsigned long len) {
  BAPI_ENTER("Log_event_footer::get_checksum_alg(const char*, unsigned long)");
  enum_binlog_checksum_alg ret = BINLOG_CHECKSUM_ALG_UNDEF;
  char version[ST_SERVER_VER_LEN];
  unsigned char version_split[3];
  BAPI_ASSERT(buf[EVENT_TYPE_OFFSET] == FORMAT_DESCRIPTION_EVENT);
  if (len > LOG_EVENT_MINIMAL_HEADER_LEN + ST_COMMON_HEADER_LEN_OFFSET + 1) {
    uint8_t common_header_len =
        buf[LOG_EVENT_MINIMAL_HEADER_LEN + ST_COMMON_HEADER_LEN_OFFSET];
    if (len >=
        static_cast<unsigned long>(common_header_len + ST_SERVER_VER_OFFSET +
                                   ST_SERVER_VER_LEN)) {
      memcpy(version, buf + common_header_len + ST_SERVER_VER_OFFSET,
             ST_SERVER_VER_LEN);
      version[ST_SERVER_VER_LEN - 1] = 0;

      do_server_version_split(version, version_split);
      if (version_product(version_split) < checksum_version_product)
        ret = BINLOG_CHECKSUM_ALG_UNDEF;
      else {
        size_t checksum_alg_offset =
            len - (BINLOG_CHECKSUM_ALG_DESC_LEN + BINLOG_CHECKSUM_LEN);
        ret =
            static_cast<enum_binlog_checksum_alg>(*(buf + checksum_alg_offset));
      }
    }
  }
  BAPI_RETURN(ret);
}

A format description event is the first event of a binlog for binlog-version 4. It describes how the other events are layed out.

Row Based Replication Events

In Row Based replication the changed rows are sent to the slave which removes side-effects and makes it more reliable. Now all statements can be sent with RBR though. Most of the time you will see RBR and SBR side by side.

1
2
3
4
5
6
7
8
9
00000004  87 77 d4 5e|0f|01 00 00  00|79 00 00 00|7d 00 00  |.w.^.....y...}..|
00000014  00|00 00|04 00|38 2e 30  2e 32 30 00 00 00 00 00  |.....8.0.20.....|
00000024  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000034  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000044  00 00 00 00 00 00 00|87  77 d4 5e|13|00 0d 00 08  |........w.^.....|
00000054  00 00 00 00 04 00 04 00  00 00|61|00 04 1a 08 00  |..........a.....|
00000064  00 00 08 08 08 02 00 00  00 0a 0a 0a 2a 2a 00 12  |............**..|
00000074  34 00 0a 28|01|63 c8 ff  8b                       |4..(.c....w.^#..|

Event Header

timestamp: 87 77 d4 5e #1590982535

event type: 0f # FORMAT_DESCRIPTION_EVENT

server-id: 01 00 00 00 #1

event-size: 79 00 00 00 #121

log pos: 7d 00 00 00 #125 position of the next event

flag: 00 00 #0

Event Body (FORMAT_DESCRIPTION_EVENT)

binlog-version: 04 00 #4

mysql-server version: 38 2e 30 2e 32 30 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 #8.0.20

create timestamp: 87 77 d4 5e #1590982535

event header length: 13 #19 固定值

event type header lengths： 00 0d 00 08 00 00 00 00 04 00 04 00 00 00 # 长度(15-1)

data length: 61 #97 ==> #check sum block = event-size - event header length - data length = 121-19-97 = 5

skip: 121(eventSize)-19(header)-2(binlogVersion)-50(serverVersion)-4(time)-1(headerLength)-14(typeHeaderLength)-1(dataLength)-5(checkSumBlock) = 25

check sum type: 01 #1 CRC32

例如：TABLE_MAP_EVENT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7
1
2
3
4
5
+=====================================+
|        | fixed part (post-header)   |
| data   +----------------------------+
|        | variable part (payload)    |
+=====================================+

参考