MySQL Binary Log Internals: Decoding QUERY_EVENT for DDL and Transactions

MySQL

Replication Internals: Decoding the MySQL Binary Log - Part 6: QUERY_EVENT — DDL Statements and Transaction Boundaries

11 min read

•

about 2 months ago

Replication Internals: Decoding the MySQL Binary Log - Part 6: QUERY_EVENT — DDL Statements and Transaction Boundaries

In this sixth post of our series, we decode the QUERY_EVENT — the workhorse event that records DDL statements and transaction boundaries.

Introduction

The QUERY_EVENT (event type 2, 0x02) is one of the most versatile events in a binary log. It records:

DDL statements: CREATE, ALTER, DROP, TRUNCATE, etc.
Transaction boundaries: BEGIN statements that start a transaction
Statement-based DML: In statement-based replication mode, INSERT/UPDATE/DELETE

Even in row-based replication (the default since MySQL 5.7), DDL statements are always logged as QUERY_EVENTs because their effects can't be represented as row changes.

Event Locations

In our binary log, we have several QUERY_EVENTs:

Position   276: QUERY_EVENT (182 bytes) - CREATE TABLE
Position   537: QUERY_EVENT (83 bytes)  - BEGIN (for INSERT)
Position   847: QUERY_EVENT (92 bytes)  - BEGIN (for UPDATE)
Position  1189: QUERY_EVENT (83 bytes)  - BEGIN (for DELETE)

Let's decode both types: the DDL (CREATE TABLE) and the transaction boundary (BEGIN).

Common Header (19 bytes)

Let's start with the CREATE TABLE event at position 276:

