MySQL :: MySQL Internals Manual :: 15.9 Replication Protocol

MySQL Internals Manual :: 15 MySQL Client/Server Protocol :: 15.9 Replication Protocol

Section Navigation [Toggle]

15.9. Replication Protocol

15.9.1. Binlog File
15.9.2. Binlog Network Stream
15.9.3. Binlog Version
15.9.4. Binlog Event
15.9.5. COM_BINLOG_DUMP
15.9.6. COM_BINLOG_DUMP_GTID
15.9.7. COM_TABLE_DUMP
15.9.8. COM_CONNECT_OUT
15.9.9. COM_REGISTER_SLAVE

Replication uses binlogs to ship changes done on the master to the slave and can be written to Binlog File and sent over the network as Binlog Network Stream.

15.9.1. Binlog File

15.9.1.1. Binlog File Header

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

15.9.1.1. Binlog File Header

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

$ hexdump -C /tmp/binlog-test.log
00000000  fe 62 69 6e 19 6f c9 4c  0f 01 00 00 00 66 00 00  |.bin.o.L.....f..|
00000010  00 6a 00 00 00 00 00 04  00 6d 79 73 71 6c 2d 70  |.j.......mysql-p|
00000020  72 6f 78 79 2d 30 2e 37  2e 30 00 00 00 00 00 00  |roxy-0.7.0......|
...

15.9.2. Binlog Network Stream

Network streams are requested with COM_BINLOG_DUMP and prepend each Binlog Event with 00 OK-byte.

15.9.3. Binlog Version

15.9.3.1. Determining the Binlog Version

Depending on the MySQL Version that created the binlog the format is slightly different. Four versions are currently known:

Table 15.1. Binlog Versions

ver	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+

Version 1

supported statement based replication events

Version 2

can be ignored as it was only used in early alpha versions of MySQL 4.1.x and won't be documented here

Version 3

added the relay logs and changed the meaning of the log position

Version 4

added the FORMAT_DESCRIPTION_EVENT and made the protocol extensible

In MySQL 5.1.x the Row Based Replication Events were added.

15.9.3.1. Determining the Binlog Version

By the time you read the first event from the log you don't know what binlog version the binlog has. To determine the version correctly it has to be checked if the first event is:

a FORMAT_DESCRIPTION_EVENT version = 4
a START_EVENT_V3
- if event-size == 13 + 56: version = 1
- if event-size == 19 + 56: version = 3
- otherwise: invalid

15.9.4. Binlog Event

The events contain the actual data that should be shipped from the master to the slave. Depending on the use, different events are sent.

Binlog Management

The first event is either a START_EVENT_V3 or a FORMAT_DESCRIPTION_EVENT while the last event is either a STOP_EVENT or a ROTATE_EVENT.

Statement Based Replication Events

Statement Based Replication or SBR sends the SQL queries a client sent to the master AS IS to the slave. It needs extra events to mimic the client connection's state on the slave side.

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.

LOAD INFILE replication

LOAD DATA|XML INFILE is a special SQL statement as it has to ship the files over to the slave too to execute the statement.

A binlog event starts with a Binlog Event header and is followed by a Binlog Event Type specific data part.

15.9.4.1. Binlog Event header

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

Binlog::EventHeader:

Binlog header

Payload

4              timestamp
1              event type
4              server-id
4              event-size
   if binlog-version > 1:
4              log pos
2              flags

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

15.9.4.2. Binlog Event Flag

hex	flag
0x0001	LOG_EVENT_BINLOG_IN_USE_F
0x0002	LOG_EVENT_FORCED_ROTATE_F
0x0004	LOG_EVENT_THREAD_SPECIFIC_F
0x0008	LOG_EVENT_SUPPRESS_USE_F
0x0010	LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F
0x0020	LOG_EVENT_ARTIFICIAL_F
0x0040	LOG_EVENT_RELAY_LOG_F
0x0080	LOG_EVENT_IGNORABLE_F
0x0100	LOG_EVENT_NO_FILTER_F
0x0200	LOG_EVENT_MTS_ISOLATE_F

