Decoding GTID_LOG_EVENT | Replication Internals Part 5

MySQL

Replication Internals: Decoding the MySQL Binary Log Part 5: GTID_LOG_EVENT — The Global Transaction Identifier

10 min read

•

2 months ago

Replication Internals: Decoding the MySQL Binary Log Part 5: GTID_LOG_EVENT — The Global Transaction Identifier

In this fifth post of our series, we decode the GTID_LOG_EVENT — the event that marks every transaction with a globally unique identifier.

Introduction

Every transaction in a GTID-enabled MySQL server begins with a GTID_LOG_EVENT (event type 33, 0x21). This event assigns a globally unique identifier to the transaction, consisting of:

SID (Source ID): The UUID of the server that originally committed the transaction
GNO (Group Number): A monotonically increasing sequence number

Together, they form a GTID like b8ae2fd2-3005-11f0-8be8-0242ac150002:12.

GTIDs simplify replication by removing the need to track binary log file names and positions. With GTIDs, you can:

Set up new replicas without calculating binary log positions
Failover to a new source without manual positioning
Detect and prevent duplicate transaction execution

Note: MySQL 8.4 introduced the GTID_TAGGED_LOG_EVENT (event type 42, 0x2a), which uses a different serialization format and supports GTID tags. We'll cover that in a dedicated blog post. This post focuses on the classic untagged GTID_LOG_EVENT format.

Event Location

In our binary log, we have multiple GTID_LOG_EVENTs — one for each transaction:

Position   197: GTID_LOG_EVENT (79 bytes) ← First transaction (CREATE TABLE)
Position   458: GTID_LOG_EVENT (79 bytes) ← Second transaction (INSERT)
Position   768: GTID_LOG_EVENT (79 bytes) ← Third transaction (UPDATE)
Position  1110: GTID_LOG_EVENT (79 bytes) ← Fourth transaction (DELETE)

Let's decode the first one at position 197.

Common Header (19 bytes)