2d103568 02 01000000 b6000000 ca010000 0000
│        │  │        │        │        │
│        │  │        │        │        └─→ Flags: 0x0000
│        │  │        │        └───────────→ Next Position: 458
│        │  │        └────────────────────→ Event Size: 182 bytes
│        │  └─────────────────────────────→ Server ID: 1
│        └────────────────────────────────→ Event Type: 2 (QUERY_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308013

Field	Bytes	Little-Endian	Value
Timestamp	`2d103568`	0x6835102d	1748308013 (2025-05-27 01:06:53)
Event Type	`02`	0x02	2 (QUERY_EVENT)
Server ID	`01000000`	0x00000001	1
Event Size	`b6000000`	0x000000b6	182 bytes
Next Position	`ca010000`	0x000001ca	458
Flags	`0000`	0x0000	No flags

The common header flags field is a bitmask. For QUERY_EVENTs, the most relevant flags are:

Flag	Value	Meaning
`LOG_EVENT_THREAD_SPECIFIC_F`	0x04	Query depends on a thread-specific value (e.g., uses TEMPORARY TABLE)
`LOG_EVENT_SUPPRESS_USE_F`	0x08	Don't prepend `USE database` before executing the query on the replica
`LOG_EVENT_ARTIFICIAL_F`	0x20	Artificially generated event — don't update the master log position
`LOG_EVENT_RELAY_LOG_F`	0x40	Event was created by the replica's IO thread (written to relay log)
`LOG_EVENT_IGNORABLE_F`	0x80	Event can be safely ignored by older replicas that don't understand it
`LOG_EVENT_NO_FILTER_F`	0x100	Event is not affected by replication filters
`LOG_EVENT_MTS_ISOLATE_F`	0x200	Forces isolation in the multi-threaded replica applier

Our CREATE TABLE event has flags 0x0000 — no special flags set. As we'll see later, the BEGIN events have 0x0008 (LOG_EVENT_SUPPRESS_USE_F) set.

Reading the Raw Bytes

$ xxd -s 276 -l 182 binlog.000024
00000114: 2d10 3568 0201 0000 00b6 0000 00ca 0100  -.5h............
00000124: 0000 000a 0000 0000 0000 000c 0000 3700  ..............7.
00000134: 0000 0000 0001 2000 a045 0000 0000 0603  ...... ..E......
00000144: 7374 6404 ff00 ff00 ff00 0c01 7072 6573  std.........pres
00000154: 656e 7461 7469 6f6e 0011 3600 0000 0000  entation..6.....
00000164: 0000 12ff 0013 0070 7265 7365 6e74 6174  .......presentat
00000174: 696f 6e00 4352 4541 5445 2054 4142 4c45  ion.CREATE TABLE
00000184: 2070 6572 736f 6e20 280a 2020 4944 2049   person (.  ID I
00000194: 4e54 2050 5249 4d41 5259 204b 4559 2c0a  NT PRIMARY KEY,.
000001a4: 2020 6e61 6d65 2056 4152 4348 4152 2831    name VARCHAR(1
000001b4: 3530 2920 4445 4641 554c 5420 4e55 4c4c  50) DEFAULT NULL
000001c4: 0a29 0536 a92b                           .).6.+

The event is 182 bytes: 19-byte common header + 13-byte post-header + 55-byte status variables + 13-byte database name + 78-byte query + 4-byte checksum.

QUERY_EVENT Structure

The QUERY_EVENT has a 13-byte post-header (QUERY_HEADER_LEN) followed by variable-length data:

Field	Size	Description
Post-Header (13 bytes)
Thread ID	4 bytes	ID of the thread that executed the query
Execution Time	4 bytes	Time in seconds the query took to execute
Database Length	1 byte	Length of the default database name (max 255)
Error Code	2 bytes	MySQL error code (0 for success)
Status Vars Length	2 bytes	Length of the status variables block
Data Body
Status Variables	Variable	Session settings (SQL mode, character set, etc.) — Type-Value encoded
Database Name	Variable	Null-terminated default database (db_len + 1 bytes)
Query	Variable	The SQL statement (not null-terminated)

The query length is computed from the remaining bytes: event_size - 19 - 13 - status_vars_len - db_len - 1 - 4(checksum).

Post Header: Field-by-Field Decoding

0a000000 00000000 0c 0000 3700
│        │        │  │    │
│        │        │  │    └─→ Status Vars Length: 55 bytes
│        │        │  └───────→ Error Code: 0 (success)
│        │        └──────────→ Database Length: 12 ("presentation")
│        └───────────────────→ Execution Time: 0 seconds
└────────────────────────────→ Thread ID: 10

Field	Bytes	Value
Thread ID	`0a000000`	10
Execution Time	`00000000`	0 seconds
Database Length	`0c`	12 bytes
Error Code	`0000`	0 (success)
Status Vars Length	`3700`	55 bytes

The Thread ID identifies the connection that ran the query. The Execution Time records how long the query took to execute on the source — replicas can use this to estimate replication lag. The Error Code is non-zero when a query that produces an error is intentionally replicated (e.g., DROP TABLE IF NOT EXISTS on a table that doesn't exist produces error 1051, but is still logged).

Status Variables (55 bytes)

Status variables carry session state that the replica needs to replay the query correctly. They are encoded as Type-Value pairs: a 1-byte type code followed by a value whose size depends on the type. The type codes are defined in Query_event_status_vars.

Note: Unlike TLV (Type-Length-Value) encoding from Part 1, status variables do not carry a length field. The size of each type code's value is hardcoded in the deserialization switch statement. This means a parser that encounters an unknown type code cannot skip over it — it has no way to determine how many bytes the value occupies. The MySQL parser handles this by stopping the status variable parse at that point.

Here are the status variables in our CREATE TABLE event:

00 00000000 01 2000a04500000000 06 03 737464 04 ff00ff00ff00
0c 01 70726573656e746174696f6e 00 11 3600000000000000 12 ff00 13 00

Offset	Type	Name	Raw Bytes	Decoded Value
0	0x00	`Q_FLAGS2_CODE`	`00 00000000`	`0x00000000` — no session flags set
5	0x01	`Q_SQL_MODE_CODE`	`01 2000a04500000000`	`0x0000000045a00020`
14	0x06	`Q_CATALOG_NZ_CODE`	`06 03 737464`	length=3, "std"
19	0x04	`Q_CHARSET_CODE`	`04 ff00 ff00 ff00`	client=255, connection=255, server=255
26	0x0c	`Q_UPDATED_DB_NAMES`	`0c 01 707265...00`	count=1, ["presentation"]
41	0x11	`Q_DDL_LOGGED_WITH_XID`	`11 3600000000000000`	xid=54
50	0x12	`Q_DEFAULT_COLLATION_FOR_UTF8MB4`	`12 ff00`	collation=255 (utf8mb4_0900_ai_ci)
53	0x13	`Q_SQL_REQUIRE_PRIMARY_KEY`	`13 00`	0 (not required)

Let's look at the most important ones in detail.

Q_FLAGS2_CODE (0x00) — Session Flags

4-byte bitmask of session options from OPTIONS_WRITTEN_TO_BIN_LOG:

Bit	Flag	Meaning
14	`OPTION_AUTO_IS_NULL`	`@@sql_auto_is_null` behavior — `SELECT * WHERE id IS NULL` returns the last inserted auto-inc row
19	`OPTION_NOT_AUTOCOMMIT`	`@@autocommit = 0` — transactions are not auto-committed
26	`OPTION_NO_FOREIGN_KEY_CHECKS`	`@@foreign_key_checks = 0` — foreign key constraints are not enforced
27	`OPTION_RELAXED_UNIQUE_CHECKS`	`@@unique_checks = 0` — unique index constraints are not enforced

In our event, flags2 = 0x00000000 — all defaults (autocommit on, FK checks on, unique checks on).

Q_SQL_MODE_CODE (0x01) — SQL Mode

8-byte bitmask representing @@sql_mode. The value 0x0000000045a00020 decodes to these active flags:

Bit	Flag
5	`MODE_ONLY_FULL_GROUP_BY`
21	`MODE_STRICT_TRANS_TABLES`
23	`MODE_NO_ZERO_IN_DATE`
24	`MODE_NO_ZERO_DATE`
26	`MODE_ERROR_FOR_DIVISION_BY_ZERO`
30	`MODE_NO_ENGINE_SUBSTITUTION`

Q_CATALOG_NZ_CODE (0x06) — Catalog Name

1-byte length prefix followed by the catalog string (no null terminator). Currently, MySQL only has one catalog: "std". This field exists for forward compatibility.

Q_CHARSET_CODE (0x04) — Character Sets

6 bytes encoding three 2-byte little-endian collation IDs:

Bytes	Field	Value	Meaning
`ff00`	`character_set_client`	255	utf8mb4_0900_ai_ci
`ff00`	`collation_connection`	255	utf8mb4_0900_ai_ci
`ff00`	`collation_server`	255	utf8mb4_0900_ai_ci

These ensure the replica uses the same character set settings when executing the query, so that string comparisons and conversions produce identical results.

Q_DDL_LOGGED_WITH_XID (0x11) — DDL Transaction ID

8-byte XID value. This is the internal transaction ID assigned to the DDL statement, making DDL crash-safe. In our event, xid=54. This field is only present for DDL statements (CREATE, ALTER, DROP, etc.), not for BEGIN queries.

Complete Status Variable Reference

For completeness, here is the full list of all status variable types defined in Query_event_status_vars:

Code	Name	Size	Description
0	`Q_FLAGS2_CODE`	4 bytes	Session flags bitmask
1	`Q_SQL_MODE_CODE`	8 bytes	SQL mode bitmask
2	`Q_CATALOG_CODE`	1 + N + 1 bytes	Legacy catalog (with null terminator, MySQL 5.0.0–5.0.3 only)
3	`Q_AUTO_INCREMENT`	4 bytes	Two uint16: `auto_increment_increment` and `auto_increment_offset`
4	`Q_CHARSET_CODE`	6 bytes	Three uint16: client charset, connection collation, server collation
5	`Q_TIME_ZONE_CODE`	1 + N bytes	Time zone name (length-prefixed string)
6	`Q_CATALOG_NZ_CODE`	1 + N bytes	Catalog name (length-prefixed, no null terminator)
7	`Q_LC_TIME_NAMES_CODE`	2 bytes	Locale number for `lc_time_names`
8	`Q_CHARSET_DATABASE_CODE`	2 bytes	Database default collation
9	`Q_TABLE_MAP_FOR_UPDATE_CODE`	8 bytes	64-bit table map for multi-table UPDATE
10	`Q_MASTER_DATA_WRITTEN_CODE`	4 bytes	Placeholder — unused after MySQL 8.0.2
11	`Q_INVOKER`	1 + N + 1 + N bytes	DEFINER's user and host (for stored routines, views, triggers)
12	`Q_UPDATED_DB_NAMES`	1 + variable	Count of accessed databases + null-terminated names (for parallel replication)
13	`Q_MICROSECONDS`	3 bytes	Microsecond component of the query timestamp
14	`Q_COMMIT_TS`	—	Obsolete (unused)
15	`Q_COMMIT_TS2`	—	Obsolete (unused)
16	`Q_EXPLICIT_DEFAULTS_FOR_TIMESTAMP`	1 byte	`@@explicit_defaults_for_timestamp` value
17	`Q_DDL_LOGGED_WITH_XID`	8 bytes	Internal XID for crash-safe DDL
18	`Q_DEFAULT_COLLATION_FOR_UTF8MB4`	2 bytes	Default utf8mb4 collation (for cross-version replication)
19	`Q_SQL_REQUIRE_PRIMARY_KEY`	1 byte	`@@sql_require_primary_key` value
20	`Q_DEFAULT_TABLE_ENCRYPTION`	1 byte	`@@default_table_encryption` value

Database Name and Query

After the status variables, we have the database name (null-terminated):

70 72 65 73 65 6e 74 61 74 69 6f 6e 00
p  r  e  s  e  n  t  a  t  i  o  n  \0

Database: "presentation" (12 bytes + 1 null terminator)

The rest of the payload (before the 4-byte checksum) is the SQL statement (78 bytes, not null-terminated):

CREATE TABLE person (
  ID INT PRIMARY KEY,
  name VARCHAR(150) DEFAULT NULL
)

The BEGIN Event (Position 537)

Transaction boundaries for row-based events are marked with a QUERY_EVENT containing "BEGIN":

$ xxd -s 537 -l 83 binlog.000024
00000219: 3210 3568 0201 0000 0053 0000 006c 0200  2.5h.....S...l..
00000229: 0008 000a 0000 0000 0000 000c 0000 1d00  ................
00000239: 0000 0000 0001 2000 a045 0000 0000 0603  ...... ..E......
00000249: 7374 6404 ff00 ff00 ff00 12ff 0070 7265  std..........pre
00000259: 7365 6e74 6174 696f 6e00 4245 4749 4e95  sentation.BEGIN.
00000269: c51d cf                                  ...

Common Header

32103568 02 01000000 53000000 6c020000 0800
│        │  │        │        │        │
│        │  │        │        │        └─→ Flags: 0x0008 (LOG_EVENT_SUPPRESS_USE_F)
│        │  │        │        └───────────→ Next Position: 620
│        │  │        └────────────────────→ Event Size: 83 bytes
│        │  └─────────────────────────────→ Server ID: 1
│        └────────────────────────────────→ Event Type: 2 (QUERY_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308018

Note the flags field is 0x0008 — this is LOG_EVENT_SUPPRESS_USE_F, which tells the replica not to execute a USE database statement before this query. This makes sense — BEGIN doesn't depend on the current database.

Post-Header

0a000000 00000000 0c 0000 1d00
│        │        │  │    │
│        │        │  │    └─→ Status Vars Length: 29 bytes
│        │        │  └───────→ Error Code: 0
│        │        └──────────→ Database Length: 12
│        └───────────────────→ Execution Time: 0
└────────────────────────────→ Thread ID: 10

The BEGIN event has only 29 bytes of status variables (vs. 55 for CREATE TABLE) — it doesn't include Q_UPDATED_DB_NAMES, Q_DDL_LOGGED_WITH_XID, or Q_SQL_REQUIRE_PRIMARY_KEY since those are DDL-specific.

Status Variables (29 bytes)

Type	Name	Value
0x00	`Q_FLAGS2_CODE`	`0x00000000`
0x01	`Q_SQL_MODE_CODE`	`0x0000000045a00020`
0x06	`Q_CATALOG_NZ_CODE`	"std"
0x04	`Q_CHARSET_CODE`	client=255, conn=255, server=255
0x12	`Q_DEFAULT_COLLATION_FOR_UTF8MB4`	255

Query

BEGIN

Comparing the BEGIN Events

Our binary log has three BEGIN events. Let's compare them:

Position	Size	Flags	Status Vars Len	Extra Status Variables
537	83	0x0008	29	—
847	92	0x0008	38	`Q_TABLE_MAP_FOR_UPDATE_CODE` = `0x0000000000000001`
1189	83	0x0008	29	—

The UPDATE transaction's BEGIN event (position 847) is 9 bytes larger because it includes Q_TABLE_MAP_FOR_UPDATE_CODE (1 byte type + 8 bytes value). This 64-bit bitmask identifies which tables will be modified by the transaction — the replica's multi-threaded applier uses this to determine if transactions can be applied in parallel.

Visual Breakdown: CREATE TABLE Event

Position 276: QUERY_EVENT - CREATE TABLE (182 bytes)

┌─────────────────────────────────────────────────────────────────────────┐
│                         COMMON HEADER (19 bytes)                        │
├─────────────────────────────────────────────────────────────────────────┤
│ 2d103568     │ 02   │ 01000000 │ b6000000 │ ca010000 │ 0000             │
│ Timestamp    │ Type │ ServerID │ Size     │ NextPos  │ Flags            │
│ 1748308013   │ 2    │ 1        │ 182      │ 458      │ 0x0000           │
└─────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                         POST-HEADER (13 bytes)                          │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 0a000000                     │ Thread ID: 10                            │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 00000000                     │ Execution Time: 0 seconds                │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0c                           │ Database Length: 12                      │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0000                         │ Error Code: 0 (success)                  │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 3700                         │ Status Vars Length: 55                   │
└──────────────────────────────┴──────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                       STATUS VARIABLES (55 bytes)                       │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 00 00000000                  │ Q_FLAGS2_CODE: 0x00000000                │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 01 2000a04500000000          │ Q_SQL_MODE_CODE: 0x0000000045a00020      │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 06 03 737464                 │ Q_CATALOG_NZ_CODE: "std"                 │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 04 ff00ff00ff00               │ Q_CHARSET_CODE: 255/255/255             │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0c 01 707265...00            │ Q_UPDATED_DB_NAMES: ["presentation"]     │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 11 3600000000000000          │ Q_DDL_LOGGED_WITH_XID: xid=54            │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 12 ff00                      │ Q_DEFAULT_COLLATION_FOR_UTF8MB4: 255     │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 13 00                        │ Q_SQL_REQUIRE_PRIMARY_KEY: 0             │
└──────────────────────────────┴──────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                       DATABASE + QUERY                                  │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 70726573656e746174696f6e00   │ Database: "presentation\0" (13 bytes)    │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 435245415445205441424c4520...│ Query (78 bytes):                        │
│                              │ CREATE TABLE person (                    │
│                              │   ID INT PRIMARY KEY,                    │
│                              │   name VARCHAR(150) DEFAULT NULL         │
│                              │ )                                        │
└──────────────────────────────┴──────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                           CHECKSUM (4 bytes)                            │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 0536a92b                     │ CRC32                                    │
└──────────────────────────────┴──────────────────────────────────────────┘

Transaction Flow

Here's how a typical DML transaction looks in the binary log:

GTID_LOG_EVENT          ← Transaction identifier (GNO=13)
QUERY_EVENT (BEGIN)     ← Transaction start
TABLE_MAP_EVENT         ← Table metadata
WRITE_ROWS_EVENT        ← The actual row data
XID_EVENT               ← Transaction commit

For DDL statements:

GTID_LOG_EVENT          ← Transaction identifier (GNO=12)
QUERY_EVENT (DDL)       ← The DDL statement itself (implicitly committed)

Note that DDL statements don't have explicit BEGIN/COMMIT — they are implicitly committed. The Q_DDL_LOGGED_WITH_XID status variable ensures crash-safety for the DDL operation.

Try It Yourself

import struct

STATUS_VAR_NAMES = {
    0x00: ('Q_FLAGS2_CODE', 4),
    0x01: ('Q_SQL_MODE_CODE', 8),
    0x03: ('Q_AUTO_INCREMENT', 4),
    0x04: ('Q_CHARSET_CODE', 6),
    0x06: ('Q_CATALOG_NZ_CODE', None),  # variable
    0x07: ('Q_LC_TIME_NAMES_CODE', 2),
    0x08: ('Q_CHARSET_DATABASE_CODE', 2),
    0x09: ('Q_TABLE_MAP_FOR_UPDATE_CODE', 8),
    0x0c: ('Q_UPDATED_DB_NAMES', None),  # variable
    0x11: ('Q_DDL_LOGGED_WITH_XID', 8),
    0x12: ('Q_DEFAULT_COLLATION_FOR_UTF8MB4', 2),
    0x13: ('Q_SQL_REQUIRE_PRIMARY_KEY', 1),
    0x14: ('Q_DEFAULT_TABLE_ENCRYPTION', 1),
}

def decode_status_vars(data):
    """Decode QUERY_EVENT status variables (Type-Value pairs)."""
    off = 0
    while off < len(data):
        code = data[off]
        off += 1
        info = STATUS_VAR_NAMES.get(code)
        name = info[0] if info else f'UNKNOWN(0x{code:02x})'

        if code == 0x00:
            val = struct.unpack('<I', data[off:off+4])[0]
            print(f"  0x{code:02x} {name}: 0x{val:08x}")
            off += 4
        elif code == 0x01:
            val = struct.unpack('<Q', data[off:off+8])[0]
            print(f"  0x{code:02x} {name}: 0x{val:016x}")
            off += 8
        elif code == 0x04:
            c1, c2, c3 = struct.unpack('<HHH', data[off:off+6])
            print(f"  0x{code:02x} {name}: client={c1}, conn={c2}, server={c3}")
            off += 6
        elif code == 0x06:
            slen = data[off]; off += 1
            s = data[off:off+slen].decode('utf-8'); off += slen
            print(f"  0x{code:02x} {name}: \"{s}\"")
        elif code == 0x09:
            val = struct.unpack('<Q', data[off:off+8])[0]
            print(f"  0x{code:02x} {name}: 0x{val:016x}")
            off += 8
        elif code == 0x0c:
            count = data[off]; off += 1
            dbs = []
            for _ in range(count):
                end = data.index(0x00, off)
                dbs.append(data[off:end].decode('utf-8')); off = end + 1
            print(f"  0x{code:02x} {name}: {dbs}")
        elif code == 0x11:
            val = struct.unpack('<Q', data[off:off+8])[0]
            print(f"  0x{code:02x} {name}: xid={val}")
            off += 8
        elif code == 0x12:
            val = struct.unpack('<H', data[off:off+2])[0]
            print(f"  0x{code:02x} {name}: {val}")
            off += 2
        elif code == 0x13 or code == 0x14 or code == 0x10:
            val = data[off]; off += 1
            print(f"  0x{code:02x} {name}: {val}")
        else:
            print(f"  0x{code:02x} {name}: (unknown encoding, stopping)")
            break

with open('binlog.000024', 'rb') as f:
    positions = [276, 537, 847, 1189]
    labels = ["CREATE TABLE", "BEGIN (INSERT)", "BEGIN (UPDATE)", "BEGIN (DELETE)"]

    for pos, label in zip(positions, labels):
        f.seek(pos)
        header = f.read(19)
        timestamp, event_type, server_id, event_size, next_pos, flags = \
            struct.unpack('<IBIIIH', header)

        post_header = f.read(13)
        thread_id, exec_time, db_len, error_code, status_len = \
            struct.unpack('<IIBHH', post_header)

        print(f"Position {pos}: QUERY_EVENT - {label}")
        print(f"  Event Size: {event_size} bytes, Flags: 0x{flags:04x}")
        print(f"  Thread ID: {thread_id}, Exec Time: {exec_time}s, "
              f"Error Code: {error_code}")
        print(f"  Status Variables ({status_len} bytes):")

        status_vars = f.read(status_len)
        decode_status_vars(status_vars)

        db_name = f.read(db_len).decode('utf-8')
        f.read(1)  # null terminator

        query_len = event_size - 19 - 13 - status_len - db_len - 1 - 4
        query = f.read(query_len).decode('utf-8')

        print(f"  Database: \"{db_name}\"")
        print(f"  Query: \"{query}\"")
        print()

Output:

Position 276: QUERY_EVENT - CREATE TABLE
  Event Size: 182 bytes, Flags: 0x0000
  Thread ID: 10, Exec Time: 0s, Error Code: 0
  Status Variables (55 bytes):
  0x00 Q_FLAGS2_CODE: 0x00000000
  0x01 Q_SQL_MODE_CODE: 0x0000000045a00020
  0x06 Q_CATALOG_NZ_CODE: "std"
  0x04 Q_CHARSET_CODE: client=255, conn=255, server=255
  0x0c Q_UPDATED_DB_NAMES: ['presentation']
  0x11 Q_DDL_LOGGED_WITH_XID: xid=54
  0x12 Q_DEFAULT_COLLATION_FOR_UTF8MB4: 255
  0x13 Q_SQL_REQUIRE_PRIMARY_KEY: 0
  Database: "presentation"
  Query: "CREATE TABLE person (
  ID INT PRIMARY KEY,
  name VARCHAR(150) DEFAULT NULL
)"

Position 537: QUERY_EVENT - BEGIN (INSERT)
  Event Size: 83 bytes, Flags: 0x0008
  Thread ID: 10, Exec Time: 0s, Error Code: 0
  Status Variables (29 bytes):
  0x00 Q_FLAGS2_CODE: 0x00000000
  0x01 Q_SQL_MODE_CODE: 0x0000000045a00020
  0x06 Q_CATALOG_NZ_CODE: "std"
  0x04 Q_CHARSET_CODE: client=255, conn=255, server=255
  0x12 Q_DEFAULT_COLLATION_FOR_UTF8MB4: 255
  Database: "presentation"
  Query: "BEGIN"

Position 847: QUERY_EVENT - BEGIN (UPDATE)
  Event Size: 92 bytes, Flags: 0x0008
  Thread ID: 10, Exec Time: 0s, Error Code: 0
  Status Variables (38 bytes):
  0x00 Q_FLAGS2_CODE: 0x00000000
  0x01 Q_SQL_MODE_CODE: 0x0000000045a00020
  0x06 Q_CATALOG_NZ_CODE: "std"
  0x04 Q_CHARSET_CODE: client=255, conn=255, server=255
  0x09 Q_TABLE_MAP_FOR_UPDATE_CODE: 0x0000000000000001
  0x12 Q_DEFAULT_COLLATION_FOR_UTF8MB4: 255
  Database: "presentation"
  Query: "BEGIN"

Position 1189: QUERY_EVENT - BEGIN (DELETE)
  Event Size: 83 bytes, Flags: 0x0008
  Thread ID: 10, Exec Time: 0s, Error Code: 0
  Status Variables (29 bytes):
  0x00 Q_FLAGS2_CODE: 0x00000000
  0x01 Q_SQL_MODE_CODE: 0x0000000045a00020
  0x06 Q_CATALOG_NZ_CODE: "std"
  0x04 Q_CHARSET_CODE: client=255, conn=255, server=255
  0x12 Q_DEFAULT_COLLATION_FOR_UTF8MB4: 255
  Database: "presentation"
  Query: "BEGIN"

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

Query_event_status_vars enum — All status variable type codes
Query_event class definition — Post-header offsets and member variables
Query_event deserialization — How the event is parsed from a buffer
Query_log_event::write() — How the event is serialized to the binary log
SQL mode flags — MODE_* bitmask values
OPTIONS_WRITTEN_TO_BIN_LOG — Flags written to Q_FLAGS2_CODE
Event header flags — LOG_EVENT_*_F definitions

What's Next?

Now that we understand how DDL statements and transaction boundaries are recorded, we're ready to look at row-based events. In the next post, we'll decode the TABLE_MAP_EVENT — the event that describes the structure of tables involved in row changes.

Next up: Part 7: TABLE_MAP_EVENT — Table Metadata for Row-Based Replication

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.

Authors

Marcelo Altmann