1 of 29

Replication Protocol

The MariaDB replication protocol governs the replication of data from a master server to downstream slave servers

1-Binlog Events

Binary log events, or binlog events, are information about data modification made to a MariaDB server instance stored in the binary log files.

Log File Structure

Each log file contains:

a 4-byte magic number, followed by a series of events describing data modifications: The magic number bytes are 0xfe 0x62 0x69 0x6e = 0xfe 'b''i''n' (this is the BINLOG_MAGIC constant).
series of binlog events.

Event Content

Each event contains the 'header' followed by 'data bytes':

The header bytes provide information about

event type
creation time
which server created the event
flags and so forth

The data bytes provide information specific to the type of event.

Note that the first event, FORMAT_DESCRIPTION_EVENT at 'position' 4, is a descriptor event that describes the format used to write events in the file.

The remaining events are interpreted according to the version.

The final event is usually a log-rotation event ROTATE_EVENT that specifies the next binary log filename or a STOP_EVENT written during server shutdown.

Note: in case of a server crash there is no terminating event (no ROTATE nor STOP)

Example MariaDB 10.1 Binlog File (Hexdump -C $file_name)

fe 62 69 6e a4 85 9e 59  0f 8c 27 00 00 f5 00 00  |.bin...Y..'.....|
00 f9 00 00 00 00 00 04  00 31 30 2e 31 2e 32 34  |.........10.1.24|
2d 4d 61 72 69 61 44 42  00 6c 6f 67 00 00 00 00  |-MariaDB.log....|
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 a4 85 9e 59 13  |..............Y.|
38 0d 00 08 00 12 00 04  04 04 04 12 00 00 dd 00  |8...............|
04 1a 08 00 00 00 08 08  08 02 00 00 00 0a 0a 0a  |................|
00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
04 13 04 00 01 ab 5b a2  e0 a4 85 9e 59 a3 8c 27  |......[.....Y..'|
00 00 2b 00 00 00 24 01  00 00 00 00 01 00 00 00  |..+...$.........|
00 00 00 00 8c 27 00 00  00 0e 00 00 00 00 00 00  |.....'..........|
...
...

Example From mysqlbinlog

DELIMITER /*!*/;
# at 4
#170824  9:52:04 server id 10124  end_log_pos 249 CRC32 0xe0a25bab 	Start: binlog v 4, server v 10.1.24-MariaDB created 170824  9:52:04 at startup
ROLLBACK/*!*/;
BINLOG '
pIWeWQ+MJwAA9QAAAPkAAAAAAAQAMTAuMS4yNC1NYXJpYURCAGxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACkhZ5ZEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAAatbouA=
'/*!*/;
# at 249
#170824  9:52:04 server id 10124  end_log_pos 292 CRC32 0xb6d8f0a8 	Gtid list [0-10124-3584]
# at 292
#170824  9:52:04 server id 10124  end_log_pos 334 CRC32 0xf2dc685f 	Binlog checkpoint log-bin.000011
# at 334
#170824  9:52:13 server id 10124  end_log_pos 376 CRC32 0xe958a0ae 	GTID 0-10124-3585 trans
...
...

_{This page is licensed: CC BY-SA / Gnu FDL}

2-Binlog Event Header

All the binlog events stored in a binary log file have a common structure:

an event header
event data

Event Header Structure, 19 Bytes

uint<4> Timestamp (creation time)
uint<1> Event Type (type_code)
uint<4> Server_id (server which created the event)
uint<4> Event Length (header + data)
uint<4> Next Event position
uint<2> Event flags

Note: if CRC32 is in use, the Event Length is 4 bytes bigger in size. The 4 bytes CRC32 are written at the end of the event (just after the last 'data' byte).

Encrypted Binlog Events

For encrypted binlog events, only the event length is in plaintext and everything else is encrypted.

To decrypt the binlog event:

Store the event length in memory
Move the timestamp into the event length position
Decrypt the whole payload except the first four bytes
Move the timestamp back to its original position
Copy the original event length back to its position

Regardless of the cipher used to encrypt the binlogs, the encrypted data will be the same size as the original unencrypted event. For events that are encrypted in CBC mode and whose length is not a multiple of the cipher block size, the final partial block is encrypted using a form of residual block termination:

Encrypt the current IV of the binlog file in ECB mode
XOR the remaining bytes with the encrypted IV

Event Type

Hex

Event type description

0x02

0x03

0x04

0x10

0x0d

0x0e

0x0f

FORMAT_DESCRIPTION_EVENT

0x13

TABLE_MAP_EVENT

0x1b

HEARTBEAT_LOG_EVENT

0xa0

ANNOTATE_ROWS_EVENT

0xa1

BINLOG_CHECKPOINT_EVENT

0xa2

GTID_EVENT

0xa3

GTID_LIST_EVENT

0xa4

START_ENCRYPTION_EVENT

0xa5

QUERY_COMPRESSED_EVENT

0xa6

WRITE_ROWS_COMPRESSED_V1

0xa7

UPDATE_ROWS_COMPRESSED_V1

0xa8

DELETE_ROWS_COMPRESSED_V1

0xa9

WRITE_ROWS_V1

0xaa

UPDATE_ROWS_V1

0xab

DELETE_ROWS_V1

Fake Events

These are generated on the fly, never written.

0x04

FAKE_ROTATE_EVENT

0xa3

FAKE_GTID_LIST_EVENT

Event Flag

Hex

Event flag description

0x0001

LOG_EVENT_BINLOG_IN_USE_FThis flag only makes sense for Format_description_log_event. It is set when the event is written, and reset when a binlog file is closed (yes, it's the only case when MySQL modifies already written part of binlog). Thus it is a reliable indicator that binlog was closed correctly.

0x0002

LOG_EVENT_FORCED_ROTATE_F(unused)

0x0004

LOG_EVENT_THREAD_SPECIFIC_F If the query depends on the thread (for example: TEMPORARY TABLE)

0x0008

LOG_EVENT_SUPPRESS_USE_F Suppress the generation of 'USE' statements before the actual statement. This flag should be set for any events that does not need the current database set to function correctly. Most notable cases are 'CREATE DATABASE' and 'DROP DATABASE'.

0x0010

LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F (unused)

0x0020

LOG_EVENT_ARTIFICIAL_F Artificial events are created arbitarily and not written to binary log.These events should not update the master log position when slave SQL thread executes them.

0x0040

LOG_EVENT_RELAY_LOG_F Events with this flag set are created by slave IO thread and written to relay log

0x0080

LOG_EVENT_IGNORABLE_F For an event, 'e', carrying a type code, that a slave,'s', does not recognize, 's' will check 'e' forLOG_EVENT_IGNORABLE_F, and if the flag is set, then 'e'is ignored. Otherwise, 's' acknowledges that it has found an unknown event in the relay log.

0x0100

LOG_EVENT_NO_FILTER_F (no description yet)

0x0200

LOG_EVENT_MTS_ISOLATE_F (no description yet)

0x8000

LOG_EVENT_SKIP_REPLICATION_F Flag set by application creating the event (with @@skip_replication);the slave will skip replication of such eventsif --replicate-events-marked-for-skip is not set to REPLICATE.This is a MariaDB flag; we allocate it from the end of the available values to reduce risk of conflict with new MySQL flags.

Event Header example of FORMAT_DESCRIPTION_EVENT

This is the first event in the binlog file at pos 4

a4 85 9e 59 0f 8c 27 00  00 f5 00 00 00 f9 00 00  ...Y..'.........
00 00 00 04 00 31 30 2e  31 2e 32 34 2d 4d 61 72  .....10.1.24-Mar
69 61 44 42 00 6c 6f 67  00 00 00 00 00 00 00 00  iaDB.log....
...
...

Interpretation of First 19 Bytes of the Event (the Event Header)

a4 85 9e 59 [4] Timestamp => 59 9e 85 a4 => 1503561124 = 2017-08-24 09:52:04
0f [1] Event Type = 0x0f = FORMAT_DESCRIPTION_EVENT
8c 27 00 00 [4] Server_id => 00 00 27 8c = 10124
f5 00 00 00 [4] Event length => 00 00 00 f5 => 245
f9 00 00 00 [4] Next Event pos => 00 00 00 f9 => 249 (pos 4 + event size)
00 00 [2] Event flags = 0

_{This page is licensed: CC BY-SA / Gnu FDL}

3-Binlog Network Stream

The binary log events stored in a binary log file can be sent over the network in order to replicate data changes from the master server (where data changes are written in binary logs) to slave servers which will apply data changes into their own databases.

The MariaDB slave replication protocol consists of:

registration phase to master
events receiving (Master sending data when changes are available).

Note: This section is related to events sending only.

Binlog Network streams are requested with COM_BINLOG_DUMP and each Binlog Event is prepended with a status byte. The data sent over network is then MariaDB network protocol (4 bytes) + 1 byte status flag + event data.

MariaDB network protocol 4 bytes are:

uint<3> packet length (The sent binlog event can be up to 2^24 - 1 - 1 data bytes)
uint<1> packet sequence byte<1>(0 to 255)

Replication protocol status byte:

uint<1> OK (0) or ERR (ff) or End of File, EOF, (fe)

Note: due to the 1 byte status flag the effective data payload is event_size + 1 This means than an event of exact 16Mbytes (2 ^ 24 - 1) cannot be sent in one transmission: it requires 2 packets instead.

packet #n:   3 bytes length + sequence + status + [event_header + (event data - 1)]
packet #n+1: 3 bytes length + sequence + last byte of the event data.

Please note that the remaining bytes of a large event transmission are always sent without a status flag and binlog event header: just network packet header + data.

Example of an Event Transmission HEARTBEAT_LOG_EVENT

T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
  23 00 00 04 00 00 00 00    00 1b 67 2b 00 00 22 00    '.........g+..&.
  00 00 ed 01 00 00 20 00    66 6f 6f 2d 62 69 6e 2e    ...... .log-bin.
  31 30 30 30 31 33 39                                  1000139

Network Replication Protocol, 5 Bytes

packet size [3] = 23 00 00 => 00 00 23 => 35 (ok byte + event size)
pkt sequence [1] = 04
OK indicator [1] = 0 (OK)

Heartbeat event

Header, 19 bytes
Content, string

_{This page is licensed: CC BY-SA / Gnu FDL}

4-Semi-Sync Replication

Regular MariaDB replication is asynchronous. MariaDB, since MariaDB 5.5, has included semisynchronous replication semi-synchronous Binlog Event.

Event Header Changes

If the user variable @rpl_semi_sync_slave is set, 2 exta bytes are added after the status byte of a binlog network stream and before the normal binlog event header.

uint<1> semi-sync indicator, always 0xef
uint<1> semi-sync flags, either 0x00 (no ACK) or 0x01 (ACK)

Note : The packet size, as in the network protocol header, is then: event_size + 1 byte status + 2 bytes semi-sync replication.

The MariaDB server sets the user variable whenever it is starting replication. For MariaDB Connector/C , the following query must be executed before the call to mariadb_rpl_open() is made to enable semi-sync replication.

SET @rpl_semi_sync_slave=1

If the semi-sync flag is set to 0x01, the master waits for a Semi Sync ACK packet from the slave and when the Semi Sync ACK is seen, the master acknowledges the client which has issued the transaction with a standard OK_Packet or a ERR_Packet.

The master can then write the transaction to the binary log and send the next events to the slave.

Note : The master only requests Semi Sync ACKs if rpl_semi_sync_master_enabled is enabled. If it is not enabled, the semi-sync flag will always be 0x00.

Semi Sync ACK Details

This event is sent by the slave only if the semi-sync flag is set to 0x01.

uint<1> semi-sync indicator, always 0xef
uint<8> the next position of received event
string binlog file name.

Note: this packet sent by the slave never includes the CRC32.

Sending an ACK when the semi-sync flag is set to 0x0 will cause an error and the connection is closed.

Example of Heartbeat Event With Semi-Sync Protocol and CRC32

We can clearly see:

2a 00 00 [3 bytes] packet size:
06 [1] sequence
00 [1] status byte = 00 => OK
ef 00 [2] bytes => semi sync indicator (0xef) and semi-sync flag (00)

the ef 00 2 bytes after the OK byte 00

T 127.0.0.1:23240 -> 127.0.0.1:41054 [AP]
  2a 00 00 06 00 ef 00 00    00 00 00 1b d9 27 00 00    *............'..
  27 00 00 00 79 04 00 00    00 00 6d 79 73 71 6c 2d    '...y.....mysql-
  62 69 6e 2e 30 30 30 30    33 34 ed ef e1 f0          bin.000034....

Example of XID_EVENT, With CRC32

The master sets the Semi-Sync ACK request in the XID_EVENT event:

We see the 2 semi sync bytes: ef and 01. The latter, being 1, means the slave server must send the Semi Sync ACK packet.

We also see in the binlog event header:

Event Type [1] = 10 XID_EVENT
Next Event pos [4] = 4a 05 00 00 => 1354

22 00 00 0c 00 ef 01 17  d0 37 5a 17 d0 37 5a 10  "............7Z.
d9 27 00 00 1f 00 00 00  4a 05 00 00 00 00 6f 00  .?......J.....o.
00 00 00 00 00 00 44 30  aa fc                    ......D0..

Example of Semi-Sync ACK

This is sent by the slave server after the XID_EVENT receiving.

We see:

the semi sync indicator [1] = 0xef, sent before anything else
the Next Event position [8] = 4a 05 00 00 00 00 00 00 => 1354 which is the next position of the XID_EVENT above
the binlog filename = mysql-bin.000034

Please note:

there is no terminating CRC32
the packet sequence now start starts from 0

19 00 00 00 ef 4a 05 00    00 00 00 00 00 6d 79 73    .....J.......mys
  71 6c 2d 62 69 6e 2e 30    30 30 30 33 34             ql-bin.000034

_{This page is licensed: CC BY-SA / Gnu FDL}

5-Replica Registration

The replica server, when properly configured with CHANGE MASTER TO ... can start MariaDB replication with the START REPLICA command.

After authentication, some COM_QUERY packets are exchanged before sending COM_REGISTER_SLAVE and COM_BINLOG_DUMP

The following COM_QUERY packets come from MariaDB 10.X slaves using GTID

SELECT UNIX_TIMESTAMP()
SHOW VARIAB LES LIKE 'SERVER_ID'
SET @master_heartbeat_period= 30000001024
SET @master_binlog_checksum= @@global.binlog_checksum
SELECT @master_binlog_checksum
SET @mariadb_slave_capability=4
SELECT @@GLOBAL.gtid_domain_id GTID registration: domain ID
SET @slave_connect_state='0-10201-9868' GTID registration: the requested GTID
SET @slave_gtid_strict_mode=0 GTID registration: strict_mode
SET @slave_gtid_ignore_duplicates=0 GTID registration: ignore_duplicates

Then COM_REGISTER_SLAVE completes the registration.

The COM_BINLOG_DUMP marks the request of binlog events stream.

Note: If semi-sync is in use, the request for the network protocol change is sent between COM_REGISTER_SLAVE and COM_BINLOG_DUMP.

Example Using 'ngrep'

COM_REGISTER_SLAVE, Semi-Sync and COM_BINLOG_DUMP

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1a 00 00 00 15 75 27 00    00 08 53 42 73 6c 61 76    .....u'...SBslav
  65 31 00 00 c9 5a 00 00    00 00 00 00 00 00          e1...Z........  

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  33 00 00 00 03 53 48 4f    57 20 56 41 52 49 41 42    3....SHOW VARIAB
  4c 45 53 20 4c 49 4b 45    20 27 72 70 6c 5f 73 65    LES LIKE 'rpl_se
  6d 69 5f 73 79 6e 63 5f    6d 61 73 74 65 72 5f 65    mi_sync_master_e
  6e 61 62 6c 65 64 27                                  nabled'         

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  01 00 00 01 02 64 00 00    02 03 64 65 66 12 69 6e    .....d....def.in
  66 6f 72 6d 61 74 69 6f    6e 5f 73 63 68 65 6d 61    formation_schema
  11 53 45 53 53 49 4f 4e    5f 56 41 52 49 41 42 4c    .SESSION_VARIABL
  45 53 11 53 45 53 53 49    4f 4e 5f 56 41 52 49 41    ES.SESSION_VARIA
  42 4c 45 53 0d 56 61 72    69 61 62 6c 65 5f 6e 61    BLES.Variable_na
  6d 65 0d 56 41 52 49 41    42 4c 45 5f 4e 41 4d 45    me.VARIABLE_NAME
  0c 08 00 40 00 00 00 fd    01 00 00 00 00 5d 00 00    ...@.........]..
  03 03 64 65 66 12 69 6e    66 6f 72 6d 61 74 69 6f    ..def.informatio
  6e 5f 73 63 68 65 6d 61    11 53 45 53 53 49 4f 4e    n_schema.SESSION
  5f 56 41 52 49 41 42 4c    45 53 11 53 45 53 53 49    _VARIABLES.SESSI
  4f 4e 5f 56 41 52 49 41    42 4c 45 53 05 56 61 6c    ON_VARIABLES.Val
  75 65 0e 56 41 52 49 41    42 4c 45 5f 56 41 4c 55    ue.VARIABLE_VALU
  45 0c 08 00 00 08 00 00    fd 01 00 00 00 00 05 00    E...............
  00 04 fe 00 00 22 00 20    00 00 05 1c 72 70 6c 5f    .....". ....rpl_
  73 65 6d 69 5f 73 79 6e    63 5f 6d 61 73 74 65 72    semi_sync_master
  5f 65 6e 61 62 6c 65 64    02 4f 4e 05 00 00 06 fe    _enabled.ON.....
  00 00 22 00                                           ..". 

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1c 00 00 00 03 53 45 54    20 40 72 70 6c 5f 73 65    .....SET @rpl_se
  6d 69 5f 73 79 6e 63 5f    73 6c 61 76 65 3d 20 31    mi_sync_slave= 1

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 12 34 06 00    00 02 00 75 27 00 00 6d    .....4.....u'..m
  79 73 71 6c 2d 62 69 6e    2e 30 30 30 30 33 34       ysql-bin.000034

In the example we clearly see that these two COM_QUERY commands:

SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled'
SET @rpl_semi_sync_slave= 1

are sent just after COM_REGISTER_SLAVE and before COM_BINLOG_DUMP.

Complete Example with GTID Registration (Up to COM_BINLOG_DUMP Request), No Semi-Sync

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  5d 00 00 00 0a 35 2e 35    2e 35 2d 31 30 2e 32 2e    ]....5.5.5-10.2.
  31 30 2d 4d 61 72 69 61    44 42 2d 6c 6f 67 00 22    10-MariaDB-log."
  00 00 00 7d 2e 6a 4f 2c    2c 36 6a 00 fe f7 08 02    ...}.jO,,6j.....
  00 bf 81 15 00 00 00 00    00 00 07 00 00 00 38 74    ..............8t
  60 64 54 59 44 28 38 24    48 7c 00 6d 79 73 71 6c    `dTYD(8$H|.mysql
  5f 6e 61 74 69 76 65 5f    70 61 73 73 77 6f 72 64    _native_password
  00                                                    .               

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  a9 00 00 01 05 a2 38 80    70 03 00 40 08 00 00 00    ......8.p..@....
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 6d 73 61 6e    64 62 6f 78 00 14 52 42    ....msandbox..RB
  0b e8 ae 56 ec ff ef 1f    1f 14 51 1d 4a 47 f4 32    ...V......Q.JG.2
  56 74 6d 79 73 71 6c 5f    6e 61 74 69 76 65 5f 70    Vtmysql_native_p
  61 73 73 77 6f 72 64 00    54 03 5f 6f 73 05 4c 69    assword.T._os.Li
  6e 75 78 0c 5f 63 6c 69    65 6e 74 5f 6e 61 6d 65    nux._client_name
  08 6c 69 62 6d 79 73 71    6c 04 5f 70 69 64 05 33    .libmysql._pid.3
  30 30 31 33 0f 5f 63 6c    69 65 6e 74 5f 76 65 72    0013._client_ver
  73 69 6f 6e 07 31 30 2e    32 2e 31 30 09 5f 70 6c    sion.10.2.10._pl
  61 74 66 6f 72 6d 06 78    38 36 5f 36 34             atform.x86_64   

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 02 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  18 00 00 00 03 53 45 4c    45 43 54 20 55 4e 49 58    .....SELECT UNIX
  5f 54 49 4d 45 53 54 41    4d 50 28 29                _TIMESTAMP()    

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 26 00 00    02 03 64 65 66 00 00 00    .....&....def...
  10 55 4e 49 58 5f 54 49    4d 45 53 54 41 4d 50 28    .UNIX_TIMESTAMP(
  29 00 0c 3f 00 11 00 00    00 08 80 00 00 00 00 05    )..?............
  00 00 03 fe 00 00 02 00    0b 00 00 04 0a 31 35 31    .............151
  33 36 38 34 33 38 36 05    00 00 05 fe 00 00 02 00    3684386.........

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  20 00 00 00 03 53 48 4f    57 20 56 41 52 49 41 42     ....SHOW VARIAB
  4c 45 53 20 4c 49 4b 45    20 27 53 45 52 56 45 52    LES LIKE 'SERVER
  5f 49 44 27                                           _ID'            

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 02 64 00 00    02 03 64 65 66 12 69 6e    .....d....def.in
  66 6f 72 6d 61 74 69 6f    6e 5f 73 63 68 65 6d 61    formation_schema
  11 53 45 53 53 49 4f 4e    5f 56 41 52 49 41 42 4c    .SESSION_VARIABL
  45 53 11 53 45 53 53 49    4f 4e 5f 56 41 52 49 41    ES.SESSION_VARIA
  42 4c 45 53 0d 56 61 72    69 61 62 6c 65 5f 6e 61    BLES.Variable_na
  6d 65 0d 56 41 52 49 41    42 4c 45 5f 4e 41 4d 45    me.VARIABLE_NAME
  0c 08 00 40 00 00 00 fd    01 00 00 00 00 5d 00 00    ...@.........]..
  03 03 64 65 66 12 69 6e    66 6f 72 6d 61 74 69 6f    ..def.informatio
  6e 5f 73 63 68 65 6d 61    11 53 45 53 53 49 4f 4e    n_schema.SESSION
  5f 56 41 52 49 41 42 4c    45 53 11 53 45 53 53 49    _VARIABLES.SESSI
  4f 4e 5f 56 41 52 49 41    42 4c 45 53 05 56 61 6c    ON_VARIABLES.Val
  75 65 0e 56 41 52 49 41    42 4c 45 5f 56 41 4c 55    ue.VARIABLE_VALU
  45 0c 08 00 00 08 00 00    fd 01 00 00 00 00 05 00    E...............
  00 04 fe 00 00 22 00 10    00 00 05 09 73 65 72 76    ....."......serv
  65 72 5f 69 64 05 31 30    32 30 31 05 00 00 06 fe    er_id.10201.....
  00 00 22 00                                           ..".            

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  2a 00 00 00 03 53 45 54    20 40 6d 61 73 74 65 72    *....SET @master
  5f 68 65 61 72 74 62 65    61 74 5f 70 65 72 69 6f    _heartbeat_perio
  64 3d 20 33 30 30 30 30    30 30 31 30 32 34          d= 30000001024  

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  36 00 00 00 03 53 45 54    20 40 6d 61 73 74 65 72    6....SET @master
  5f 62 69 6e 6c 6f 67 5f    63 68 65 63 6b 73 75 6d    _binlog_checksum
  3d 20 40 40 67 6c 6f 62    61 6c 2e 62 69 6e 6c 6f    = @@global.binlo
  67 5f 63 68 65 63 6b 73    75 6d                      g_checksum      

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1f 00 00 00 03 53 45 4c    45 43 54 20 40 6d 61 73    .....SELECT @mas
  74 65 72 5f 62 69 6e 6c    6f 67 5f 63 68 65 63 6b    ter_binlog_check
  73 75 6d                                              sum             

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 2d 00 00    02 03 64 65 66 00 00 00    .....-....def...
  17 40 6d 61 73 74 65 72    5f 62 69 6e 6c 6f 67 5f    .@master_binlog_
  63 68 65 63 6b 73 75 6d    00 0c 08 00 ff ff ff 00    checksum........
  fa 00 00 27 00 00 05 00    00 03 fe 00 00 02 00 06    ...'............
  00 00 04 05 43 52 43 33    32 05 00 00 05 fe 00 00    ....CRC32.......
  02 00                                                 ..              

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  20 00 00 00 03 53 45 54    20 40 6d 61 72 69 61 64     ....SET @mariad
  62 5f 73 6c 61 76 65 5f    63 61 70 61 62 69 6c 69    b_slave_capabili
  74 79 3d 34                                           ty=4            

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1f 00 00 00 03 53 45 4c    45 43 54 20 40 40 47 4c    .....SELECT @@GL
  4f 42 41 4c 2e 67 74 69    64 5f 64 6f 6d 61 69 6e    OBAL.gtid_domain
  5f 69 64                                              _id             

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 2d 00 00    02 03 64 65 66 00 00 00    .....-....def...
  17 40 40 47 4c 4f 42 41    4c 2e 67 74 69 64 5f 64    .@@GLOBAL.gtid_d
  6f 6d 61 69 6e 5f 69 64    00 0c 3f 00 15 00 00 00    omain_id..?.....
  08 a0 00 00 00 00 05 00    00 03 fe 00 00 02 00 02    ................
  00 00 04 01 30 05 00 00    05 fe 00 00 02 00          ....0.........  

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  28 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    (....SET @slave_
  63 6f 6e 6e 65 63 74 5f    73 74 61 74 65 3d 27 30    connect_state='0
  2d 31 30 32 30 31 2d 39    38 36 38 27                -10201-9868'    

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1e 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    .....SET @slave_
  67 74 69 64 5f 73 74 72    69 63 74 5f 6d 6f 64 65    gtid_strict_mode
  3d 30                                                 =0              

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  24 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    $....SET @slave_
  67 74 69 64 5f 69 67 6e    6f 72 65 5f 64 75 70 6c    gtid_ignore_dupl
  69 63 61 74 65 73 3d 30                               icates=0        

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 15 75 27 00    00 09 73 6c 61 76 65 5f    .....u'...slave_
  6e 5f 31 00 00 c9 5a 00    00 00 00 00 00 00 00       n_1...Z........ 

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 12 34 06 00    00 02 00 75 27 00 00 6d    .....4.....u'..m
  79 73 71 6c 2d 62 69 6e    2e 30 30 30 30 33 34       ysql-bin.000034

Events Transmission After COM_BINLOG_DUMP.

The MariaDB 10.x Master always sends, after the COM_BINLOG_DUMP:

FAKE_ROTATE_EVENT
FORMAT_DESCRIPTION_EVENT: Next Pos in the header is set to 0 if not requesting binlog file form the beginning and GTID is not in use otherwise Next Pos is related to next event after FDE
FAKE_GTID_LIST_EVENT with latest GTID information.

After those first events, the master sends events related to changes in database to the connected replica binlog. The replica is just waiting for new events from master.

Complete Example of Event Transmission With CRC32

T 127.0.0.1:23240 -> 127.0.0.1:42219 [AP]
  30 00 00 01 00 00 00 00    00 04 d9 27 00 00 2f 00    0..........'../.
  00 00 00 00 00 00 20 00    04 00 00 00 00 00 00 00    ...... .........
  6d 79 73 71 6c 2d 62 69    6e 2e 30 30 30 30 33 34    mysql-bin.000034
  d5 3f ea d7 fd 00 00 02    00 fb cc 37 5a 0f d9 27    .?.........7Z..'
  00 00 fc 00 00 00 00 01    00 00 00 00 04 00 31 30    ..............10
  2e 32 2e 31 30 2d 4d 61    72 69 61 44 42 2d 6c 6f    .2.10-MariaDB-lo
  67 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    g...............
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 13 38 0d 00    08 00 12 00 04 04 04 04    .....8..........
  12 00 00 e4 00 04 1a 08    00 00 00 08 08 08 02 00    ................
  00 00 0a 0a 0a 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 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 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 04 13 04    00 0d 08 08 08 0a 0a 0a    ................
  01 17 0b 12 63 3c 00 00    03 00 fb cc 37 5a a3 d9    ....c<......7Z..
  27 00 00 3b 00 00 00 3b    01 00 00 00 00 02 00 00    '..;...;........
  00 00 00 00 00 01 00 00    00 1e 00 00 00 00 00 00    ................
  00 00 00 00 00 d9 27 00    00 86 26 00 00 00 00 00    ......'...&.....
  00 b6 33 8a 22 2c 00 00    04 00 fb cc 37 5a a1 d9    ..3.",......7Z..
  27 00 00 2b 00 00 00 66    01 00 00 00 00 10 00 00    '..+...f........
  00 6d 79 73 71 6c 2d 62    69 6e 2e 30 30 30 30 33    .mysql-bin.00003
  34 16 1f fe 3f 2c 00 00    05 00 00 00 00 00 a3 d9    4...?,..........
  27 00 00 2b 00 00 00 34    06 00 00 20 00 01 00 00    '..+...4... ....
  00 00 00 00 00 d9 27 00    00 8c 26 00 00 00 00 00    ......'...&.....
  00 4a 01 94 22 2b 00 00    06 00 94 fd 38 5a a2 d9    .J.."+......8Z..
  27 00 00 2a 00 00 00 5e    06 00 00 08 00 8d 26 00    '..*...^......&.
  00 00 00 00 00 00 00 00    00 29 00 00 00 00 00 00    .........)......
  22 87 c0 61 4c 00 00 07    00 94 fd 38 5a 02 d9 27    "..aL......8Z..'
  00 00 4b 00 00 00 a9 06    00 00 00 00 21 00 00 00    ..K.........!...
  00 00 00 00 00 00 00 1a    00 00 00 00 00 00 01 00    ................
  00 20 54 00 00 00 00 06    03 73 74 64 04 08 00 08    . T......std....
  00 08 00 00 66 6c 75 73    68 20 74 61 62 6c 65 73    ....flush tables
  6e c8 89 60                                           n..`

