A ROWS_EVENT_V1 is written for row based replication if data is inserted, deleted or updated:
|WRITE_ROWS_EVENT_V1||Insert new row|
|UPDATE_ROWS_EVENT_V1||Update existing row|
|DELETE_ROWS_EVENT_V1||Delete existing row|
- 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)
|0x0001||End of statement|
|0x0002||No foreign key checks|
|0x0004||No unique key checks|
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
- 1 byte
The types that aren't listed here do not store data in the the metadata block.
|MYSQL_TYPE_NULL||Bit set in null bitmap, no value in row data|
|MYSQL_TYPE_TINY||1 byte integer|
|MYSQL_TYPE_SHORT||2 byte integer|
|MYSQL_TYPE_YEAR||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 |
|MYSQL_TYPE_DOUBLE||8 byte floating point value (stored as a C |
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.
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.
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) seconds = value % 100 value = value / 100 minutes = value % 100 value = value / 100 hours = value % 100 value = value / 100 days = value % 100 value = value / 100 months = value % 100 value = value / 100 years = value
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.
value = read_3_byte_value(row_data) seconds = value % 100 value = value / 100 minutes = value % 100 value = value / 100 hours = value
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.
Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC).
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
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 ....