MySQL :: MySQL Internals Manual :: 15.10 Row Based Replication

MySQL Internals Manual :: 15 MySQL Client/Server Protocol :: 15.10 Row Based Replication

« 15.9 Replication Protocol

15.11 Semi-Sync Replication »

Section Navigation [Toggle]

15.10. Row Based Replication

15.10.1. TABLE_MAP_EVENT
15.10.2. ROWS_EVENT
15.10.3. ROWS_QUERY_EVENT

New in 5.0.0

15.10.1. TABLE_MAP_EVENT

The first event used in Row Based Replication declares how a table that is about to be changed is defined.

TABLE_MAP_EVENT:

The TABLE_MAP_EVENT defines the structure if the tables that are about to be changed.

Payload

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

Fields

table_id (6) -- numeric table id
flags (2) -- flags
schema_name_length (1) -- length of the schema name
schema_name (string.var_len) -- [len=schema_name_length] schema name
table_name_length (1) -- length of the schema name
table_name (string.var_len) -- [len=table_name_length] table name
column_count (lenenc_int) -- number of columns in the table map
column_type_def (string.var_len) -- [len=column_count] array of column definitions, one byte per field type
column_meta_def (lenenc_str) -- array of metainfo per column, length is the overall length of the metainfo-array in bytes, the length of each metainfo field is dependent on the columns field type
null_bitmap (string.var_len) -- [len=(column_count + 8) / 7]

column_type_def

the column definitions. It is sent as length encoded string where the length of the string is the number of columns and each byte of it is the Protocol::ColumnType of the column.

column_meta_def

type specific meta-data for each column

type	meta-len
`Protocol::MYSQL_TYPE_STRING`	2
`Protocol::MYSQL_TYPE_VAR_STRING`	2
`Protocol::MYSQL_TYPE_VARCHAR`	2
`Protocol::MYSQL_TYPE_BLOB`	1
`Protocol::MYSQL_TYPE_DECIMAL`	2
`Protocol::MYSQL_TYPE_NEWDECIMAL`	2
`Protocol::MYSQL_TYPE_DOUBLE`	1
`Protocol::MYSQL_TYPE_FLOAT`	1
`Protocol::MYSQL_TYPE_ENUM`	2
`Protocol::MYSQL_TYPE_SET`	see MYSQL_TYPE_ENUM
`Protocol::MYSQL_TYPE_BIT`	0
`Protocol::MYSQL_TYPE_DATE`	0
`Protocol::MYSQL_TYPE_DATETIME`	0
`Protocol::MYSQL_TYPE_TIMESTAMP`	0
`Protocol::MYSQL_TYPE_TIME`	--
`Protocol::MYSQL_TYPE_TINY`	0
`Protocol::MYSQL_TYPE_SHORT`	0
`Protocol::MYSQL_TYPE_INT24`	0
`Protocol::MYSQL_TYPE_LONG`	0
`Protocol::MYSQL_TYPE_LONGLONG`	0

Protocol::MYSQL_TYPE_STRING

due to Bug37426 layout of the string meta-data is a bit tightly packed:

1              byte0
1              byte1

The two bytes encode type and length

NULL-bitmap

a bitmask contained a bit set for each column that can be NULL. The column-length is taken from the column-def

15.10.2. ROWS_EVENT

Three basic kinds of ROWS_EVENT exist:

event	SQL command	rows contains
WRITE_ROWS_EVENT	INSERT	the row data to insert.
DELETE_ROWS_EVENT	DELETE	as much data as needed to identify a row.
UPDATE_ROWS_EVENT	UPDATE	as much data as needed to identify a row + the data to change.

The format of the events changed between releases which is why 3 diffent sets of ROWS_EVENT exist:

Version 0

written from MySQL 5.1.0 to 5.1.15

UPDATE_ROWS_EVENTv0
WRITE_ROWS_EVENTv0
DELETE_ROWS_EVENTv0

Version 1

written from MySQL 5.1.15 to 5.6.x

UPDATE_ROWS_EVENTv1
WRITE_ROWS_EVENTv1
DELETE_ROWS_EVENTv1

added the after-image for the UPDATE_ROWS_EVENT

Version 2

written from MySQL 5.6.x

UPDATE_ROWS_EVENTv2
WRITE_ROWS_EVENTv2
DELETE_ROWS_EVENTv2

added the extra-data fields

The format is pretty similar for all the events:

Binlog::RowsEvent:

Payload

header:
  if post_header_len == 6 {
4                    table id
  } else {
6                    table id
  }
2                    flags
  if version == 2 {
2                    extra-data-length
string.var_len       extra-data
  }

body:
lenenc_int           number of columns
string.var_len       columns-present-bitmap1, length: (num of columns+7)/8
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       columns-present-bitmap2, length: (num of columns+7)/8
  }

rows:
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap1'+7)/8
string.var_len       value of each field as defined in table-map
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap2'+7)/8
string.var_len       value of each field as defined in table-map
  }
  ... repeat rows until event-end

Fields

table_id (6) -- If the table id is 0x00ffffff it is a dummy event that should have the end of statement flag set that declares that all table maps can be freed. Otherwise it refers to a table defined by TABLE_MAP_EVENT.

Note
if the post_header_length in the Binlog::FORMAT_DESCRIPTION_EVENT is 6 (instead of 8 or 10) the table_id is only 4 bytes long.

flags (2) --

hex	name
0x0001	end of statement
0x0002	no foreign key checks
0x0004	no unique key checks
0x0008	row has a columns

extra_data_len (2) -- length of extra_data (has to be ≥ 2)
extra_data (string.var_len) -- extra_data [length=extra_data_len - 2], zero or more Binlog::RowsEventExtraData

Binlog::RowsEventExtraData:

Payload

1              type
string.len     type-specific data

Fields

type (1) --

hex	name
0x00	RW_V_EXTRAINFO_TAG

Binlog::RowsEventExtraDataExtraInfo:

Payload

1              length
1              format
string.len     payload [length=length]

Fields

length (1) -- length of the payload

format (1) -- format of the payload

hex	format
0x00	NDB
0x40	OPEN1
0x41	OPEN2
0xFF	MULTI

payload (string.var_len) -- payload [length=length]

Binlog::RowsEventExtraDataExtraInfoFormat:: not used in 5.6.6 yet

15.10.3. ROWS_QUERY_EVENT

Binlog::RowsQueryEvent:

Query that caused the following ROWS_EVENT

Payload

1              length
string.EOF     query text

Fields

length (1) -- ignored
query (string.EOF) -- query text of the current ROWS_EVENT

Top / Previous / Next / Up / Table of Contents