LOG_EVENT_BINLOG_IN_USE_F: gets unset in the FORMAT_DESCRIPTION_EVENT when the file gets closed to detect broken binlogs
LOG_EVENT_FORCED_ROTATE_F: unused
LOG_EVENT_THREAD_SPECIFIC_F: event is thread specific (CREATE TEMPORARY TABLE ...)
LOG_EVENT_SUPPRESS_USE_F: event doesn't need default database to be updated (CREATE DATABASE, ...)
LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F: unused
LOG_EVENT_ARTIFICIAL_F: event is created by the slaves SQL-thread and shouldn't update the master-log pos
LOG_EVENT_RELAY_LOG_F: event is created by the slaves IO-thread when written to the relay log

15.9.4.3. Binlog Event Type

hex	event name
0x00	UNKNOWN_EVENT
0x01	START_EVENT_V3
0x02	QUERY_EVENT
0x03	STOP_EVENT
0x04	ROTATE_EVENT
0x05	INTVAR_EVENT
0x06	LOAD_EVENT
0x07	SLAVE_EVENT
0x08	CREATE_FILE_EVENT
0x09	APPEND_BLOCK_EVENT
0x0a	EXEC_LOAD_EVENT
0x0b	DELETE_FILE_EVENT
0x0c	NEW_LOAD_EVENT
0x0d	RAND_EVENT
0x0e	USER_VAR_EVENT
0x0f	FORMAT_DESCRIPTION_EVENT
0x10	XID_EVENT
0x11	BEGIN_LOAD_QUERY_EVENT
0x12	EXECUTE_LOAD_QUERY_EVENT
0x13	TABLE_MAP_EVENT
0x14	WRITE_ROWS_EVENTv0
0x15	UPDATE_ROWS_EVENTv0
0x16	DELETE_ROWS_EVENTv0
0x17	WRITE_ROWS_EVENTv1
0x18	UPDATE_ROWS_EVENTv1
0x19	DELETE_ROWS_EVENTv1
0x1a	INCIDENT_EVENT
0x1b	HEARTBEAT_EVENT
0x1c	IGNORABLE_EVENT
0x1d	ROWS_QUERY_EVENT
0x1e	WRITE_ROWS_EVENTv2
0x1f	UPDATE_ROWS_EVENTv2
0x20	DELETE_ROWS_EVENTv2
0x21	GTID_EVENT
0x22	ANONYMOUS_GTID_EVENT
0x23	PREVIOUS_GTIDS_EVENT

15.9.4.4. ignored events

UNKNOWN_EVENT
SLAVE_EVENT

15.9.4.5. START_EVENT_V3

Binlog::START_EVENT_V3:

A start event is the first event of a binlog for binlog-version 1 to 3.

Payload

2                binlog-version
string[50]       mysql-server version
4                create timestamp

Fields

binlog-version (2) -- version of this binlog format.
mysql-server version (string.fix_len) -- [len=50] version of the MySQL Server that created the binlog. The string is evaluted to apply work-arounds in the slave.
create_timestamp (4) -- seconds since Unix epoch when the binlog was created

15.9.4.6. FORMAT_DESCRIPTION_EVENT

Binlog::FORMAT_DESCRIPTION_EVENT:

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

Note

added in MySQL 5.0.0 as replacement for START_EVENT_V3

Payload

2                binlog-version
string[50]       mysql-server version
4                create timestamp
1                event header length
string[p]        event type header lengths

Fields

binlog-version (2) -- version of this binlog format.
mysql-server version (string.fix_len) -- [len=50] version of the MySQL Server that created the binlog. The string is evaluted to apply work-arounds in the slave.
create_timestamp (4) -- seconds since Unix epoch when the binlog was created
event_header_length (1) -- length of the Binlog Event Header of next events. Should always be 19.
event type header length (string.EOF) -- a array indexed by Binlog Event Type - 1 to extract the length of the event specific header.

Example

$ hexdump -v -s 4 -C relay-bin.000001
00000004  82 2d c2 4b 0f 02 00 00  00 67 00 00 00 6b 00 00  |.-.K.....g...k..|
00000014  00 00 00 04 00 35 2e 35  2e 32 2d 6d 32 00 00 00  |.....5.5.2-m2...|
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 82  2d c2 4b 13 38 0d 00 08  |........-.K.8...|
00000054  00 12 00 04 04 04 04 12  00 00 54 00 04 1a 08 00  |..........T.....|
00000064  00 00 08 08 08 02 00                              |........        |

New in 5.0.0

For mysql-5.5.2-m2 the event specific header lengths are:

Table 15.2. event type header lengths by binlog-version