We can see:

FAKE_ROTATE_EVENT packet: 30 00 00 01 ... d5 3f ea d7
FORMAT_DESCRIPTION_EVENT packet: fd 00 00 02 00 ... 17 0b 12 63 FDE size is fc 00 00 00 (252) Next Pos in FDE is 00 01 00 00 = >256 = 4 + FDE size (252)
FAKE GTID_LIST_EVENT packet : 3c 00 00 03 00 ... b6 33 8a 22
BINLOG_CHECKPOINT EVENT packet: 2c 00 00 04 ... 16 1f fe 3f
GTID_LIST_EVENT packet: 2c 00 00 05 ... 4a 01 94 22
GTID_EVENT packet: 2b 00 00 06 ... 22 87 c0 61
QUERY_EVENT packet: 4c 00 00 07 ... 6e c8 89 60

_{This page is licensed: CC BY-SA / Gnu FDL}

ANNOTATE_ROWS_EVENT

ANNOTATE_ROWS_EVENT events accompany row events and describe the query which caused the row event.

You can enable this with --binlog-annotate-row-events (default on from MariaDB 10.2.4).

In the binary log, each Annotate_rows event precedes the corresponding Table map event.

For additional information refer to the annotate_rows_log_event documentation.

Note: the master server sends ANNOTATE_ROWS_EVENT events only if the Slave server connects with the BINLOG_SEND_ANNOTATE_ROWS_EVENT flag (value is 2) in the COM_BINLOG_DUMP Slave Registration phase.

