In this eighth post of our series, we decode the three row events — WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT — that carry the actual row data for INSERT, UPDATE, and DELETE operations in row-based replication.
In the previous posts, we decoded the events that set the stage for row-based replication: the GTID_LOG_EVENT identifies the transaction, the QUERY_EVENT opens it with BEGIN, and the TABLE_MAP_EVENT describes the table's schema. Now we finally arrive at the events that carry the actual data — the row events.
All three row events share the same base structure defined by the Rows_event class. The differences come down to which row images each event carries:
A row event always appears immediately after a TABLE_MAP_EVENT and references the table by its numeric ID. The row event's structure is identical regardless of which subclass it is — the deserialization constructor lives entirely in the Rows_event base class; the Write_rows_event, Update_rows_event, and Delete_rows_event subclass constructors simply call through to the parent.
MySQL has defined two generations of row event type codes:
| Version | WRITE | UPDATE | DELETE | Introduced | Status |
|---|
| V1 | 23 (0x17) | 24 (0x18) | 25 (0x19) | MySQL 5.1.16 | Obsolete |
| V2 | 30 (0x1e) | 31 (0x1f) | 32 (0x20) | MySQL 5.6 | Current |
As noted in the source code:
The V1 event numbers are used from 5.1.16 until mysql-5.6. Not generated since 8.2.0, and rejected by the applier since 8.4.0.
The only structural difference between V1 and V2 is in the post-header:
| V1 | V2 | |
|---|
| Post-header length | 8 bytes (ROWS_HEADER_LEN_V1) | 10 bytes (ROWS_HEADER_LEN_V2) |
| Fields | table_id (6B) + flags (2B) | table_id (6B) + flags (2B) + extra_data_length (2B) |
| Variable-length extra data | Not supported | Supported |
V2 added a 2-byte extra_data_length field that enables a variable-length section after the fixed post-header. This section can carry:
- NDB Cluster info (typecode NDB = 0): Extra row data specific to NDB storage engine
- Partition info (typecode PART = 1): The
partition_id for the target partition (and source_partition_id for UPDATE events)
For standard InnoDB tables without partitioning, the extra_data_length is simply 2 — meaning the variable-header contains only the 2-byte length field itself, with no additional payload.
The body format (column bitmaps and row data) is identical between V1 and V2. The deserializer simply checks whether post_header_len == ROWS_HEADER_LEN_V2 to decide whether to read the extra data section.
Our binary log was created by MySQL 8.0.40, so all events use the V2 format.
In our binary log, the three DML transactions each follow the same pattern — TABLE_MAP_EVENT immediately followed by the row event:
INSERT transaction:
Position 537: QUERY_EVENT (BEGIN)
Position 620: TABLE_MAP_EVENT (68 bytes)
Position 688: WRITE_ROWS_EVENT (49 bytes) ← INSERT INTO person VALUES (1, 'Marcelo')
Position 737: XID_EVENT
UPDATE transaction:
Position 847: QUERY_EVENT (BEGIN)
Position 939: TABLE_MAP_EVENT (68 bytes)
Position 1007: UPDATE_ROWS_EVENT (72 bytes) ← UPDATE person SET name = 'Marcelo Altmann'
Position 1079: XID_EVENT
DELETE transaction:
Position 1189: QUERY_EVENT (BEGIN)
Position 1272: TABLE_MAP_EVENT (68 bytes)
Position 1340: DELETE_ROWS_EVENT (57 bytes) ← DELETE FROM person WHERE ID = 1
Position 1397: XID_EVENT
Notice the sizes: the UPDATE event (72 bytes) is the largest because it carries both the old and new row. The DELETE event (57 bytes) is larger than the WRITE event (49 bytes) because it stores 'Marcelo Altmann' (15 bytes) rather than 'Marcelo' (7 bytes).
The post-header field offsets are defined as constants in rows_event.h:
#define ROWS_MAPID_OFFSET 0
#define ROWS_FLAGS_OFFSET 6
#define ROWS_VHLEN_OFFSET 8
The complete layout, as documented in the Rows_event class and serialized by write_data_header() and write_data_body():
| Field | Size | Description |
|---|
| Post-Header (10 bytes) | | |
| table_id | 6 bytes | Numeric table identifier — must match a preceding TABLE_MAP_EVENT |
| flags | 2 bytes | Row event flags (enum_flag) |
| extra_data_length | 2 bytes | Total size of variable-length extra data (includes these 2 bytes; V2 only) |
| Body | | |
| m_width | Packed int | Number of columns in the table |
| columns_before_image | (m_width + 7) / 8 bytes | Bitmap of columns present in the before-image (UPDATE and DELETE only) |
| columns_after_image | (m_width + 7) / 8 bytes | Bitmap of columns present in the after-image (UPDATE only — for WRITE, the single bitmap serves as the after-image) |
| row_data | Variable | One or more rows, packed end-to-end until the checksum |
The key difference in the body between the three events is the number of column bitmaps and how the row data is structured:
| Event | Bitmaps | Row Data Layout |
|---|
| WRITE_ROWS | 1 (after-image) | [null_bitmap + column_values] per row |
| UPDATE_ROWS | 2 (before + after) | [null_bitmap + column_values] + [null_bitmap + column_values] per row (old, then new) |
| DELETE_ROWS | 1 (before-image) | [null_bitmap + column_values] per row |
This layout is documented in sql/rpl_record.h:
For WRITE_ROWS_EVENT: +--------------+
| after-image |
+--------------+
For DELETE_ROWS_EVENT: +--------------+
| before-image |
+--------------+
For UPDATE_ROWS_EVENT: +--------------+-------------+
| before-image | after-image |
+--------------+-------------+
Let's start with the simplest case — the INSERT. This event records the row (1, 'Marcelo') being inserted into the presentation.person table.
$ xxd -s 688 -l 49 binlog.000024
000002b0: 3210 3568 1e01 0000 0031 0000 00e1 0200 2.5h.....1......
000002c0: 0000 005f 0000 0000 0001 0002 0002 ff00 ..._............
000002d0: 0100 0000 0700 4d61 7263 656c 6f8e 965c ......Marcelo..\
000002e0: 4f O
The event is 49 bytes: 19-byte header + 10-byte post-header + 16-byte body + 4-byte checksum.
32103568 1e 01000000 31000000 e1020000 0000
│ │ │ │ │ │
│ │ │ │ │ └─→ Flags: 0x0000
│ │ │ │ └───────────→ Next Position: 737
│ │ │ └────────────────────→ Event Size: 49 bytes
│ │ └─────────────────────────────→ Server ID: 1
│ └────────────────────────────────→ Event Type: 30 (WRITE_ROWS_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308018
Quick cross-check: 688 + 49 = 737.
5f0000000000 0100 0200
│ │ │
│ │ └───→ extra_data_length: 2 (no extra data)
│ └────────→ flags: 0x0001 (STMT_END_F)
└─────────────────────→ table_id: 95
Table ID (6 bytes): 5f 00 00 00 00 00 → 95. This matches the TABLE_MAP_EVENT at position 620 that we decoded in Part 7. The constructor reads this with reader.read<uint64_t>(6).
Flags (2 bytes): 01 00 → 0x0001 (STMT_END_F). This indicates the last (and here, only) row event for this statement. A single INSERT that affects many rows could be split across multiple WRITE_ROWS_EVENTs — only the final one has STMT_END_F set.
The full flags enum is defined in Rows_event::enum_flag:
Why is COMPLETE_ROWS_F not set? You might expect this flag on our INSERT event since all columns are present. But COMPLETE_ROWS_F is never written to the binary log by the source. The source constructor initializes m_flags = 0 and only sets NO_FOREIGN_KEY_CHECKS_F, RELAXED_UNIQUE_CHECKS_F, or USE_SQL_FOREIGN_KEY_F based on session variables. COMPLETE_ROWS_F is set on the replica side, on the in-memory event object during do_apply_event(), when the applier confirms that the event's columns match the replica's table. It then uses this flag to optimize the UPDATE apply path — if the row is complete, there's no need to fetch the existing row to fill in missing columns.
Extra Data Length (2 bytes): 02 00 → 2. This field specifies the total size of the variable-length extra data section, including the 2 bytes of this length field itself. The deserializer subtracts 2 to get the actual payload size:
READER_TRY_SET(var_header_len, read<uint16_t>);
var_header_len -= 2;
A value of 2 means 2 - 2 = 0 bytes of extra data — the common case for non-NDB, non-partitioned tables. On the write side, the minimum value is always EXTRA_ROW_INFO_HEADER_LENGTH (= 2), which is incremented only when NDB or partition info is present.
02 ff 00 01000000 0700 4d617263656c6f
│ │ └──────────────────────────────┘
│ │ row_data (1 row: after-image)
│ └──→ columns_used bitmap
└─────→ m_width (column count)
Column count (m_width): 02 → 2. Read by net_field_length_ll(), the same packed integer encoding from Part 1.
Columns-used bitmap (1 byte): ff. This bitmap has (m_width + 7) / 8 = 1 byte and indicates which table columns are included in the row data. With m_width = 2, only bits 0 and 1 are meaningful — the remaining 6 bits are padding. Both columns are present.
For WRITE_ROWS_EVENT and DELETE_ROWS_EVENT, there is a single bitmap. For UPDATE_ROWS_EVENT, there are two bitmaps — one for the before-image and one for the after-image:
READER_TRY_CALL(assign, &columns_before_image, n_bits_len);
if (event_type == UPDATE_ROWS_EVENT ||
event_type == PARTIAL_UPDATE_ROWS_EVENT) {
READER_TRY_CALL(assign, &columns_after_image, n_bits_len);
} else
columns_after_image = columns_before_image;
Note: Which columns appear in the image depends on the binlog_row_image setting. With the default FULL, all columns are included. With MINIMAL, only columns needed to identify the row (typically the primary key) appear in the before-image, and only modified columns in the after-image.
Row data — after-image (13 bytes):
Each row in the data starts with a null bitmap followed by column values, as packed by pack_row():
00 01000000 0700 4d617263656c6f
│ │ │ │
│ │ │ └─→ "Marcelo" (7 bytes, UTF-8)
│ │ └──────→ VARCHAR length: 7 (2-byte LE)
│ └───────────────→ Column 1 (INT): 1
└──────────────────→ Null bitmap: 0x00 (no NULLs)
Null bitmap (1 byte): The null bitmap has one bit per column present in the columns_used bitmap. Its size is (N + 7) / 8 bytes, where N is the number of columns marked as "used". From pack_row():
Bit_writer null_bits(pack_ptr);
pack_ptr += (image_column_count + 7) / 8;
A bit set to 1 means the column is NULL and its value is not stored. A bit set to 0 means the column has a value that follows in the row data.
00 = 0000 0000
││
│└─→ Column 1 (ID): bit 0 = 0 → not NULL
└──→ Column 2 (name): bit 1 = 0 → not NULL
Column 1 — ID (INT): 01 00 00 00 → 1
MYSQL_TYPE_LONG (type code 3) is stored as a 4-byte little-endian integer.
Column 2 — name (VARCHAR): 07 00 4d 61 72 63 65 6c 6f → "Marcelo"
MYSQL_TYPE_VARCHAR (type code 15) is stored as a length-prefixed byte string. The size of the length prefix depends on the column's field_length (maximum byte length) from the TABLE_MAP_EVENT metadata. From Field_varstring::pack():
*to++ = length & 0xFF;
if (length_bytes == 2 && max_length >= 2) *to++ = (length >> 8) & 0xFF;
The rule is straightforward:
- If
field_length ≤ 255: 1-byte length prefix
- If
field_length > 255: 2-byte little-endian length prefix
From the TABLE_MAP_EVENT in Part 7, the field_length for our name column is 600 (VARCHAR(150) × 4 bytes/char for utf8mb4). Since 600 > 255, we use a 2-byte length prefix:
07 00 → little-endian uint16 → 7 (byte length of the string)
4d 61 72 63 65 6c 6f → "Marcelo"
We can cross-check with mysqlbinlog:
$ mysqlbinlog --no-defaults -vvv binlog.000024
### INSERT INTO `presentation`.`person`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Marcelo' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
The UPDATE event introduces the key difference: each row is stored as a pair — the before-image (old values) followed by the after-image (new values). As the Update_rows_event class documentation states:
The row data consists of pairs of row data: one row for the old data and one row for the new data.
This event records UPDATE person SET name = 'Marcelo Altmann' WHERE ID = 1.
$ xxd -s 1007 -l 72 binlog.000024
000003ef: 3210 3568 1f01 0000 0048 0000 0037 0400 2.5h.....H...7..
000003ff: 0000 005f 0000 0000 0001 0002 0002 ffff ..._............
0000040f: 0001 0000 0007 004d 6172 6365 6c6f 0001 .......Marcelo..
0000041f: 0000 000f 004d 6172 6365 6c6f 2041 6c74 .....Marcelo Alt
0000042f: 6d61 6e6e 8152 5d72 mann.R]r
The event is 72 bytes: 19-byte header + 10-byte post-header + 39-byte body + 4-byte checksum.
32103568 1f 01000000 48000000 37040000 0000
│ │ │ │ │ │
│ │ │ │ │ └─→ Flags: 0x0000
│ │ │ │ └───────────→ Next Position: 1079
│ │ │ └────────────────────→ Event Size: 72 bytes
│ │ └─────────────────────────────→ Server ID: 1
│ └────────────────────────────────→ Event Type: 31 (UPDATE_ROWS_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308018
Cross-check: 1007 + 72 = 1079.
The post-header is identical in structure to the WRITE_ROWS_EVENT:
5f0000000000 0100 0200
Table ID 95, flags STMT_END_F, extra data length 2 (no extra data).
Here's where the UPDATE event differs. After m_width, there are two column bitmaps instead of one:
02 ff ff 00 01000000 0700 4d617263656c6f 00 01000000 0f00 4d617263656c6f20416c746d616e6e
│ │ │ └────────────────────────────────────────────────────────────────────────────────┘
│ │ │ row_data (1 row pair: before-image + after-image)
│ │ └──→ columns_after_image bitmap: both columns
│ └─────→ columns_before_image bitmap: both columns
└────────→ m_width: 2
Column count (m_width): 02 → 2
Columns before-image bitmap: ff → both columns present in the old row
Columns after-image bitmap: ff → both columns present in the new row
Row pair — Before-image (old row, 13 bytes):
00 01000000 0700 4d617263656c6f
│ │ │ │
│ │ │ └─→ "Marcelo"
│ │ └──────→ VARCHAR length: 7
│ └───────────────→ ID: 1
└──────────────────→ Null bitmap: no NULLs
Row pair — After-image (new row, 21 bytes):
00 01000000 0f00 4d617263656c6f20416c746d616e6e
│ │ │ │
│ │ │ └─→ "Marcelo Altmann"
│ │ └──────→ VARCHAR length: 15
│ └───────────────→ ID: 1
└──────────────────→ Null bitmap: no NULLs
The before-image and after-image are packed back-to-back with no separator — the parser knows the boundary because it walks each row's columns one by one, consuming the exact number of bytes each type requires.
Cross-check with mysqlbinlog:
### UPDATE `presentation`.`person`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Marcelo' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Marcelo Altmann' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
Notice that even though only name changed, both columns appear in both images because binlog_row_image = FULL (the default). With binlog_row_image = MINIMAL, the before-image would contain only the primary key (ID), and the after-image would contain only the modified column (name) plus the primary key.
The DELETE event is structurally identical to the WRITE event — a single bitmap and a single row image — except the image is the before-image (the row being deleted) instead of the after-image.
This event records DELETE FROM person WHERE ID = 1.
$ xxd -s 1340 -l 57 binlog.000024
0000053c: 3210 3568 2001 0000 0039 0000 0075 0500 2.5h ....9...u..
0000054c: 0000 005f 0000 0000 0001 0002 0002 ff00 ..._............
0000055c: 0100 0000 0f00 4d61 7263 656c 6f20 416c ......Marcelo Al
0000056c: 746d 616e 6ead 0437 41 tmann..7A
The event is 57 bytes: 19-byte header + 10-byte post-header + 24-byte body + 4-byte checksum.
32103568 20 01000000 39000000 75050000 0000
│ │ │ │ │ │
│ │ │ │ │ └─→ Flags: 0x0000
│ │ │ │ └───────────→ Next Position: 1397
│ │ │ └────────────────────→ Event Size: 57 bytes
│ │ └─────────────────────────────→ Server ID: 1
│ └────────────────────────────────→ Event Type: 32 (DELETE_ROWS_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308018
Cross-check: 1340 + 57 = 1397.
5f0000000000 0100 0200
Same as before: table ID 95, flags STMT_END_F, extra data length 2.
02 ff 00 01000000 0f00 4d617263656c6f20416c746d616e6e
│ │ └──────────────────────────────────────────────┘
│ │ row_data (1 row: before-image)
│ └──→ columns_used bitmap: both columns
└─────→ m_width: 2
Row data — before-image (the deleted row):
00 01000000 0f00 4d617263656c6f20416c746d616e6e
│ │ │ │
│ │ │ └─→ "Marcelo Altmann" (15 bytes)
│ │ └──────→ VARCHAR length: 15
│ └───────────────→ ID: 1
└──────────────────→ Null bitmap: no NULLs
This is the row as it existed just before deletion — after the UPDATE in the previous transaction had already changed the name from 'Marcelo' to 'Marcelo Altmann'.
Cross-check with mysqlbinlog:
### DELETE FROM `presentation`.`person`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Marcelo Altmann' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
Let's look at the three events side by side to see the pattern clearly:
| WRITE (pos 688) | UPDATE (pos 1007) | DELETE (pos 1340) | |
|---|
| Event type | 30 (0x1e) | 31 (0x1f) | 32 (0x20) |
| Event size | 49 bytes | 72 bytes | 57 bytes |
| Table ID | 95 | 95 | 95 |
| Flags | STMT_END_F | STMT_END_F | STMT_END_F |
| Column bitmaps | 1 (after) | 2 (before + after) | 1 (before) |
| Row images | (1, 'Marcelo') | (1, 'Marcelo') → (1, 'Marcelo Altmann') | (1, 'Marcelo Altmann') |
| Body size | 16 bytes | 39 bytes | 24 bytes |
The data tells a consistent story: the INSERT created the row, the UPDATE modified the name (captured in a before/after pair), and the DELETE removed the final version of the row.
All three events above were recorded with the default binlog_row_image = FULL, which is why every row image contains all columns. The binlog_row_image variable controls how many columns are included, and it directly affects the columns-used bitmap and the row data in each event.
The three possible values are defined in enum_binlog_row_image:
| Value | Constant | Before-Image | After-Image |
|---|
| 2 | BINLOG_ROW_IMAGE_FULL | All columns | All columns |
| 1 | BINLOG_ROW_IMAGE_NOBLOB | All columns except BLOB/TEXT (unless they are part of the PK) | All columns except unchanged BLOB/TEXT |
| 0 | BINLOG_ROW_IMAGE_MINIMAL | Primary key columns only | Modified columns only |
The column selection happens in two stages on the source:
Stage 1 — mark_columns_per_binlog_row_image() is called during statement execution. It adjusts the table's read_set (used for before-images) and write_set (used for after-images) based on the setting:
switch (thd->variables.binlog_row_image) {
case BINLOG_ROW_IMAGE_FULL:
if (s->primary_key < MAX_KEY) bitmap_set_all(read_set);
bitmap_set_all(write_set);
break;
case BINLOG_ROW_IMAGE_MINIMAL:
if (s->primary_key < MAX_KEY)
mark_columns_used_by_index_no_reset(s->primary_key, read_set);
break;
...
}
With FULL, both bitmaps get all bits set. With MINIMAL, only the primary key is marked in read_set, and write_set is left with only the columns the statement actually modifies.
Stage 2 — When the row is about to be packed into the event, binlog_prepare_row_images() further trims the read_set for UPDATE and DELETE events, keeping only the PK columns for MINIMAL.
The event constructors then copy these bitmaps into m_cols:
With MINIMAL, the columns-used bitmap in the event has fewer bits set. For example, consider our person table (ID INT PRIMARY KEY, name VARCHAR(150)) with UPDATE person SET name = 'Marcelo Altmann' WHERE ID = 1:
With FULL (our binary log):
columns_before_image: 0xff = 1111 1111 → bits 0,1 set (ID + name)
columns_after_image: 0xff = 1111 1111 → bits 0,1 set (ID + name)
Before-image: ID=1, name='Marcelo'
After-image: ID=1, name='Marcelo Altmann'
With MINIMAL (hypothetical):
columns_before_image: 0x01 = 0000 0001 → bit 0 set (ID only — the PK)
columns_after_image: 0x02 = 0000 0010 → bit 1 set (name only — the modified column)
Before-image: ID=1
After-image: name='Marcelo Altmann'
The difference is significant: the MINIMAL event omits name from the before-image (the PK alone is sufficient to locate the row) and omits ID from the after-image (it wasn't modified).
When parsing a row event, the columns-used bitmap is the authoritative source of which columns are present in the row data. Each bit corresponds to a column index from the TABLE_MAP_EVENT:
- Read the bitmap — bit N set means column N is present
- Count the set bits — this determines the null bitmap size for each row:
(set_bit_count + 7) / 8 bytes
- The null bitmap inside each row has one bit per present column (not per table column)
- Column values follow only for present, non-NULL columns
A parser that doesn't respect the bitmap will misinterpret the row data, because the values are packed with no separators — skipping a column means its bytes simply aren't there.
With MINIMAL, it's even possible for the row data to be empty. If an INSERT uses all default values on a table where no columns need to be explicitly written:
SET SESSION binlog_row_image = MINIMAL;
CREATE TABLE t1 (c1 INT DEFAULT 100);
INSERT INTO t1 VALUES ();
The write_set is empty, so the columns bitmap is 0x00, the null bitmap is 0 bytes, and there are no column values at all — the row data section is zero bytes. The replica uses the column defaults to reconstruct the row.
Position 688: WRITE_ROWS_EVENT (49 bytes)
┌─────────────────────────────────────────────────────────────────────────┐
│ COMMON HEADER (19 bytes) │
├─────────────────────────────────────────────────────────────────────────┤
│ 32103568 │ 1e │ 01000000 │ 31000000 │ e1020000 │ 0000 │
│ Timestamp │ Type │ ServerID │ Size │ NextPos │ Flags │
│ 1748308018 │ 30 │ 1 │ 49 │ 737 │ 0x0000 │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ POST-HEADER (10 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 5f0000000000 │ table_id: 95 │
│ 0100 │ flags: 0x0001 (STMT_END_F) │
│ 0200 │ extra_data_length: 2 (no extra data) │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ BODY (16 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 02 │ m_width: 2 columns │
│ ff │ columns_used: both columns │
│ │ │
│ │ After-image (inserted row): │
│ 00 │ null_bitmap: 0x00 (no NULLs) │
│ 01000000 │ ID (INT): 1 │
│ 0700 4d617263656c6f │ name (VARCHAR): "Marcelo" │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ CHECKSUM (4 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 8e965c4f │ CRC32 │
└──────────────────────────────┴──────────────────────────────────────────┘
Position 1007: UPDATE_ROWS_EVENT (72 bytes)
┌─────────────────────────────────────────────────────────────────────────┐
│ COMMON HEADER (19 bytes) │
├─────────────────────────────────────────────────────────────────────────┤
│ 32103568 │ 1f │ 01000000 │ 48000000 │ 37040000 │ 0000 │
│ Timestamp │ Type │ ServerID │ Size │ NextPos │ Flags │
│ 1748308018 │ 31 │ 1 │ 72 │ 1079 │ 0x0000 │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ POST-HEADER (10 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 5f0000000000 │ table_id: 95 │
│ 0100 │ flags: 0x0001 (STMT_END_F) │
│ 0200 │ extra_data_length: 2 (no extra data) │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ BODY (39 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 02 │ m_width: 2 columns │
│ ff │ columns_before_image: both columns │
│ ff │ columns_after_image: both columns │
│ │ │
│ │ Before-image (old row): │
│ 00 │ null_bitmap: 0x00 (no NULLs) │
│ 01000000 │ ID (INT): 1 │
│ 0700 4d617263656c6f │ name (VARCHAR): "Marcelo" │
│ │ │
│ │ After-image (new row): │
│ 00 │ null_bitmap: 0x00 (no NULLs) │
│ 01000000 │ ID (INT): 1 │
│ 0f00 4d617263656c6f20 │ name (VARCHAR): "Marcelo Altmann" │
│ 416c746d616e6e │ │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ CHECKSUM (4 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 81525d72 │ CRC32 │
└──────────────────────────────┴──────────────────────────────────────────┘
Position 1340: DELETE_ROWS_EVENT (57 bytes)
┌─────────────────────────────────────────────────────────────────────────┐
│ COMMON HEADER (19 bytes) │
├─────────────────────────────────────────────────────────────────────────┤
│ 32103568 │ 20 │ 01000000 │ 39000000 │ 75050000 │ 0000 │
│ Timestamp │ Type │ ServerID │ Size │ NextPos │ Flags │
│ 1748308018 │ 32 │ 1 │ 57 │ 1397 │ 0x0000 │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ POST-HEADER (10 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 5f0000000000 │ table_id: 95 │
│ 0100 │ flags: 0x0001 (STMT_END_F) │
│ 0200 │ extra_data_length: 2 (no extra data) │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ BODY (24 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 02 │ m_width: 2 columns │
│ ff │ columns_used: both columns │
│ │ │
│ │ Before-image (deleted row): │
│ 00 │ null_bitmap: 0x00 (no NULLs) │
│ 01000000 │ ID (INT): 1 │
│ 0f00 4d617263656c6f20 │ name (VARCHAR): "Marcelo Altmann" │
│ 416c746d616e6e │ │
└──────────────────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ CHECKSUM (4 bytes) │
├──────────────────────────────┬──────────────────────────────────────────┤
│ ad043741 │ CRC32 │
└──────────────────────────────┴──────────────────────────────────────────┘
The script below first parses the TABLE_MAP_EVENT to learn the table schema, then decodes all three row events — handling the different bitmap and image layouts for each event type:
import struct
WRITE_ROWS_EVENT = 30
UPDATE_ROWS_EVENT = 31
DELETE_ROWS_EVENT = 32
EVENT_NAMES = {
WRITE_ROWS_EVENT: 'WRITE_ROWS_EVENT',
UPDATE_ROWS_EVENT: 'UPDATE_ROWS_EVENT',
DELETE_ROWS_EVENT: 'DELETE_ROWS_EVENT',
}
TYPE_NAMES = {3: 'INT', 15: 'VARCHAR'}
def read_packed_int(data, offset):
"""Read a MySQL packed integer (net_field_length_ll encoding)."""
first = data[offset]
if first < 251:
return first, offset + 1
elif first == 0xFC:
return struct.unpack('<H', data[offset+1:offset+3])[0], offset + 3
elif first == 0xFD:
return struct.unpack('<I', data[offset+1:offset+4] + b'\x00')[0], offset + 4
elif first == 0xFE:
return struct.unpack('<Q', data[offset+1:offset+9])[0], offset + 9
def parse_table_map(f, pos):
"""Parse a TABLE_MAP_EVENT and return the schema info."""
f.seek(pos)
header = f.read(19)
_, _, _, event_size, _, _ = struct.unpack('<IBIIIH', header)
post = f.read(8)
table_id = struct.unpack('<Q', post[:6] + b'\x00\x00')[0]
body = f.read(event_size - 19 - 8 - 4)
o = 0
db_len = body[o]; o += 1
db_name = body[o:o+db_len].decode(); o += db_len + 1
tbl_len = body[o]; o += 1
tbl_name = body[o:o+tbl_len].decode(); o += tbl_len + 1
colcnt, o = read_packed_int(body, o)
col_types = list(body[o:o+colcnt]); o += colcnt
meta_len, o = read_packed_int(body, o)
col_metadata = body[o:o+meta_len]
col_meta = []
mi = 0
for t in col_types:
if t == 15:
fl = struct.unpack('<H', col_metadata[mi:mi+2])[0]
col_meta.append({'field_length': fl})
mi += 2
else:
col_meta.append({})
return {
'table_id': table_id,
'db': db_name,
'table': tbl_name,
'col_types': col_types,
'col_meta': col_meta,
'colcnt': colcnt,
}
def decode_row(body, o, col_types, col_meta, used_cols):
"""Decode a single row image. Returns (values_dict, new_offset)."""
num_used = len(used_cols)
null_bitmap_len = (num_used + 7) // 8
null_bits = body[o:o+null_bitmap_len]; o += null_bitmap_len
nulls = set()
for i in range(num_used):
if null_bits[i // 8] & (1 << (i % 8)):
nulls.add(used_cols[i])
values = {}
for col_idx in used_cols:
if col_idx in nulls:
values[col_idx] = None
continue
col_type = col_types[col_idx]
if col_type == 3:
values[col_idx] = struct.unpack('<i', body[o:o+4])[0]
o += 4
elif col_type == 15:
fl = col_meta[col_idx].get('field_length', 0)
if fl > 255:
slen = struct.unpack('<H', body[o:o+2])[0]; o += 2
else:
slen = body[o]; o += 1
values[col_idx] = body[o:o+slen].decode('utf-8')
o += slen
else:
values[col_idx] = f'<unsupported type {col_type}>'
return values, o
def format_row(values, col_types):
"""Format a row as a readable string."""
parts = []
for i in sorted(values.keys()):
name = TYPE_NAMES.get(col_types[i], f'type_{col_types[i]}')
val = 'NULL' if values[i] is None else repr(values[i])
parts.append(f"@{i+1}={val} ({name})")
return ', '.join(parts)
with open('binlog.000024', 'rb') as f:
schema = parse_table_map(f, 620)
print(f"Table: {schema['db']}.{schema['table']} "
f"(ID={schema['table_id']})")
for i, t in enumerate(schema['col_types']):
extra = ''
if 'field_length' in schema['col_meta'][i]:
extra = f", field_length={schema['col_meta'][i]['field_length']}"
print(f" Column {i+1}: {TYPE_NAMES.get(t, f'type_{t}')}{extra}")
row_event_positions = [688, 1007, 1340]
for pos in row_event_positions:
f.seek(pos)
header = f.read(19)
timestamp, event_type, server_id, event_size, next_pos, flags = \
struct.unpack('<IBIIIH', header)
print(f"\n{'='*60}")
print(f"{EVENT_NAMES[event_type]} at position {pos} "
f"({event_size} bytes)")
post = f.read(10)
tbl_id = struct.unpack('<Q', post[:6] + b'\x00\x00')[0]
row_flags = struct.unpack('<H', post[6:8])[0]
extra_data_len = struct.unpack('<H', post[8:10])[0]
flag_names = []
if row_flags & 0x01: flag_names.append('STMT_END_F')
if row_flags & 0x02: flag_names.append('NO_FOREIGN_KEY_CHECKS_F')
if row_flags & 0x04: flag_names.append('RELAXED_UNIQUE_CHECKS_F')
if row_flags & 0x08: flag_names.append('COMPLETE_ROWS_F')
print(f" Table ID: {tbl_id}")
print(f" Flags: {' | '.join(flag_names)}")
print(f" Extra data: {extra_data_len - 2} bytes")
extra_payload = extra_data_len - 2
if extra_payload > 0:
f.read(extra_payload)
body = f.read(event_size - 19 - 10 - extra_payload - 4)
o = 0
m_width, o = read_packed_int(body, o)
bitmap_len = (m_width + 7) // 8
cols_before_raw = body[o:o+bitmap_len]; o += bitmap_len
cols_before = [i for i in range(m_width)
if cols_before_raw[i // 8] & (1 << (i % 8))]
if event_type == UPDATE_ROWS_EVENT:
cols_after_raw = body[o:o+bitmap_len]; o += bitmap_len
cols_after = [i for i in range(m_width)
if cols_after_raw[i // 8] & (1 << (i % 8))]
print(f" Columns (before-image): {[c+1 for c in cols_before]}")
print(f" Columns (after-image): {[c+1 for c in cols_after]}")
else:
cols_after = cols_before
image_type = "after" if event_type == WRITE_ROWS_EVENT \
else "before"
print(f" Columns ({image_type}-image): "
f"{[c+1 for c in cols_before]}")
row_num = 0
while o < len(body):
row_num += 1
if event_type == UPDATE_ROWS_EVENT:
before_vals, o = decode_row(
body, o, schema['col_types'],
schema['col_meta'], cols_before)
after_vals, o = decode_row(
body, o, schema['col_types'],
schema['col_meta'], cols_after)
print(f"\n Row {row_num}:")
print(f" Before: "
f"{format_row(before_vals, schema['col_types'])}")
print(f" After: "
f"{format_row(after_vals, schema['col_types'])}")
elif event_type == WRITE_ROWS_EVENT:
vals, o = decode_row(
body, o, schema['col_types'],
schema['col_meta'], cols_after)
print(f"\n Row {row_num} (after-image):")
print(f" {format_row(vals, schema['col_types'])}")
else:
vals, o = decode_row(
body, o, schema['col_types'],
schema['col_meta'], cols_before)
print(f"\n Row {row_num} (before-image):")
print(f" {format_row(vals, schema['col_types'])}")
Output:
Table: presentation.person (ID=95)
Column 1: INT
Column 2: VARCHAR, field_length=600
============================================================
WRITE_ROWS_EVENT at position 688 (49 bytes)
Table ID: 95
Flags: STMT_END_F
Extra data: 0 bytes
Columns (after-image): [1, 2]
Row 1 (after-image):
@1=1 (INT), @2='Marcelo' (VARCHAR)
============================================================
UPDATE_ROWS_EVENT at position 1007 (72 bytes)
Table ID: 95
Flags: STMT_END_F
Extra data: 0 bytes
Columns (before-image): [1, 2]
Columns (after-image): [1, 2]
Row 1:
Before: @1=1 (INT), @2='Marcelo' (VARCHAR)
After: @1=1 (INT), @2='Marcelo Altmann' (VARCHAR)
============================================================
DELETE_ROWS_EVENT at position 1340 (57 bytes)
Table ID: 95
Flags: STMT_END_F
Extra data: 0 bytes
Columns (before-image): [1, 2]
Row 1 (before-image):
@1=1 (INT), @2='Marcelo Altmann' (VARCHAR)
Note: The binary log files used in this series (binlog.000024, binlog_gtid_tag.000001, and others) are available at github.com/altmannmarcelo/presentations/tree/main/binlog.
- Rows_event — base class definition with binary format documentation and enum_flag (
STMT_END_F, NO_FOREIGN_KEY_CHECKS_F, etc.)
- Write_rows_event, Update_rows_event, Delete_rows_event — subclass definitions
- Rows_event::Rows_event(const char*, ...) — deserialization constructor (reads table_id, flags, extra data, column bitmaps, and row data)
- ROWS_HEADER_LEN_V1 = 8 and ROWS_HEADER_LEN_V2 = 10 — post-header length constants
- ROWS_MAPID_OFFSET, ROWS_FLAGS_OFFSET, ROWS_VHLEN_OFFSET — post-header field offsets
- enum_extra_row_info_typecode — NDB (0) and PART (1) extra data types
- OBSOLETE_WRITE_ROWS_EVENT_V1, OBSOLETE_UPDATE_ROWS_EVENT_V1, OBSOLETE_DELETE_ROWS_EVENT_V1 — V1 event type codes
- Rows_log_event::write_data_header() — post-header serialization
- Rows_log_event::write_data_body() — body serialization (m_width, column bitmaps, row data)
- pack_row() — row image packing (null bitmap + column values)
- Field_varstring::pack() — VARCHAR packing with 1- or 2-byte length prefix
- enum_field_types — MySQL column type codes
- Row format documentation — layout of before-image and after-image per event type
Next up: Part 9: XID_EVENT — Transaction Commit
This series is based on a presentation given at the MySQL Online Summit. The goal is to help MySQL users understand what goes under the hood of replication by manually decoding binary log files.