event name	header-len
event name	v=4	v=3	v=1
Binlog Event Header	19		13
START_EVENT_V3	56
QUERY_EVENT	13	11
STOP_EVENT	0
ROTATE_EVENT	8		0
INTVAR_EVENT	0
LOAD_EVENT	18
SLAVE_EVENT	0
CREATE_FILE_EVENT	4
APPEND_BLOCK_EVENT	4
EXEC_LOAD_EVENT	4
DELETE_FILE_EVENT	4
NEW_LOAD_EVENT	18
RAND_EVENT	0
USER_VAR_EVENT	0
FORMAT_DESCRIPTION_EVENT	84	---
XID_EVENT	0	---
BEGIN_LOAD_QUERY_EVENT	4	---
EXECUTE_LOAD_QUERY_EVENT	26	---
TABLE_MAP_EVENT	8	---
DELETE_ROWS_EVENTv0	0	---
UPDATE_ROWS_EVENTv0	0	---
WRITE_ROWS_EVENTv0	0	---
DELETE_ROWS_EVENTv1	8/6	---
UPDATE_ROWS_EVENTv1	8/6	---
WRITE_ROWS_EVENTv1	8/6	---
INCIDENT_EVENT	2	---
HEARTBEAT_EVENT	0	---
DELETE_ROWS_EVENTv2	10	---
UPDATE_ROWS_EVENTv2	10	---
WRITE_ROWS_EVENTv2	10	---

The event-size of 0x67 (103) minus the event-header length of 0x13 (19) should match the event type header length of the FORMAT_DESCRIPTION_EVENT 0x54 (84).

The number of events understood by the master may differ from what the slave supports. It is calculated by:

event-size - event-header length - 2 - 50 - 4 - 1

For mysql-5.5.2-m2 it is 0x1b (27).

15.9.4.7. ROTATE_EVENT

Binlog::ROTATE_EVENT:

The rotate event is added to the binlog as last event to tell the reader what binlog to request next.

Post-header

  if binlog-version > 1 {
8              position
  }

Payload

string[p]      name of the next binlog

15.9.4.8. STOP_EVENT

A STOP_EVENT has no payload or post-header.

15.9.4.9. QUERY_EVENT

Binlog::QUERY_EVENT:

The query event is used to send text querys right the binlog.

Post-header

4              slave_proxy_id
4              execution time
1              schema length
2              error-code
  if binlog-version ≥ 4:
2              status-vars length

Payload

string[$len]   status-vars
string[$len]   schema
1              [00]
string[EOF]    query

Fields

status_vars_length (2) -- number of bytes in the following sequence of status-vars

status_vars (string.var_len) -- [len=$status_vars_length] a sequence of status key-value pairs. The key is 1-byte, while its value is dependent on the key.

hex	flag	value-len
0x00	Q_FLAGS2_CODE	4
0x01	Q_SQL_MODE_CODE	8
0x02	Q_CATALOG	1 + n + 1
0x03	Q_AUTO_INCREMENT	2 + 2
0x04	Q_CHARSET_CODE	2 + 2 + 2
0x05	Q_TIME_ZONE_CODE	1 + n
0x06	Q_CATALOG_NZ_CODE	1 + n
0x07	Q_LC_TIME_NAMES_CODE	2
0x08	Q_CHARSET_DATABASE_CODE	2
0x09	Q_TABLE_MAP_FOR_UPDATE_CODE	8
0x0a	Q_MASTER_DATA_WRITTEN_CODE	4
0x0b	Q_INVOKERS	1 + n + 1 + n
0x0c	Q_UPDATED_DB_NAMES	1 + n*nul-term-string
0x0d	Q_MICROSECONDS	3

schema (string.var_len) -- [len=$schema_length] schema
query (string.EOF) -- text of the query

15.9.4.10. LOAD_EVENT

Binlog::LOAD_EVENT:

all string lengths are without the terminating 0

Payload

4              slave_proxy_id
4              exec_time
4              skip_lines
1              table_name_len
1              schema_len
4              num_fields

1              field_term
1              enclosed_by
1              line_term
1              line_start
1              escaped_by
1              opt_flags
1              empty_flags

string.var_len [len=1 * num_fields] (array of 1-byte) field_name_lengths
string.var_len [len=sum(field_name_lengths) + num_fields] (array of nul-terminated strings) field_names
string.var_len [len=table_name_len + 1] (nul-terminated string) table_name
string.var_len [len=schema_len + 1] (nul-terminated string) schema_name
string.NUL     file_name