Header

Event Type is 160 (0xa0)

Fields

string The SQL statement (not null-terminated)

Complete Example with CRC32

ee b7 15 5a a0 01 00 00  00 36 00 00 00 80 0b 00  ...Z.....6......
00 00 00 69 6e 73 65 72  74 20 69 6e 74 6f 20 74  ...insert into t
65 73 74 2e 74 34 20 76  61 6c 75 65 73 28 31 30  est.t4 values(10
30 29 6d 4c 42 33                                 0)mLB3

_{This page is licensed: CC BY-SA / Gnu FDL}

BEGIN_LOAD_QUERY_EVENT

This event is written into the binary log file for LOAD DATA INFILE events if the server variable binlog_mode was set to "STATEMENT".

Header

Event Type = 0x11

Fields

Fixed data part:

uint<4> The ID of the file

Variable data part:

byte Null terminated data block.

Example

TODO: Add an example

_{This page is licensed: CC BY-SA / Gnu FDL}

BINLOG_CHECKPOINT_EVENT

Binlog Checkpoint Event, Event Type is 161 (0xa1) This event specifies a binlog file such that XA crash recovery can start from that file.

Note: there can be more than one in a binlog file.

Header

Event type is 161 (0xa1)

Fields

uint<4> Log filename length
string Log filename

