In this fifth post of our series, we decode the GTID_LOG_EVENT — the event that marks every transaction with a globally unique identifier.
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.
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.
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 |
$ 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.
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
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.
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.
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
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.
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.
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.
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)
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.
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 |
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.
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.
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
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
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.
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)
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]
body_size = event_size - 19 - 42 - 4
body = f.read(body_size)
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
trx_len, offset = read_packed_int(body, offset)
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.
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.