Fields

empty_flags (1) --

x	name
0x01	FIELD_TERM_EMPTY
0x02	ENCLOSED_EMPTY
0x04	LINE_TERM_EMPTY
0x08	LINE_START_EMPTY
0x10	ESCAPE_EMPTY

opt_flags (1) --

x	name
0x01	DUMPFILE_FLAG
0x02	OPT_ENCLOSED_FLAG
0x04	REPLACE_FLAG
0x08	IGNORE_FLAG

15.9.4.11. NEW_LOAD_EVENT

Binlog::NEW_LOAD_EVENT:

Payload

4              slave_proxy_id
4              exec_time
4              skip_lines
1              table_name_len
1              schema_len
4              num_fields

1              field_term_len
string.var_len field_term
1              enclosed_by_len
string.var_len enclosed_by
1              line_term_len
string.var_len line_term
1              line_start_len
string.var_len line_start
1              escaped_by_len
string.var_len escaped_by
1              opt_flags

string.var_len [len=1 * num_fields] (array of 1-byte) field_name_lengths
string.var_len [len=sum(field_name_lengths) + num_fields] (array of nul-terminated strings) field_names
string.var_len [len=table_name_len] (nul-terminated string) table_name
string.var_len [len=schema_len] (nul-terminated string) schema_name
string.EOF     file_name

Fields

opt_flags (1) --

x	name
01	DUMPFILE_FLAG
02	OPT_ENCLOSED_FLAG
04	REPLACE_FLAG
08	IGNORE_FLAG

15.9.4.12. CREATE_FILE_EVENT

Binlog::CREATE_FILE_EVENT:

Payload

4              file_id
string.EOF     block-data

15.9.4.13. APPEND_BLOCK_EVENT

Binlog::APPEND_BLOCK_EVENT:

append block-data to file

Payload

4              file_id
string.EOF     block-data

15.9.4.14. EXEC_LOAD_EVENT

Binlog::EXEC_LOAD_EVENT:

Payload

4              file_id

15.9.4.15. BEGIN_LOAD_QUERY_EVENT

Binlog::BEGIN_LOAD_QUERY_EVENT:

truncate a file and set block-data

Payload

4              file_id
string.EOF     block-data

See

Binlog::APPEND_BLOCK_EVENT

15.9.4.16. EXECUTE_LOAD_QUERY_EVENT

Binlog::EXECUTE_LOAD_QUERY_EVENT:

Post-header

4              slave_proxy_id
4              execution time
1              schema length
2              error-code
2              status-vars length

Payload

4              file_id
4              start_pos
4              end_pos
1              dup_handling_flags

See

Binlog::QUERY_EVENT

15.9.4.17. DELETE_FILE_EVENT

Binlog::DELETE_FILE_EVENT:

Payload

4              file_id

15.9.4.18. RAND_EVENT

Binlog::RAND_EVENT:

Internal state of the RAND() function.

Payload

8              seed1
8              seed2

15.9.4.19. XID_EVENT

Binlog::XID_EVENT:

Transaction ID for 2PC, written whenever a COMMIT is expected.

Payload

8              xid

15.9.4.20. INTVAR_EVENT

Binlog::INTVAR_EVENT:

Integer based session-variables

Payload

1              type
8              value

Fields

type (1) --

hex	intvar event type
0x00	INVALID_INT_EVENT
0x01	LAST_INSERT_ID_EVENT
0x02	INSERT_ID_EVENT

value (8) -- value

15.9.4.21. USER_VAR_EVENT

Binlog::USER_VAR_EVENT:

Payload

4                name_length
string[$len]     name
1                is_null
  if not is_null {
1                type
4                charset
4                value_length
string[$len]     value
    if more data {
1                flags
    }
  }

Fields

name_length (4) -- length of name
name (string.var_len) -- [len=$name_length] name
is_null (1) -- 1 if value is NULL, 0 otherwise
type (1) -- type of value
charset (4) -- character set of value
value_length (4) -- length of value
value (string.var_len) -- [len=$value_length] value

flags (1) -- flags of value

hex	uservar flags
0x01	UNSIGNED

15.9.4.22. INCIDENT_EVENT

Binlog::INCIDENT_EVENT:

Payload

2              type
1              message length
n              message

Fields

type (2) --