Example Without CRC32

12 ad 26 5a a1 84 27 00  00 27 00 00 00 47 01 00  ..&Z..'..'...G..
00 00 00 10 00 00 00 6d  79 73 71 6c 2d 62 69 6e  .......mysql-bin
2e 30 30 30 30 36 32                              .000062

Header, 19 Bytes

Event Timestamp = 12 ad 26 5a
Event Type = a1 => 161
Server_id = 84 27 00 00 => 00 00 27 84 = 10116
Event Size = 27 00 00 00 => 00 00 00 27 = 39
Next Pos = 47 01 00 00 => 00 00 01 47 = 327
Flags = 00 => 0

Content, Variable Size

filename length = 10 00 00 00 = >00 00 00 10 => 16
filename = mysql-bin.000062

_{This page is licensed: CC BY-SA / Gnu FDL}

COM_BINLOG_DUMP

This is a command the slave sends to the master after COM_REGISTER_SLAVE.

The master server sends the binlog events from the requested file and position, or if GTID registration is in use, from the GTID value set in the earlier registration phase.

The payload is:

uint<1> command (COM_BINLOG_DUMP = 0x12)
uint<4> The requested binlog position
uint<2> Flags
uint<4> Slave server_id
string The requested binlog file name

Note

Flags, usually set to 0. It can be set to BINLOG_SEND_ANNOTATE_ROWS_EVENT (0x02) if the slave server wants to receive the MariaDB 10 ANNOTATE_ROWS events. It can also be set to BINLOG_DUMP_NON_BLOCK (1): in the case the slave is receiving an EOF packet after last event sent by the master.
Requested binlog position can be 4 when registering to master server for the very first time or when requesting events from a particular binlog file from the beginning of it.
The requested binlog file can empty when registering for the very first time if master log file is unknown or with GTID registration (not required).
After sending events to the replica the server kills connection.

When replication resumes or it is restarted (STOP SLAVE; START SLAVE) the slave server always sends the latest binlog file name and position even if GTID registration is in place.

Example of COM_BINLOG_DUMP

1b 00 00 00 12 34 06 00    00 02 00 75 27 00 00 6d    .....4.....u'..m
  79 73 71 6c 2d 62 69 6e    2e 30 30 30 30 33 34       ysql-bin.000034

After 4 bytes network protocol header we can see:

command [1] = 12
requested binlog position [4] = 34 06 00 00 => 00 00 06 34 = 1588
flags [2] = 02 00 => 2 = BINLOG_SEND_ANNOTATE_ROWS_EVENT
binlog file[n] = mysql-bin.000034

_{This page is licensed: CC BY-SA / Gnu FDL}

COM_REGISTER_SLAVE

This command is sent by the slave server in order to start MariaDB replication and should be sent before requesting binlog events with COM_BINLOG_DUMP.

The payload is:

uint<1> command (COM_REGISTER_SLAVE = 0x15)
uint<4> Slave server_id
uint<1> Slave hostname length
string Hostname
uint<1> Slave username len
string Username
uint<1> Slave password len
string Slave password
uint<2> Slave connection port
uint<4> Replication rank
uint<4> Master server id

Note:

Slave hostname, slave user, slave password and slave port are usually not set. Some slave replication parameters can be used for such settings (report_host, report_port etc).
Replication rank is not set.
Master server id is not set as well.

Example of COM_REGISTER_SLAVE

The slave server is configured with:

server-id=10101
report-host=slave_n_1
report-port=23241

1b 00 00 00 15 75 27 00    00 09 73 6c 61 76 65 5f    .....u'...slave_
 6e 5f 31 00 00 c9 5a 00    00 00 00 00 00 00 00       n_1...Z.......

We can see from the example:

server_id [4] = 75 27 00 00 => 10101
hostname_len [1] = 09
hostname[n] = slave_n_1 (9 bytes)
username len [1] = 0 (not set)
password len [1] = 0 (not set)
slave port [2] = c9 5a => 23241
rank [4] = 0
master server id = 0

_{This page is licensed: CC BY-SA / Gnu FDL}

EXECUTE_LOAD_QUERY_EVENT

This event is written into the binary log file for LOAD DATA INFILE events. The event format is similar to a QUERY_EVENT except that it has extra static fields.

Header

Event Type = 0x12

Fields

Fixed data part:

uint<4> The ID of the thread that issued this statement on the master.
uint<4> The time in seconds that the statement took to execute.
uint<1> The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.
uint<2> The error code resulting from execution of the statement on the master.
uint<2> The length of the status variable block.
uint<4> The ID of the loaded file
uint<4> Offset from the start of the statement to the beginning of the filename
uint<4> Offset from the start of the statement to the end of the filename
uint<1> How LOAD DATA INFILE handles duplicates (0x0: error, 0x1: ignore, 0x2: replace).

Variable data part:

byte Zero or more status variables. Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific. The number of bytes 'n' is the length of the status variable block (read in fixed data part)
string The default database name (null-terminated).
string The SQL statement. By subtraction the size of the statement can be known.

Example

TODO: Add an example

_{This page is licensed: CC BY-SA / Gnu FDL}

Fake GTID_LIST event

This event is sent by master server to the registering slave and it's sent only once, afterFormat Description Event

Note: the fake GTID_LIST event is not written in the binlog file. It's created by the master and sent to new connected slave before any "real" binlog event.

Header

Event type is set to 163 (0xa3)
Time stamp set to 0
NextPos tells which is the binlog position of next event
Flags are set to ARTIFICIAL (0x20)

Content

The content is the same as the "real" GTID_LIST.

of GTIDs
domain_id
server_id
sequence
...

_{This page is licensed: CC BY-SA / Gnu FDL}

Fake ROTATE_EVENT

When a slave server connects to a MariaDB 10 master server, the first binlog event sent is Fake ROTATE_EVENT. This event is similar to ROTATE_EVENT but it's artificial and its purpose is to tell the slave server which is the binlog file name of the master. This matters of course when the slave connects with the GTID option (no filename is given) or when using file and pos with empty file name (usually file='' and pos = 4).

The Event Type is set ROTATE_EVENT (0x4)

Note: the fake ROTATE_EVENT event is not written in the binlog file. It's created by the master and sent to new connected slave before FORMAT_DESCRIPTION_EVENT

Header

Timestamp set to 0
Event Tye is ROTATE_EVENT
Next Pos is set to 0
Flags are set to LOG_ARTIFICIAL_F (0x20)

Content

The content is the same as ROTATE_EVENT.

pos = the requested pos from slave, usually 4
filename = the master binlog filename

If it is the first fake rotate event and global server variable @@binlog_checksum was set to CRC32:

crc32_checksum (4 Bytes)

_{This page is licensed: CC BY-SA / Gnu FDL}

FORMAT_DESCRIPTION_EVENT

This is a descriptor event that is written to the beginning of a binary log file, at position 4 (after the 4 magic number bytes)

The whole event written to disk is byte<19> event header + data fields

Header

The Event Type is 15 (0x0f)

Fields

uint<2> The binary log format version. This is 4 in MariaDB 10 and up.
string<50> The MariaDB server version (example: 10.2.1-debug-log), padded with 0x00 bytes on the right.
uint<4> Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.
uint<1> The header length. This length - 19 gives the size of the extra headers field at the end of the header for other events.
byte Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about. The value 'n' comes from the following formula:

n = event_size - header length - offset (2 + 50 + 4 + 1) - checksum_algo - checksum

uint<1> Checksum Algorithm Type
uint<4> CRC32 4 bytes (value matters only if checksum algo is CRC32)

Example FDE of MariaDB 10.2.10 With CRC32

4d af 15 5a 0f 01 00 00  00 fc 00 00 00 00 01 00  M..Z............
00 00 00 04 00 31 30 2e  32 2e 31 30 2d 4d 61 72  .....10.2.10-Mar
69 61 44 42 2d 6c 6f 67  00 00 00 00 00 00 00 00  iaDB-log.......
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 4d af 15 5a 13  ...........M..Z.
00 12 00 04 04 04 04 12  00 00 e4 00 04 1a 08 00  ................
00 00 08 08 08 02 00 00  00 0a 0a 0a 00 00 00 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
00 00 00 00 00 00 00 00  00 00 00 00 04 13 04 00  ................
0d 08 08 08 0a 0a 0a 01  d6 ce 13 e2              ............

Header, 19 Bytes

timestamp => 4d af 15 5a
type = 0f => 15
server_id = 1
Event Size = fc => 252
Next Pos = 00 01 00 00 => 00 00 01 00 => 256
Flags = 00 => 0

Content, Variable Size Depending on MariaDB Versions

format version = 04 00 => 4
server's version = 10.2.10-MariaDB-log .... [50 bytes]
create time = 4d af 15 5a
header_length = 13 => 19
event_types array[252 - 19 - (2 + 50 + 4 +1) - 1 - 4] = 171 supported events
checksum_algo = 01 => 1 (CRC32)
CRC32 bytes = d6 ce 13 e2

Example FDE of MariaDB 10.1.16 With CRC32

12 ad 26 5a 0f 84 27 00  00 f5 00 00 00 f9 00 00  ..&Z..'.........
00 01 00 04 00 31 30 2e  31 2e 31 36 2d 4d 61 72  .....10.1.16-Mar
69 61 44 42 00 6c 6f 67  00 00 00 00 00 00 00 00  iaDB.log........
00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ................
00 00 00 00 00 00 00 12  ad 26 5a 13 38 0d 00 08  .........&Z.8...
00 12 00 04 04 04 04 12  00 00 dd 00 04 1a 08 00  ................
00 00 08 08 08 02 00 00  00 0a 0a 0a 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 04 13 04 00  ................
00 2b 91 c2 91

