Replication Internals: Decoding the MySQL Binary Log - Part 8: Row Events — WRITE_ROWS, UPDATE_ROWS, and DELETE_ROWS

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.

Introduction

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.

V1 vs V2: Two Versions of Row Events

MySQL has defined two generations of row event type codes:

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:

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.

Event Locations

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).

Row Event Structure

The post-header field offsets are defined as constants in rows_event.h:

#define ROWS_MAPID_OFFSET  0    // table_id starts at offset 0
#define ROWS_FLAGS_OFFSET  6    // flags at offset 6
#define ROWS_VHLEN_OFFSET  8    // variable-header length at offset 8 (V2 only)

The complete layout, as documented in the Rows_event class and serialized by write_data_header() and write_data_body():

The key difference in the body between the three events is the number of column bitmaps and how the row data is structured:

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

Decoding the WRITE_ROWS_EVENT (Position 688)

Let's start with the simplest case — the INSERT. This event records the row (1, 'Marcelo') being inserted into the presentation.person table.

Reading the Raw Bytes

$ 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.

Common Header (19 bytes)

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.

Post-Header (10 bytes)

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.

Body (16 bytes)

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():

/* Length always stored little-endian */
*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 */

Decoding the UPDATE_ROWS_EVENT (Position 1007)

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.

Reading the Raw Bytes

$ 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.

Common Header

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.

Post-Header

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).

Body (39 bytes)

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.

Decoding the DELETE_ROWS_EVENT (Position 1340)

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.

Reading the Raw Bytes

$ 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.

Common Header

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.

Post-Header

5f0000000000 0100 0200

Same as before: table ID 95, flags STMT_END_F, extra data length 2.

Body (24 bytes)

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 */

Comparing All Three Events

Let's look at the three events side by side to see the pattern clearly:

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.

Minimal Row-Based Replication (binlog_row_image)

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:

How It Works on the Source

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:
    /* mark the primary key if available in the read_set */
    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:

• Write_rows_log_event: m_cols ← tbl_arg->write_set
• Update_rows_log_event: m_cols ← tbl_arg->read_set (before-image)
• Delete_rows_log_event: m_cols ← tbl_arg->read_set

What Changes in the Binary Log

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).

How to Know Which Columns Are Present

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:

1. Read the bitmap — bit N set means column N is present
2. Count the set bits — this determines the null bitmap size for each row: (set_bit_count + 7) / 8 bytes
3. The null bitmap inside each row has one bit per present column (not per table column)
4. 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.

Edge Case: Empty Row Data

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.

Visual Breakdown

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                                    │
└──────────────────────────────┴──────────────────────────────────────────┘

Try It Yourself

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

# Event type constants (from binlog_event.h)
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',
}

# Column type names (from include/field_types.h)
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]

    # Build per-column field_length for VARCHAR
    col_meta = []
    mi = 0
    for t in col_types:
        if t == 15:  # MYSQL_TYPE_VARCHAR
            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:  # MYSQL_TYPE_LONG (INT)
            values[col_idx] = struct.unpack('<i', body[o:o+4])[0]
            o += 4
        elif col_type == 15:  # MYSQL_TYPE_VARCHAR
            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:
    # Parse the TABLE_MAP_EVENT (same schema for all three transactions)
    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}")

    # Decode the three row events
    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-header (10 bytes for V2)
        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")

        # Skip extra data payload
        extra_payload = extra_data_len - 2
        if extra_payload > 0:
            f.read(extra_payload)

        # Body
        body = f.read(event_size - 19 - 10 - extra_payload - 4)
        o = 0

        # m_width
        m_width, o = read_packed_int(body, o)
        bitmap_len = (m_width + 7) // 8

        # Column bitmaps
        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]}")

        # Parse rows
        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:  # DELETE_ROWS_EVENT
                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.

References

• 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.