2d103568 21 01000000 4f000000 14010000 0000
│        │  │        │        │        │
│        │  │        │        │        └─→ Flags: 0x0000
│        │  │        │        └───────────→ Next Position: 276
│        │  │        └────────────────────→ Event Size: 79 bytes
│        │  └─────────────────────────────→ Server ID: 1
│        └────────────────────────────────→ Event Type: 33 (GTID_LOG_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748308013

Field	Bytes	Little-Endian	Value
Timestamp	`2d103568`	0x6835102d	1748308013 (2025-05-27 01:06:53)
Event Type	`21`	0x21	33 (GTID_LOG_EVENT)
Server ID	`01000000`	0x00000001	1
Event Size	`4f000000`	0x0000004f	79 bytes
Next Position	`14010000`	0x00000114	276
Flags	`0000`	0x0000	No flags

Reading the Raw Bytes

$ xxd -s 197 -l 79 binlog.000024
000000c5: 2d10 3568 2101 0000 004f 0000 0014 0100  -.5h!....O......
000000d5: 0000 0001 b8ae 2fd2 3005 11f0 8be8 0242  ....../.0......B
000000e5: ac15 0002 0c00 0000 0000 0000 0200 0000  ................
000000f5: 0000 0000 0001 0000 0000 0000 00c6 551b  ..............U.
00000105: ae13 3606 fc05 01a8 3801 0068 d627 61    ..6.....8..h.'a

The event is 79 bytes: 19-byte common header + 42-byte post-header + 14-byte data body + 4-byte checksum.

GTID_LOG_EVENT Payload Structure

The GTID_LOG_EVENT payload consists of two parts: a fixed-size 42-byte post-header and a variable-length data body containing additional metadata fields.

The post-header fields are written by Gtid_log_event::write_post_header_to_memory():

Field	Size	Description
GTID Flags	1 byte	Bit flags — bit 0 (`FLAG_MAY_HAVE_SBR`): indicates the transaction may contain statement-based replication events
SID (UUID)	16 bytes	The server UUID portion of the Transaction Source Identifier (TSID)
GNO	8 bytes	Group Number (transaction sequence number within this SID)
Logical Clock Typecode	1 byte	Always `0x02` — indicates logical clock timestamps follow
Last Committed	8 bytes	Sequence number of the commit parent (for parallel replication)
Sequence Number	8 bytes	This transaction's logical clock sequence number

The data body fields are written by Gtid_log_event::write_body_to_memory():

Field	Size	Description
Immediate Commit Timestamp	7 bytes	Microsecond timestamp on the immediate server; MSB (Most Significant Bit, bit 55) flags whether Original Commit Timestamp follows
Original Commit Timestamp	7 bytes	Conditional — only present if MSB of Immediate Commit Timestamp is set (i.e., transaction was replicated from a different server)
Transaction Length	1–9 bytes	Packed integer — total transaction size in bytes, including this GTID event
Immediate Server Version	4 bytes	MySQL version of the immediate server (e.g., 80040 = 8.0.40); MSB (bit 31) flags whether Original Server Version follows
Original Server Version	4 bytes	Conditional — only present if MSB of Immediate Server Version is set
Commit Group Ticket	8 bytes	Conditional — only present when Binlog Group Commit ticketing is active (value != 0)

Let's decode the full payload (56 bytes, excluding checksum):

01 b8ae2fd2300511f08be80242ac150002 0c00000000000000 02 0000000000000000 0100000000000000 c6551bae133606 fc0501 a8380100

Post Header: Field-by-Field Decoding

GTID Flags: `01`

01 → 0x01 = FLAG_MAY_HAVE_SBR is set

This is a bit field stored in gtid_flags. Bit 0 is FLAG_MAY_HAVE_SBR — when set, it indicates the transaction may contain changes logged with statement-based replication (SBR). When cleared (0), the transaction contains only row-based events, which allows the replica applier to optimize its isolation level.

In MySQL 5.6, 5.7.0–5.7.18, and 8.0.0–8.0.1, this flag is always set. Starting in 5.7.19 and 8.0.2, it is cleared if the transaction only contains row events.

SID (UUID): 16 bytes

b8ae2fd2 300511f0 8be80242 ac150002

The UUID is stored as a raw 16-byte sequence (not little-endian — UUIDs use network byte order). Converting to standard UUID format: b8ae2fd2-3005-11f0-8be8-0242ac150002

This is the server_uuid of the MySQL instance where this transaction was first committed.

GNO: `0c00000000000000`

0c00000000000000 → little-endian int64 → 0x000000000000000c = 12

The Group Number is 12. Combined with the UUID, the complete GTID is:

b8ae2fd2-3005-11f0-8be8-0242ac150002:12

Logical Clock Typecode: `02`

02 → Type 2 indicates logical clock timestamps are present

This typecode was introduced with the parallel replication logical clock in MySQL 5.7. Its value is always 2 (LOGICAL_TIMESTAMP_TYPECODE). If this byte is not present (in older binary logs), the last_committed and sequence_number fields are absent.

Last Committed: `0000000000000000`

0000000000000000 → little-endian int64 → 0

The last_committed value identifies the commit parent — the most recently committed transaction that this one depends on. A value of 0 means this is the first transaction in the binary log or has no dependencies.

Sequence Number: `0100000000000000`

0100000000000000 → little-endian int64 → 1

This transaction's sequence number is 1. Together with last_committed, these fields drive parallel replication on the replica — transactions with the same last_committed can be applied in parallel.

Data Body: Transaction Metadata

The remaining 14 bytes after the 42-byte post-header contain transaction metadata. Let's decode them field by field:

Offset:  0  1  2  3  4  5  6  7  8  9  10 11 12 13
Bytes:  c6 55 1b ae 13 36 06 fc 05 01 a8 38 01 00
        └──────────────────┘ └───────┘ └─────────┘
        immediate_commit_ts  trx_len   imm_server_ver
        (7 bytes, int7store) (packed)  (4 bytes, int4store)

Immediate Commit Timestamp (7 bytes): `c6 55 1b ae 13 36 06`

The timestamp is stored as a 7-byte little-endian integer using int7store(), representing microseconds since the Unix epoch:

c6 55 1b ae 13 36 06 → 0x063613ae1b55c6 = 1748308013569478 μs

The MSB (bit 55) serves as a flag — see ENCODED_COMMIT_TIMESTAMP_LENGTH:

0: The transaction originated on this server — original_commit_timestamp equals immediate_commit_timestamp and is not written
1: The transaction was replicated — the next 7 bytes contain the original_commit_timestamp

In our case, byte 6 is 0x06 = 0000 0110 in binary — bit 55 (the highest bit of the 7-byte value) is 0. This means the transaction originated locally, and no original_commit_timestamp follows.

Converting the timestamp: 1748308013569478 μs ÷ 1,000,000 = 1748308013.569478 seconds → 2025-05-27 01:06:53.569478 UTC — consistent with the header timestamp.

Transaction Length (packed integer): `fc 05 01`

This field uses MySQL's packed integer encoding (net_store_length()):

fc → 0xFC (252): signals that the next 2 bytes contain a 16-bit little-endian integer
05 01 → little-endian uint16 → 0x0105 = 261

The total transaction length is 261 bytes — this includes the GTID_LOG_EVENT itself plus all subsequent events up to and including the transaction's terminal event (XID_EVENT or QUERY_EVENT with COMMIT/ROLLBACK).

We can verify: from position 197 (this GTID event) through 261 bytes lands at position 458 — which is exactly where the next GTID_LOG_EVENT begins. ✓

As a reminder from Part 1, the packed integer encoding works as follows:

First Byte	Meaning
`0x00`–`0xFA` (0–250)	The value itself (1 byte total)
`0xFC` (252)	Next 2 bytes as a 16-bit integer
`0xFD` (253)	Next 3 bytes as a 24-bit integer
`0xFE` (254)	Next 8 bytes as a 64-bit integer

Immediate Server Version (4 bytes): `a8 38 01 00`

Stored as a 4-byte little-endian integer using int4store():

a8 38 01 00 → little-endian uint32 → 0x000138a8 = 80040

The value 80040 encodes MySQL version 8.0.40 (major × 10000 + minor × 100 + patch).

The MSB (bit 31) serves as a flag, similar to the commit timestamp — see ENCODED_SERVER_VERSION_LENGTH:

0: original_server_version equals immediate_server_version and is not written
1: The next 4 bytes contain the original_server_version

In our case, 0x000138a8 has bit 31 clear — both versions are the same. No original_server_version follows.

Commit Group Ticket (not present)

If the Binlog Group Commit (BGC) ticketing mechanism is active, an additional 8-byte commit_group_ticket would follow. It is only written when the value differs from the default kGroupTicketUnset (0). In our event, there are no remaining bytes — the ticket is not present.

Visual Breakdown

Position 197: GTID_LOG_EVENT (79 bytes)

┌─────────────────────────────────────────────────────────────────────────┐
│                         COMMON HEADER (19 bytes)                        │
├─────────────────────────────────────────────────────────────────────────┤
│ 2d103568     │ 21   │ 01000000 │ 4f000000 │ 14010000 │ 0000             │
│ Timestamp    │ Type │ ServerID │ Size     │ NextPos  │ Flags            │
│ 1748308013   │ 33   │ 1        │ 79       │ 276      │ 0x0000           │
└─────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                         POST-HEADER (42 bytes)                          │
├──────────────────────────────┬──────────────────────────────────────────┤
│ 01                           │ GTID Flags: 0x01 (FLAG_MAY_HAVE_SBR)     │
├──────────────────────────────┼──────────────────────────────────────────┤
│ b8ae2fd2300511f0             │ SID: b8ae2fd2-3005-11f0-8be8-            │
│ 8be80242ac150002             │      0242ac150002                        │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0c00000000000000             │ GNO: 12                                  │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 02                           │ Logical Clock Typecode: 2                │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0000000000000000             │ Last Committed: 0                        │
├──────────────────────────────┼──────────────────────────────────────────┤
│ 0100000000000000             │ Sequence Number: 1                       │
└──────────────────────────────┴──────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                         DATA BODY (14 bytes)                            │
├──────────────────────────────┬──────────────────────────────────────────┤
│ c6551bae133606               │ Immediate Commit Timestamp (7 bytes)     │
│                              │ 1748308013569478 μs (bit 55 = 0:         │
│                              │ originated locally, no original ts)      │
├──────────────────────────────┼──────────────────────────────────────────┤
│ fc0501                       │ Transaction Length (packed integer)      │
│                              │ 0xFC + 2 bytes LE → 261 bytes            │
├──────────────────────────────┼──────────────────────────────────────────┤
│ a8380100                     │ Immediate Server Version (4 bytes)       │
│                              │ 80040 → MySQL 8.0.40 (bit 31 = 0:        │
│                              │ same as original, no original version)   │
└──────────────────────────────┴──────────────────────────────────────────┘

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

GTID: b8ae2fd2-3005-11f0-8be8-0242ac150002:12

Parallel Replication: last_committed and sequence_number

The last_committed and sequence_number fields enable MySQL's parallel replication feature (introduced in MySQL 5.7). Here's how they work:

sequence_number: A unique, monotonically increasing number assigned to each transaction
last_committed: The sequence number of the most recent transaction that this transaction depends on

Transactions can be applied in parallel on a replica if they share the same last_committed value — meaning they don't depend on each other. For example:

Transaction A: last_committed=0, sequence_number=1
Transaction B: last_committed=1, sequence_number=2
Transaction C: last_committed=1, sequence_number=3
Transaction D: last_committed=3, sequence_number=4

In this example:

B and C can run in parallel (both depend only on A)
D must wait for C to complete

Comparing All GTIDs in Our Log

Let's look at all the GTID_LOG_EVENTs in our binary log:

Position	GNO	GTID Flags	Last Committed	Sequence	Transaction
197	12	0x01 (FLAG_MAY_HAVE_SBR)	0	1	CREATE TABLE person
458	13	0x00 (row-only)	1	2	INSERT INTO person VALUES (1, 'Marcelo')
768	14	0x00 (row-only)	2	3	UPDATE person SET name = 'Marcelo Altmann'
1110	15	0x00 (row-only)	3	4	DELETE FROM person WHERE ID = 1

Each transaction has a sequential GNO, all from the same server UUID. Notice that the CREATE TABLE has FLAG_MAY_HAVE_SBR set (0x01) because DDL statements are always logged as statements. The DML transactions (INSERT, UPDATE, DELETE) have the flag cleared (0x00) — they contain only row-based events.

Also notice that each transaction's last_committed equals the previous transaction's sequence_number — they form a serial chain and cannot be parallelized. This is expected for a single-threaded workload on one server.

Try It Yourself

import struct
import uuid

def read_packed_int(data, offset):
    """Read a MySQL packed integer (net_store_length encoding)."""
    first_byte = data[offset]
    if first_byte < 251:
        return first_byte, offset + 1
    elif first_byte == 0xFC:
        val = struct.unpack('<H', data[offset+1:offset+3])[0]
        return val, offset + 3
    elif first_byte == 0xFD:
        val = struct.unpack('<I', data[offset+1:offset+4] + b'\x00')[0]
        return val, offset + 4
    elif first_byte == 0xFE:
        val = struct.unpack('<Q', data[offset+1:offset+9])[0]
        return val, offset + 9

with open('binlog.000024', 'rb') as f:
    positions = [197, 458, 768, 1110]

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

        # Read post-header (42 bytes)
        post_header = f.read(42)

        gtid_flags = post_header[0]
        sid = uuid.UUID(bytes=post_header[1:17])
        gno = struct.unpack('<Q', post_header[17:25])[0]
        ts_type = post_header[25]
        last_committed = struct.unpack('<Q', post_header[26:34])[0]
        seq_number = struct.unpack('<Q', post_header[34:42])[0]

        # Read data body
        body_size = event_size - 19 - 42 - 4  # subtract header, post-header, checksum
        body = f.read(body_size)

        # Immediate commit timestamp (7 bytes, little-endian)
        imm_ts_raw = int.from_bytes(body[0:7], 'little')
        has_original_ts = (imm_ts_raw & (1 << 55)) != 0
        imm_ts = imm_ts_raw & ~(1 << 55)
        offset = 7

        if has_original_ts:
            orig_ts = int.from_bytes(body[offset:offset+7], 'little')
            offset += 7
        else:
            orig_ts = imm_ts

        # Transaction length (packed integer)
        trx_len, offset = read_packed_int(body, offset)

        # Immediate server version (4 bytes)
        imm_ver = struct.unpack('<I', body[offset:offset+4])[0]
        has_original_ver = (imm_ver & (1 << 31)) != 0
        imm_ver = imm_ver & ~(1 << 31)
        offset += 4

        if has_original_ver:
            orig_ver = struct.unpack('<I', body[offset:offset+4])[0]
            offset += 4
        else:
            orig_ver = imm_ver

        print(f"Position {pos}:")
        print(f"  GTID: {sid}:{gno}")
        print(f"  GTID Flags: 0x{gtid_flags:02x} "
              f"({'FLAG_MAY_HAVE_SBR' if gtid_flags & 1 else 'row-only'})")
        print(f"  Last Committed: {last_committed}, Sequence: {seq_number}")
        print(f"  Immediate Commit Timestamp: {imm_ts} μs"
              f" ({imm_ts / 1_000_000:.6f} epoch)")
        print(f"  Transaction Length: {trx_len} bytes")
        print(f"  Server Version: {imm_ver} "
              f"({imm_ver // 10000}.{(imm_ver % 10000) // 100}.{imm_ver % 100})")
        print()

Output:

Position 197:
  GTID: b8ae2fd2-3005-11f0-8be8-0242ac150002:12
  GTID Flags: 0x01 (FLAG_MAY_HAVE_SBR)
  Last Committed: 0, Sequence: 1
  Immediate Commit Timestamp: 1748308013569478 μs (1748308013.569478 epoch)
  Transaction Length: 261 bytes
  Server Version: 80040 (8.0.40)

Position 458:
  GTID: b8ae2fd2-3005-11f0-8be8-0242ac150002:13
  GTID Flags: 0x00 (row-only)
  Last Committed: 1, Sequence: 2
  Immediate Commit Timestamp: 1748308018964504 μs (1748308018.964504 epoch)
  Transaction Length: 310 bytes
  Server Version: 80040 (8.0.40)

Position 768:
  GTID: b8ae2fd2-3005-11f0-8be8-0242ac150002:14
  GTID Flags: 0x00 (row-only)
  Last Committed: 2, Sequence: 3
  Immediate Commit Timestamp: 1748308018968955 μs (1748308018.968955 epoch)
  Transaction Length: 342 bytes
  Server Version: 80040 (8.0.40)

Position 1110:
  GTID: b8ae2fd2-3005-11f0-8be8-0242ac150002:15
  GTID Flags: 0x00 (row-only)
  Last Committed: 3, Sequence: 4
  Immediate Commit Timestamp: 1748308018972353 μs (1748308018.972353 epoch)
  Transaction Length: 318 bytes
  Server Version: 80040 (8.0.40)

Notice that the first transaction (CREATE TABLE) has FLAG_MAY_HAVE_SBR set (0x01) while the remaining DML transactions show 0x00 (row-only). This is because DDL statements are always logged as statements, whereas our DML operations were logged using row-based replication.

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

Gtid_event class definition — Constants, field documentation, and the post-header layout
Gtid_event deserialization constructor — How the event is read from a binary log buffer
Gtid_log_event::write_post_header_to_memory() — Post-header serialization (flags, UUID, GNO, logical clock)
Gtid_log_event::write_body_to_memory() — Data body serialization (timestamps, transaction length, server versions)
net_store_length() — Packed integer encoding implementation

What's Next?

Now that we understand how transactions are identified with GTIDs, we're ready to look at the actual transaction content. In the next post, we'll decode the QUERY_EVENT — the event that records SQL statements like DDL commands and transaction boundaries.

Next up: Part 6: QUERY_EVENT — DDL Statements and Transaction Boundaries

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