Header, 19 Bytes

timestamp => 12 ad 26 5aa
type = 0f => 15
server_id = 84 27 00 00 => 00 00 27 84 => 10116
Event Size = fc => 245
Next Pos = f9 00 00 00 => 00 00 00 f9 => 249
Flags = 00 => 0

Content, Variable Size Depending on MariaDB Versions

format version = 04 00 => 4
server's version = 10.1.16-MariaDB.log .... [50 bytes]
create time = 12 ad 26 5a
header_length = 13 => 19
event_types array[245 - 19 - (2 + 50 + 4 +1) - 1 - 4] = 164 supported events
checksum_algo = 0 => 0 (NONE)
CRC32 bytes = 2b 91 c2 91 (useless)

_{This page is licensed: CC BY-SA / Gnu FDL}

GTID_EVENT

For global transaction ID, used to start a new transaction event group, instead of the old BEGIN query event, and also to mark stand-alone (ddl).

GTID_EVENT, event type is 162 (0xa2)

Event Header

Type[1] = 0xa2
Flags[2] = 08 00 => LOG_EVENT_SUPPRESS_USE_F

Fields

uint<8> GTID sequence
uint<4> Replication Domain ID
uint<1> Flags

if flag & FL_GROUP_COMMIT_ID

uint<8> commit_id

else if flag & (FL_PREPARED_XA | FL_COMPLETED_XA)

uint<4> format_id
uint<1> gtid_length
uint<1> bqual_length
bytexid, where n is sum of gtrid and bqual lengths

else

uint<6> 0

Flags

FL_STANDALONE

Set when there is no terminating COMMIT event.

FL_GROUP_COMMIT_ID

Set when event group is part of a group commit on the master. Groups with same commit_id are part of the same group commit.

FL_TRANSACTIONAL

Set for an event group that can be safely rolled back (no MyISAM, eg.).

FL_ALLOW_PARALLEL

Reflects the (negation of the) value of @@SESSION.skip_parallel_replication at the time of commit.

FL_WAITED

Set if a row lock wait (or other wait) is detected during the execution of the transaction.

FL_DDL

Set for event group containing DDL.

FL_PREPARED_XA

Set for XA transaction.

FL_COMPLETED_XA

128

XA transaction completed (committed or rolled back)

Transaction Example from mysqlbinlog Utility

BEGIN
#171205 18:22:52 server id 10124  end_log_pos 652 CRC32 0x23c8d337 	GTID 0-10124-9884 trans
TBALE_MAP
#171205 18:22:52 server id 10124  end_log_pos 752 CRC32 0x52601513 	Table_map: `test`.`t4` mapped to number 92
WRITE
#171205 18:22:52 server id 10124  end_log_pos 790 CRC32 0x8869c123 	Write_rows: table id 92 flags: STMT_END_F
COMMIT
#171205 18:22:52 server id 10124  end_log_pos 821 CRC32 0x15517636 	Xid = 42004

Standalone Event DDL (FLUSH TABLES) from mysqlbinlog Utility

#171205 17:44:27 server id 10124  end_log_pos 535 CRC32 0x309a668e 	GTID 0-10124-9883 ddl
#171205 17:44:27 server id 10124  end_log_pos 610 CRC32 0xda151470 	Query	thread_id=819	...

Example GTID_EVENT with DDL and CRC32

eb cc 26 5a a2 8c 27 00  00 2a 00 00 00 17 02 00  ...&Z..'..*.....
00 08 00 9b 26 00 00 00  00 00 00 00 00 00 00 29  ...&..........).
00 00 00 00 00 00 8e 66  9a 30                    ......f.0.

Content

GTID seq[8] = 9b 26 00 00 00 00 00 00 => 9883
domain id[4] = 00 00 00 00 00 => 0
flags[1] = 29 => 41 (FL_DDL =32 + FL_ALLOW_PARALLEL=8 + FL_STANDALONE=1)
commit_id[6] = 00 00 00 00 00 00 = 0
CRC32[4] = 8e 66 9a 30

Example GTID_EVENT With a Transaction and CRC32

ec d5 26 5a a2 8c 27 00  00 2a 00 00 00 8c 02 00 ..&Z..'..*......
00 08 00 9c 26 00 00 00  00 00 00 00 00 00 00 0c ....&...........
00 00 00 00 00 00 37 d3  c8 23                   ......7..#

Content

GTID seq[8] = 9c 26 00 00 00 00 00 00 => 9884
domain id[4] = 00 00 00 00 00 => 0
flags[1] = 0c => 12 (FL_ALLOW_PARALLEL=8 + FL_TRANSACTIONAL=4)
commit_id[6] = 00 00 00 00 00 00 = 0
CRC32[4] = 37 d3 c8 23

_{This page is licensed: CC BY-SA / Gnu FDL}

GTID_LIST_EVENT

Logged in every binlog to record the current replication state. Consists of the last GTID seen for each replication domain.

The Global Transaction ID, GTID for short, consists of three components:

replication domain ID
master server ID
sequence ID

It's represented as three numbers separated with dashes '-' For example:1-1222-1011

It's usually written after the Format Description Event, if binary log encryption is enabled it is written after the Start Encryption Event.

Note: In case of encrypted binlog files (encrypt_binlog is set to ON), this event is written just after the START_ENCRYPTION_EVENT

Header

Event type is 163 (0xa3)

Fields

uint<4> Number of GTIDs
for (i=0; i < gtid_count; i++)
- uint<4> Replication Domain ID
- uint<4> Server_ID
- uint<8> GTID sequence

The minimum content size for 1 GTID is: 4 + (4 + 4 + 8) * 1 = 20 bytes

Example With 1 GTID, With CRC32

From the mysqlbinlog utility:

170824 9:52:04 server id 10124 end_log_pos 292 CRC32 0xb6d8f0a8 Gtid list [0-10124-3584]

a4 85 9e 59 a3 8c 27 00  00 2b 00 00 00 24 01 00  ...Y..'..+...$..
00 00 00 01 00 00 00 00  00 00 00 8c 27 00 00 00  ............'...
0e 00 00 00 00 00 00 a8  f0 d8 b6                 ..........

Header, 19 Bytes