hex	name
0x0000	INCIDENT_NONE
0x0001	INCIDENT_LOST_EVENTS

message_length (1) -- length of message
message (string.var_len) -- [len=$message_length] message

15.9.4.23. HEARTBEAT_EVENT

A artificial event generated by the master. It isn't written to the relay logs.

It is added by the master after the replication connection was idle for x-seconds to update the slaves Seconds_Behind_Master timestamp in the SHOW SLAVE STATUS.

It has no payload nor post-header.

15.9.5. COM_BINLOG_DUMP

Requests a binlog network stream from the master starting a given position.

You can use SHOW MASTER LOGS to get the current logfile and position from the master.

The master responds either with a

binlog network stream
a ERR_Packet
or (if BINLOG_DUMP_NON_BLOCK is set) with EOF_Packet

If the binlog-filename is empty, the server will send the binlog-stream of the first known binlog.

COM_BINLOG_DUMP:

request a binlog-stream from the server

Payload

1              [12] COM_BINLOG_DUMP
4              binlog-pos
2              flags
4              server-id
string[EOF]    binlog-filename

Fields

binlog_pos (4) -- position in the binlog-file to start the stream with

flags (2) -- can right now has one value:

flag

description

0x01

BINLOG_DUMP_NON_BLOCK

server_id (4) -- server id of this slave
binlog-filename (string.EOF) -- filename of the binlog on the master

flag	description
0x01	BINLOG_DUMP_NON_BLOCK

15.9.6. COM_BINLOG_DUMP_GTID

If the binlog-filename is empty, the server will send the binlog-stream of the first known binlog.

COM_BINLOG_DUMP_GTID:

request the Binlog Network Stream based on a GTID

Payload

1              [1e] COM_BINLOG_DUMP_GTID
2              flags
4              server-id
4              binlog-filename-len
string[len]    binlog-filename
8              binlog-pos
  if flags & BINLOG_THROUGH_GTID {
4              data-size
string[len]    data
  }

Fields

flags (2) --

flag	description
0x01	`BINLOG_DUMP_NON_BLOCK`
0x02	`BINLOG_THROUGH_POSITION`
0x04	`BINLOG_THROUGH_GTID`

server_id (4) -- server id of this slave
binlog-filename-len (4) -- length of binlog-filename
binlog-filename (string.fix_len) -- filename of the binlog on the master
binlog_pos (8) -- position in the binlog-file to start the stream with
data-len (4) -- length of data

data (string.fix_len) -- SID block

4                n_sids
  for n_sids {
string[16]       SID
8                n_intervals
    for n_intervals {
8                start (signed)
8                end (signed)
    }
  }

Returns

a Binlog Network Stream, a ERR_Packet or if BINLOG_DUMP_NON_BLOCK is set a EOF_Packet

15.9.7. COM_TABLE_DUMP

COM_TABLE_DUMP:

dump a table

Payload

1              [13] COM_TABLE_DUMP
1              database_len
string[$len]   [len=database_len] database name
1              table_len
string[$len]   [len=table_len] tablename

Returns

a table dump or ERR_Packet

New in 3.23.x

Deprecated in 5.0.0

15.9.8. COM_CONNECT_OUT

COM_CONNECT_OUT:

a internal command in the server

Payload

1              [14] COM_CONNECT_OUT

Returns

ERR_Packet

New in 3.23.x

15.9.9. COM_REGISTER_SLAVE

Registers a slave at the master. Should be sent before requesting a binlog events with COM_BINLOG_DUMP.

COM_REGISTER_SLAVE:

Payload

1              [14] COM_REGISTER_SLAVE
4              server-id
1              slaves hostname length
string[$len]   slaves hostname
1              slaves user len
string[$len]   slaves user
1              slaves password len
string[$len]   slaves password
2              slaves mysql-port
4              replication rank
4              master-id

Fields

server_id (4) -- the slaves server-id
slave_hostname (string.var_len) -- see --report-host, usually empty
slave_user (string.var_len) -- see --report-user, usually empty
slave_password (string.var_len) -- see --report-password, usually empty
slave_port (2) -- see --report-port, usually empty
replication_rank (4) -- ignored
master_id (4) -- usually 0. Appears as "master id" in SHOW SLAVE HOSTS on the master. Unknown what else it impacts.

Returns

OK_Packet or ERR_Packet

New in 4.0.x

Top / Previous / Next / Up / Table of Contents