Event Time = a4 85 9e 59 ===> 2017-08-24 9:52:04
Event Type = a3 => 163
Server_id = 8c 27 00 00 => 00 00 27 8c => 10124
Event Size = 2b => 43 (header[19] + 1 GTID(20 bytes) + CRC32[4]
Next Pos = 24 01 00 00 => 00 00 01 24 => 292
Flags = 00 => 0

Content, Variable Size, is (4 + (4 + 4 + 8 ) * n_GTIDs) Bytes

The content example with one GTID is 20 bytes + 4 bytes CRC32:

Number of GTIDs[4] = 01 00 00 00 => 1
GTID[0] replication_domain[4] = 00 00 00 00 => 0
GTID[0] Server_id[4] = 8c 27 00 00 => 00 00 27 8c => 10124
GTID[0] Sequence[8] = 00 0e 00 00 00 00 00 00 ===> 3584
crc32[4] = a8 f0 d8 b6 => b6 d8 f0 a8 => 0xb6d8f0a8

_{This page is licensed: CC BY-SA / Gnu FDL}

HEARTBEAT_LOG_EVENT

This event does not appear in the binary log. It's only sent over the network by a master to a slave server to let it know that the master is still alive, and is only sent when the master has no binlog events to send to slave servers.

This event is never written into the binary log file

Header

Timestamp is set to 0
Next position is set to last pos
Type is set to HEARTBEAT_EVENT (0x1b)

Fields

string The current master binary log name

Example of Transmission (Without CRC32)

T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
  23 00 00 04 00 00 00 00    00 1b 67 2b 00 00 22 00    '.........g+..&.
  00 00 ed 01 00 00 20 00    66 6f 6f 2d 62 69 6e 2e    ...... .log-bin.
  31 30 30 30 31 33 39                                  1000139

Network Replication protocol, 5 bytes

packet size [3] = 23 00 00 => 00 00 23 => 35 (ok byte + event size)
pkt sequence [1] = 04
OK indicator [1] = 0 (OK)

Heartbeat event

Header

Timestamp [4] = 00 00 00 00 => 0
Event Type [1] = 1b => 27
Server_id [4] = 67 2b 00 00 => 00 00 2b 67 => 111111
Event Size [4] = 22 00 00 00 => 00 00 00 26 => 34 (header + data)
Next_pos [4] = ed 01 00 00 => 00 00 01 ed => 493
Flags [2] == 20 00 => 00 20 = > 32

Content, string

log-bin.1000139

_{This page is licensed: CC BY-SA / Gnu FDL}

INTVAR_EVENT

A INTVAR_EVENT is written every time a statement uses an auto increment column or LAST_INSERT_ID() function.

Header

Event Type is 5 (0x05)

Fields

uint<1> Type
uint<8> Value

Type

0x00

invalid value

0x01

LAST_INSERT_ID

0x02

Insert id (auto_increment)

Example From mysqlbinlog Utility, CRC32

# at 738
#180610 11:20:56 server id 1  end_log_pos 770 CRC32 0xf5a23f2d 	Intvar
SET LAST_INSERT_ID=1/*!*/;

Example Event As It's Written In The Binlog File

78 ed 1c 5b 05 01 00 00 00 20 00       x..[..... .
00 00 02 03 00 00 00 00 01 01 00 00 00 00 00 00  ................
00 2d 3f a2 f5                                   .-?..

_{This page is licensed: CC BY-SA / Gnu FDL}

QUERY_EVENT

This event is written into the binary log file for:

STATEMENT based replication (updating statements)
DDLs
COMMIT related to non transactional engines (MyISAM, BLACKHOLE etc)

Header

Event Type = 0x02

Fields

Fixed data part:

uint<4> The ID of the thread that issued this statement on the master.
uint<4> The time in seconds that the statement took to execute.
uint<1> The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.
uint<2> The error code resulting from execution of the statement on the master.
uint<2> The length of the status variable block.

Variable data part:

byte Zero or more status variables. Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific. The number of bytes 'n' is the length of the status variable block (read in fixed data part)
string The default database name (null-terminated).
string The SQL statement. By subtraction the size of the statement can be known.

Status variables

Q_FLAGS2_CODE (0x00):

uint<4> bitmask

Value

Option

0x00004000

OPTION_AUTO_IS_NULL

0x00080000

OPTION_NOT_AUTOCOMMIT

0x04000000

OPTION_NO_FOREIGN_KEY_CHECKS

0x08000000

OPTION_RELAXED_UNIQUE_CHECKS

Q_SQL_MODE_CODE (0x01):

uint<8> 8-byte bitmask

Value

SQL_MODE

0x00000001

MODE_REAL_AS_FLOAT

0x00000002

MODE_PIPES_AS_CONCAT

0x00000004

MODE_ANSI_QUOTES

0x00000008

MODE_IGNORE_SPACE

0x00000010

MODE_NOT_USED

0x00000020

MODE_ONLY_FULL_GROUP_BY

0x00000040

MODE_NO_UNSIGNED_SUBTRACTION

0x00000080

MODE_NO_DIR_IN_CREATE

0x00000100

MODE_POSTGRESQL

0x00000200

MODE_ORACLE

0x00000400

MODE_MSSQL

0x00000800

MODE_DB2

0x00001000

MODE_MAXDB

0x00002000

MODE_NO_KEY_OPTIONS

0x00004000

MODE_NO_TABLE_OPTIONS

0x00008000

MODE_NO_FIELD_OPTIONS

0x00010000

MODE_MYSQL323

0x00020000

MODE_MYSQL40

0x00040000

MODE_ANSI

0x00080000

MODE_NO_AUTO_VALUE_ON_ZERO

0x00100000

MODE_NO_BACKSLASH_ESCAPES

0x00200000

MODE_STRICT_TRANS_TABLES

0x00400000

MODE_STRICT_ALL_TABLES

0x00800000

MODE_NO_ZERO_IN_DATE

0x01000000

MODE_NO_ZERO_DATE

0x02000000

MODE_INVALID_DATES

0x04000000

MODE_ERROR_FOR_DIVISION_BY_ZERO

0x08000000

MODE_TRADITIONAL

0x10000000

MODE_NO_AUTO_CREATE_USER

0x20000000

MODE_HIGH_NOT_PRECEDENCE

0x40000000

MODE_NO_ENGINE_SUBSTITUTION

0x80000000

MODE_PAD_CHAR_TO_FULL_LENGTH

Q_CATALOG_NZ_CODE (0x02):

uint<1> length
string<length +1> catalog name + '\0'

Q_AUTO_INCREMENT (0x03):

uint<2> auto_increment increment
uint<2> auto_increment offset

Q_CHARSET_CODE (0x04):

uint<2> client character set
uint<2> collation_connection
uint<2> collation_server

Q_TIMEZONE_CODE (0x05):

uint<1> length
string time zone

Q_CATALOG_NZ_CODE (0x06):

uint<1> length
string catalog

Q_LC_TIME_NAMES_CODE (0x07):

uint<2> code The mapping between code and names are defined in sql_locale.cc.

Q_CHARSET_DATABASE_CODE (0x08)

uint<2> database collation

Q_TABLE_MAP_FOR_UPDATE_CODE (0x09)

uint<8> table bittmask Every bit of this variable represents a table, and is set to 1 if the corresponding table is to be updated by this statement.

Q_MASTER_DATA_WRITTEN_CODE (0x0A): #not in use anymore

uint<4> original event length

Q_INVOKER (0x0B|):

uint<1> user name length
string user name
uint<1> host name length
string host name

Q_UPDATED_DB_NAMES (0x0C): MySQL only

uint<1> count
for (i=0;i < count; i++)
- string Null terminated database name

Q_MICROSECONDS (0x0D): MySQL only

uint<3> microsecond part

Q_HRNOW (0x80): MariaDB only

uint<3> microsecond part

Q_XID (0x81): MariaDB only

uint<8> xid

Example With CRC32

71 17 28 5a 02 8c 27 00  00 55 00 00 00 01 09 00  q.(Z..'..U......
00 00 00 66 01 00 00 00  00 00 00 00 00 00 1a 00  ...f............
00 00 00 00 00 01 00 00  00 50 00 00 00 00 06 03  .........P......
73 74 64 04 08 00 08 00  08 00 00 54 52 55 4e 43  std........TRUNC
41 54 45 20 54 41 42 4c  45 20 74 65 73 74 2e 74  ATE TABLE test.t
34 4a 69 9e ed                                    4Ji..

Header, 19 Bytes

Event Time[4] = 71 17 28 5a ===> 1512576881
Event Type[1] = 2
Server_id[4] = 8c 27 00 00 ===> 10124
Event Size = 55 00 00 00 ===> 85
Next Pos = 01 09 00 00 ===> 2305
Flags = 00 00 => 0

Content, Variable Data

Thread ID[4] = 66 01 00 00 ===> 358
Execution Time[4] = 00 00 00 00 => 0 seconds
Statement default database name len[1] = 00 => 0 (no default db)
Error code[2] = 00 00 => 0 (no errors)
Status variable block len[2] = 1a 00 => 26
Status variables[n] = 00 ... 08 00
The default database[string] = 00 = 0 (no default db)
The SQL statement[string] = TRUNCATE TABLE test.t4

CRC32, 4 Bytes

4a 69 9e ed

Example With Default db and CRC32

MariaDB []> use test;
Database changed
MariaDB [test]> TRUNCATE TABLE t4;
...

ce 22 28 5a 02 8c 27 00  00 54 00 00 00 87 0c 00  ."(Z..'..T......
00 00 00 66 01 00 00 01  00 00 00 04 00 00 1a 00  ...f............
00 00 00 00 00 01 00 00  00 50 00 00 00 00 06 03  .........P......
73 74 64 04 08 00 08 00  08 00 74 65 73 74 00 54  std.......test.T
52 55 4e 43 41 54 45 20  54 41 42 4c 45 20 74 34  RUNCATE TABLE t4
08 f1 09 16                                       ....

Content, Variable Data

Thread ID[4] = 66 01 00 00 ===> 358
Execution Time[4] = 10 00 00 00 => 1 second
Statement default database name len[1] = 04 => 4 (default db is "test")
Error code[2] = 00 00 => 0 (no errors)
Status variable block len[2] = 1a 00 => 26
Status variables[n] = 00 ... 08 00
The default database[string] = 74 65 73 74 00 =>test
The SQL statement[string] = TRUNCATE TABLE test.t4

_{This page is licensed: CC BY-SA / Gnu FDL}

RAND_EVENT

The SQL function RAND() generates a random number.

A RAND_EVENT contains two seed values that set the rand_seed1 and rand_seed2 system variables that are used to compute the random number.

Note: it is written only before a QUERY_EVENT and is not used with row-based logging.

Header

Event Type is 13 (0x0d)

Fields

uint<8> The value for the first seed
uint<8> The value for the second seed

Example From mysqlbinlog Utility, No CRC32

# at 389
#171206 13:46:56 server id 10116  end_log_pos 424 	Rand
SET @@RAND_SEED1=685157301, @@RAND_SEED2=758850369/*!*/;
# at 424

Example Event As It's Written In The Binlog File

c0 e6 27 5a 0d 84 27 00 00 23 00 00 00 a8 01 00 ..'Z..'..#...... 00 00 00 b5 ab d6 28 00 00 00 00 41 23 3b 2d 00 ......(......... 00 00 00 ....

_{This page is licensed: CC BY-SA / Gnu FDL}

ROTATE_EVENT

When a binary log file exceeds the configured size limit, a ROTATE_EVENT is written at the end of the file, pointing to the next file in the sequence.

ROTATE_EVENT is generated locally and written to the binary log on the master and it's also written when a FLUSH LOGS statement occurs on the master server.

The ROTATE_EVENT is sent to the connected slave servers.

Header

The Event Type is set ROTATE_EVENT (0x4)

Fields

uint<8> The position of the first event in the next log file. Note: it always contains the number 4 (meaning the next event starts at position 4 in the next binary log).
string The next binary log name. The filename is not null-terminated.

Example of Transmission With CRC32 (The Last 4 Bytes)

T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
  30 00 00 4d 00 bc 4e 21    5a 04 d9 27 00 00 2f 00    0..M..N!Z..'../.
  00 00 c0 01 00 00 00 00    04 00 00 00 00 00 00 00    ................
  6d 79 73 71 6c 2d 62 69    6e 2e 30 30 30 30 31 39    mysql-bin.000019
  b2 bc db bf                                           ....

_{This page is licensed: CC BY-SA / Gnu FDL}

ROWS_EVENT_V1/V2, ROWS_COMPRESSED_EVENT_V1

A ROWS_EVENT_V1 is written for row based replication if data is inserted, deleted or updated

A ROWS_EVENT (version 2) is written for row based replication if data is inserted, deleted or updated if database server is MySQL 5.6 or newer. MariaDB Server doesn't send version 2 row events.

Event types

WRITE_ROWS_EVENT_V1

Insert new row

UPDATE_ROWS_EVENT_V1

Update existing row

DELETE_ROWS_EVENT_V1

Delete existing row

WRITE_ROWS_COMPRESSED_EVENT_V1

Insert new row

UPDATE_ROWS_COMPRESSED_EVENT_V1

Update existing row

DELETE_ROWS_COMPRESSED_EVENT_V1

Delete existing row

WRITE_ROWS_EVENT

Insert new row (version 2, MySQL only)

UPDATE_ROWS_EVENT

Update existing row (version 2, MySQL only)

DELETE_ROWS_EVENT

Delete existing row (version 2, MySQL only)

Header

WRITE_ROWS_EVENT_V1: Event Type is 23 (0x17)
UPDATE_ROWS_EVENT_V1: Event Type is 24 (0x18)
DELETE_ROWS_EVENT_V1: Event Type is 25 (0x19)
WRITE_ROWS_EVENT: Event Type is 30 (0xFD)
UPDATE_ROWS_EVENT: Event Type is 31 (0xFE)
DELETE_ROWS_EVENT: Event Type is 32 (0x20)
WRITE_ROWS_COMPRESSED_EVENT_V1: Event Type is 166 (0xA6)
UPDATE_ROWS_COMPRESSED_EVENT_V1: Event Type is 167 (0xA7)
DELETE_ROWS_COMPRESSED_EVENT_V1: Event Type is 168 (0xA8)

Fields

uint<6> The table id
uint<2> Flags
if rows_event is version 2
- uint<2> Extra data length
- string Extra data
uint Number of columns
byteColumns used. n = (number_of_columns + 7)/8
if (event_type == UPDATE_ROWS_EVENT_v1
- byte Columns used (Update). n = (number_of_columns + 7)/8
if *_COMPRESSED_EVENT_V1
- byte<1> header
  - algorithm: (header & 0x07) >> 4 (always 0=zlib)
  - header_size: header & 0x07
- byte<header_size>uncompressed length, stored in MyISAM format:
byte Null Bitmap (n = (number_of_columns + 7)/8)
string Column data. The length needs to be calculated by checking the column types from referring TABLE_MAP_EVENT.
if (event_type == UPDATE_ROWS_EVENT_v1
- byte Null Bitmap_Update. n = (number_of_columns + 7)/8
- string Update Column data. The length needs to be calculated by checking the used colums bitmap and column types from referring TABLE_MAP_EVENT.

Table id

Table id refers to a table defined by TABLE_MAP_EVENT. The special value 0xFFFFFF should have "end of statement flag" (0x0001) set and indicates that table maps can be freed.

Flags

0x0001

End of statement

0x0002

No foreign key checks

0x0004

No unique key checks

0x0008

Indicates that rows in this event are complete

0x0010

No check constraints

Extra data length (version 2)

The length of extra data

Extra data (version 2)

Extra data, length is extra data length - 2

Column Data Formats

The row data is stored in a packed format where each field is encoded in a particular format. The encoding is almost identical to the binary protocol but there are a few differences.

The field metadata is stored in the metadata block of the TABLE_MAP_EVENT. The metadata is required to decode the events. The following list shows number of bytes a field uses from the metadata block.

2 bytes
- MYSQL_TYPE_BIT
- MYSQL_TYPE_ENUM
- MYSQL_TYPE_SET
- MYSQL_TYPE_NEWDECIMAL
- MYSQL_TYPE_DECIMAL
- MYSQL_TYPE_VARCHAR
- MYSQL_TYPE_VAR_STRING
- MYSQL_TYPE_STRING
1 byte
- MYSQL_TYPE_TINY_BLOB
- MYSQL_TYPE_MEDIUM_BLOB
- MYSQL_TYPE_LONG_BLOB
- MYSQL_TYPE_BLOB
- MYSQL_TYPE_TIMESTAMP2
- MYSQL_TYPE_DATETIME2
- MYSQL_TYPE_TIME2
- MYSQL_TYPE_FLOAT
- MYSQL_TYPE_DOUBLE

The types that aren't listed here do not store data in the metadata block.

Simple Types

MYSQL_TYPE_NULL

Bit set in null bitmap, no value in row data

MYSQL_TYPE_TINY

1 byte integer

MYSQL_TYPE_YEAR

1 byte integer (year = value + 1900)

MYSQL_TYPE_SHORT

2 byte integer

MYSQL_TYPE_INT24

3 byte integer

MYSQL_TYPE_LONG

4 byte integer

MYSQL_TYPE_LONGLONG

8 byte integer

MYSQL_TYPE_FLOAT

4 byte floating point value (stored as a C float type)

MYSQL_TYPE_DOUBLE

8 byte floating point value (stored as a C double type)

`MYSQL_TYPE_BLOB` and other blob types

Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the blob. The size of the preceding integer in bytes is stored as a one byte integer in the table metadata that is a part of the table map event.

For example if the value 4 is stored in the table metadata the length is stored as a 4 byte integer (e.g. uint32_t) followed by the data.

The exact column_type can be determined by the metadata length:

Length

Type

MYSQL_TYPE_TINY_BLOB

MYSQL_TYPE_BLOB

MYSQL_TYPE_MEDIUM_BLOB

MYSQL_TYPE_LONG_BLOB

`MYSQL_TYPE_STRING`, `MYSQL_TYPE_SET` and `MYSQL_TYPE_ENUM`

Stored as a fixed-length string with the length of the string stored in the second byte of the table metadata. All three of these types are stored as MYSQL_TYPE_STRING in the binlog and the real type of the field is stored in the first byte of the metadata.

`MYSQL_TYPE_VARCHAR` and other variable length string types

Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the string. The field length is stored as a two byte integer in the table metadata.

If the field length is larger than 255, the string length is stored as a two byte integer. If the value is equal to or less than 255, the string length is stored as a one byte integer.

`MYSQL_TYPE_DATETIME`

Note: This field type is only used in MariaDB if global variable mysql56_temporal_format was set to OFF. Stored as a 8 byte value with the values stored as multiples of 100. This means that the stored value is in the format YYYYMMDDHHMMSS and can be easily extracted by repeatedly calculating the remainder of dividing the value by 100 and dividing the value by 100. The following pseudo-code demonstrates extracting the value.

value = read_8_byte_value(row_data)
date_val= value / 1000000
time_val= value % 1000000

year = (date_val / 100) / 100
month = (date_val / 100) % 100
day = date_val % 100
hour= (time_val / 100) / 100
minute = (time_val / 100) % 100
second = time_val % 100

`MYSQL_TYPE_TIME`

Note: This field type is only used in MariaDB if global variable mysql56_temporal_format was set to OFF. Stored as a 3 byte value with the values stored as multiples of 100. This means that the stored value is in the format HHMMSS and can be easily extracted the same way a MYSQL_TYPE_DATETIME is extracted. The following pseudo-code demonstrates extracting the value.

time_val = read_3_byte_value(row_data); /* myisam pack format */
hour= (time_val / 100) / 100;
minute = (time_val / 100) % 100;
second = time_val % 100;

`MYSQL_TYPE_DATETIME2`

Stored as 4-byte value The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 5 byte datetime value can be calculated with the following formula: (decimals + 1) / 2

val = read_5_byte_value(row_data) - 0x8000000000
d_val= val >> 17;
t_val = val % (1 << 17);

day= d_val % (1 << 5);
month= (d_val >> 5) % 13;
year= (d_val >> 5) / 13;
second= t_val % (1 << 6);
minute= (t_val >> 6) % (1 << 6);
hour= (t_val)(time_part >> 12);

`MYSQL_TYPE_TIME2`

Stored as 3-byte value The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 3 byte time value can be calculated with the following formula: (decimals + 1) / 2

t_val = read_3_byte_value(row_data) - 0x800000
if (t_val < 0)
{
  signed= 1;
  t_val= - tval;
}
hour= (t_val >> 12) % (1 << 10);
minute= (t_val >> 6) % (1 << 6);
second= t_val % (1 << 6);

`MYSQL_TYPE_TIMESTAMP2`

Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC) followed by the fractional second parts. The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 4 byte timestamp can be calculated with the following formula: (decimals + 1) / 2

Microseconds for MYSQL_TYPE_DATETIME2, MYSQL_TYPE_TIME2 and MYSQL_TYPE_TIMESTAMP

len = (decimals + 1) / 2
  val= read_len_byte_value(row_data)
  llen= 0: microseconds= 0
  len = 1 or 2: microseconds = val * 10000
  len = 3 or 4: microseconds = 2-bytes val in myisam pack format
  len = 5 or 6: microseconds = 3-bytes val in myisam pack format

`MYSQL_TYPE_DATE`

Stored as a 3 byte value where bits 1 to 5 store the day, bits 6 to 9 store the month and the remaining bits store the year.

`MYSQL_TYPE_TIMESTAMP`

Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC).

Example From mysqlbinlog Utility, CRC32

# at 1680
#180611  9:50:51 server id 1  end_log_pos 1754 CRC32 0x5415a8fb 	Write_rows: table id 23 flags: STMT_END_F

BINLOG '
2ykeWxMBAAAAPgAAAJAGAAAAABcAAAAAAAEABHRlc3QACWJ1bGtfbnVsbAAFDwMFE/YGFAAIAAMB
H1bULg8=
2ykeWxcBAAAASgAAANoGAAAAABcAAAAAAAEABf/gATMDAAAAAAAAAAAACECAAACDAP/gATMDAAAA
AAAAAAAACECAAACDAPuoFVQ=
'/*!*/;

Example Event As It's Written In The Binlog File

db 29 1e 5b 17 01 00 00 00 4a 00       .).[.....J.
00 00 da 06 00 00 00 00 17 00 00 00 00 00 01 00  ................
05 ff e0 01 33 03 00 00 00 00 00 00 00 00 00 08  ....3...........
40 80 00 00 83 00 ff e0 01 33 03 00 00 00 00 00  @........3......
00 00 00 00 08 40 80 00 00 83 00 fb a8 15 54     .....@........T                                   ....

_{This page is licensed: CC BY-SA / Gnu FDL}

START_ENCRYPTION_EVENT

The START_ENCRYPTION event is written to every binary log file if encrypt_binlog is set to ON.

This event is written just once, after the Format Description event (which is the first event of a binlog file at pos 4).

The event has the 19 bytes event header with EventType set to value 164 (0xa4) + 17 bytes data.

Header

Event type is 164 (0xa4)

Fields

uint<1> The Encryption scheme, always set to 1 for system files.
uint<4> The Encryption key version.
byte<12> Nonce (12 random bytes) of current binlog file.

Decryption of following events

All data of following events in the binlog file are encrypted, except for the event_length field

The 16 byte encryption IV is generated from the 12 byte nonce (uint<12>) in the binlog plus the current position of the event being encrypted (uint<4>). This means the last four bytes of the IV change for every event and the first 12 bytes change for every binlog file.

Since the event_length is always unencrypted, the encrypted data block has to be modified before it can be decrypted:

store event_length
copy the first four bytes (encrypted timestamp) to event_length position (offset=9)
decrypt starting from offset 4 and store result at offset 4 of decrypted buffer

The unencrypted block now also needs to be modified

move unencrypted timestamp value from offset 9 to the beginning (offset=0)
store event_length at position 9

Complete example with CRC32 from a binary log.

b8 5f 5a 59 a4 5d 00 00  00 28 00 00 00 21 01 00 ._ZY.]...(...!..
00 00 00 01 01 00 00 00  65 57 50 26 63 59 37 46 ........eWP&cY7F
2f 3b 33 23 06 bb da 62                          /;3#...b

header, 19 bytes:

timestamp [4] = b8 5f 5a 59 => 59 5a 5f b8 => 1499094968 [2017-07-03 17:16:08]
type [1} = a4 => 164
server_id [4} = 5d 00 00 00 => 00 00 00 5d => 93
event_size [4] = 28 00 00 00 => 00 00 00 28 => 40 [header + content + crc32(header + content)]
next_pos [4] = 21 01 00 00 => 00 00 01 21 => 289
flags [2] = 00 00 => 0

content,17 bytes

Enc scheme [1] = 01 => 1
Enc key ver [4] = 01 00 00 00 => 00 00 00 01 => 1
Nonce [12] = eWP&cY7F/;3#

crc32, 4 bytes, of the whole event (header[19] + content[17])

06 bb da 62 => 62 da bb 06 => 1658501894

_{This page is licensed: CC BY-SA / Gnu FDL}

STOP_EVENT

The master server writes the event to the binary log when it shuts down or when resuming after a mysqld process crash.

A new binary log file is always created but there is no ROTATE_EVENT.

STOP_EVENT is then the last written event after clean shutdown or resuming a crash.

This event is never sent to slave servers.

Header

Event header with EventType set to STOP_EVENT (0x03).
Event header NextPos set to EOF
No special flags added.

Fields

The event has no data

Example With CRC32 (Last 4 Bytes)

Event size = header[19] + 0 bytes data + 4 CRC32 = 23

3a b8 15 5a 03 01 00 00  00 17 00 00 00 09 0c 00  ..Z............
00 00 00 4e 99 ee 2c                              ...N..,

_{This page is licensed: CC BY-SA / Gnu FDL}

TABLE_MAP_EVENT

Used for row-based binary logging beginning (binlog_format=ROW or MIXED)

This event precedes each row operation event and maps a table definition to a number, where the table definition consists of database and table names.

Header

Event Type is 19 (0x13)

Fields

Fixed data part:

uint<6> The table ID.
uint<2> Reserved for future use.

Variable data part:

uint<1> Database name length.
string The database name (null-terminated).
uint<1> Table name length.
string The table name (null-terminated).
int The number of columns in the table.
byte An array of 'n' column types, one byte per column.
int The length of the metadata block.
byte The metadata block;
byte Bit-field indicating whether each column can be NULL, one bit per column.
if (more_data_available)
- byte Optional metadata block;

Metadata block

The metadata block contains type specific metadata information for each column

Type

Length

Description

MYSQL_TYPE_BLOB

Number of bytes for length: e.g. 4 bytes means length is stored in a 4 byte integer)

MYSQL_TYPE_DATETIME2

Length of microseconds

MYSQL_TYPE_DECIMAL

Not in use anymore

MYSQL_TYPE_DOUBLE ,MYSQL_TYPE_FLOAT

length (4 or 8 bytes

MYSQL_TYPE_STRING

1st byte contains type (MYSQL_TYPE_STRING, MYSQL_TYPE_ENUM, or MYSQL_TYPE_SET, 2nd byte contains length

MYSQL_TYPE_NEWDECIMAL

Precision, Scale

MYSQL_TYPE_TIME2

Length of microseconds

MYSQL_TYPE_TIMESTAMP2

Length of microseconds

MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VAR_STRING

Defined varchar length. If the value is > 255, length is stored in 2 bytes, otherwise 1 byte

Optional metadata block

Optional metadata will be available if global server variable BINLOG_ROW_METADATA was set to values MIN or FULL.

The metadata block consists of one or more of the following blocks:

byte<1> Optional metadata type
int Length
byte Data

Optional metadata types:

Name

Value

Mode

Description

SIGNEDNESS

MIN

Data contains a bitmap indicating which integer columns are signed

DEFAULT_CHARSET

MIN

Character set of string columns, used if most columns have the same result. Columns with other character sets will follow as pair (column_index, collation number).

COLUMN_CHARSET

MIN

Character set of columns, used if columns have different character sets. Returned as a sequence of collation numbers.

COLUMN_NAME

FULL

List of Column names, the first byte specifies the length of the column name

SET_STR_VALUE

FULL

List of set values: First byte is the number of different values, followed by length/value pairs.

ENUM_STR_VALUE

FULL

Same as SET_STR_VALUE. Since ENUM values might have up to 0xFFFF members, the number of values is a length encoded integer.

GEOMETRY_TYPE

FULL

A sequence of bytes repesenting the type of GEOMETRY columns: 0 = GEOMETRY, 1 = POINT, 2 = LINESTRING, 3 = POLYGON, 4=MULTIPOINT, 5 = MULTILINESTRING, 6 = MULTIPOLYGON, 7 = GEOMETRYCOLLECTION

SIMPLE_PRIMARY_KEY

FULL

A sequence of length encoded column indexes.

PRIMARY_KEY_WITH_PREFIX

FULL

A sequence of length encoded column indexes and prefix lengths.

ENUM_AND_SET_DEFAULT_CHARSET

FULL

The default character set number used for ENUM and SET columns

ENUM_AND_SET_COLUMN_CHARSET

FULL

Character set of ENUM and SET columns, used if these columns have different character sets. Returned as a sequence of collation numbers.

Example From mysqlbinlog

# at 847
#171206 13:43:00 server id 10124  end_log_pos 892 CRC32 0xbe3c6b05 	Table_map: `test`.`t4` mapped to number 33
# at 892

Complete Event

d4 e5 27 5a 13 8c 27 00  00 2d 00 00 00 7c 03 00  ..'Z..'..-...|..
00 00 00 21 00 00 00 00  00 01 00 04 74 65 73 74  ...!........test
00 02 74 34 00 01 03 01  01 05 6b 3c be           ..t4......k<.

_{This page is licensed: CC BY-SA / Gnu FDL}

USER_VAR_EVENT

A USER_VAR_EVENT is written every time a statement uses a user defined variable.

Header

Event Type is 14 (0x0e)

Fields

uint<4> The length of user variable name
string The name of the user variable
uint<1> NULL indicator
if (not null indicator)
- uint<1> variable type
- uint<4> collation number
- uint<4> The length of value
- string value
- uint<1> flags

Variable type

Value

Type

Example

0x00

STRING_RESULT

set @a:="foo"

0x01

REAL_RESULT

set @a:= @@timestamp

0x02

INT_RESULT

set @a:= 4

0x03

ROW_RESULT

(not in use)

0x04

DECIMAL_RESULT

set @a:=1.2345

Flag

0x01

unsigned

Example for `SET @foo:="bar"`from mysqlbinlog Utility, CRC32

# at 511
#180610 10:26:43 server id 1  end_log_pos 554 CRC32 0x7dd93d6b 	User_var
SET @`foo`:=_utf8 X'626172' COLLATE `utf8_general_ci`/*!*/;

Example Event As It's Written In The Binlog File

c3 e0 1c 5b 0e 01 00 00 00 2b 00       ...[.....+.
00 00 2a 02 00 00 00 00 03 00 00 00 66 6f 6f 00  ..*.........foo.
00 21 00 00 00 03 00 00 00 62 61 72 6b 3d d9 7d  .!.......bark=.}                                          ....

_{This page is licensed: CC BY-SA / Gnu FDL}

XA_PREPARE_LOG_EVENT

A XA_PREPARE_LOG_EVENT records the prepare phase of a distributed transaction using the XA log. It is used to ensure atomicity and consistency of transactions.

Header

Event Type is XA_PREPARE_LOG_EVENT (0x26)

Fields

uint<1> One Phase Commit
uint<4> Format ID
uint<4> Length of gtrid
uint<1> Length of bqual

Payload:

byte xid, where n is sum of gtrid and bqual lengths

_{This page is licensed: CC BY-SA / Gnu FDL}

XID_EVENT

An XID event is generated for a COMMIT of a transaction that modifies one or more tables of an XA-capable storage engine.

Header

Event Type is XID_EVENT (0x10)

Fields

uint<8> The XID transaction number.

Complete Example With CRC32

ee b7 15 5a 10 01 00 00  00 1f 00 00 00 f2 0b 00  ...Z............
00 00 00 66 00 00 00 00  00 00 00 09 30 45 a8     ...f.........0E.

Header, 19 Bytes

Event size is: header[19] + XID[8] + CRC32[4] of (header + xid)

Event Time ee b7 15 5a => 5a 15 b7 ee => 1511372782 [2017-11-22 18:46:22]
Event Type = 10
Server_id 01 00 00 00 => 1
Event Size 1f 00 00 00 => 31
Next Pos f2 0b 00 00 => 00 00 0b f2 => 3058
Flags 00 00 = 0

Content, 8 Bytes

XID 66 00 00 00 00 00 00 00 => 102

CRC32, 4 Bytes

09 30 45 a8 => a8 45 30 09 => 2823106569

_{This page is licensed: CC BY-SA / Gnu FDL}

Replication Protocol

1-Binlog Events

Log File Structure

Event Content

Example MariaDB 10.1 Binlog File (Hexdump -C $file_name)

Example From mysqlbinlog

2-Binlog Event Header

Event Header Structure, 19 Bytes

Encrypted Binlog Events

Event Type

Fake Events

Event Flag

Event Header example of FORMAT_DESCRIPTION_EVENT

Interpretation of First 19 Bytes of the Event (the Event Header)

3-Binlog Network Stream

Example of an Event Transmission HEARTBEAT_LOG_EVENT

Network Replication Protocol, 5 Bytes

Heartbeat event

4-Semi-Sync Replication

Event Header Changes

Semi Sync ACK Details

Example of Heartbeat Event With Semi-Sync Protocol and CRC32

Example of XID_EVENT, With CRC32

Example of Semi-Sync ACK

5-Replica Registration

Example Using 'ngrep'

Complete Example with GTID Registration (Up to COM_BINLOG_DUMP Request), No Semi-Sync

Events Transmission After COM_BINLOG_DUMP.

Complete Example of Event Transmission With CRC32

ANNOTATE_ROWS_EVENT

Header

Fields

Complete Example with CRC32

BEGIN_LOAD_QUERY_EVENT

Header

Fields

Example

BINLOG_CHECKPOINT_EVENT

Header

Fields

Example Without CRC32

Header, 19 Bytes

Content, Variable Size

COM_BINLOG_DUMP

Example of COM_BINLOG_DUMP

COM_REGISTER_SLAVE

Example of COM_REGISTER_SLAVE

EXECUTE_LOAD_QUERY_EVENT

Header

Fields

Example

Fake GTID_LIST event

Header

Content

of GTIDs

Fake ROTATE_EVENT

Header

Content

FORMAT_DESCRIPTION_EVENT

Header

Fields

Example FDE of MariaDB 10.2.10 With CRC32

Header, 19 Bytes

Content, Variable Size Depending on MariaDB Versions

Example FDE of MariaDB 10.1.16 With CRC32

Header, 19 Bytes

Content, Variable Size Depending on MariaDB Versions

GTID_EVENT

Event Header

Fields

Flags

Transaction Example from mysqlbinlog Utility

Standalone Event DDL (FLUSH TABLES) from mysqlbinlog Utility

Example GTID_EVENT with DDL and CRC32

Example GTID_EVENT With a Transaction and CRC32

GTID_LIST_EVENT

Header

Fields

Example With 1 GTID, With CRC32

Header, 19 Bytes

`MYSQL_TYPE_BLOB` and other blob types

`MYSQL_TYPE_STRING`, `MYSQL_TYPE_SET` and `MYSQL_TYPE_ENUM`

`MYSQL_TYPE_VARCHAR` and other variable length string types

`MYSQL_TYPE_DATETIME`

`MYSQL_TYPE_TIME`

`MYSQL_TYPE_DATETIME2`

`MYSQL_TYPE_TIME2`

`MYSQL_TYPE_TIMESTAMP2`

`MYSQL_TYPE_DATE`

`MYSQL_TYPE_TIMESTAMP`