In this second post of our series, we examine the binary log file header and the 19-byte common event header that every event shares.
Every MySQL binary log file begins with a 4-byte magic number that identifies it as a binary log:
| Bytes | Hex | ASCII | Meaning |
|---|
| 4 | FE 62 69 6E | .bin | Unencrypted binary log |
| 4 | FD 62 69 6E | .bin | Encrypted binary log |
The first byte distinguishes encrypted (0xFD) from unencrypted (0xFE) logs. For encrypted logs, an additional 508 bytes of encryption metadata follow the magic number.
Let's verify this with our binary log file:
$ xxd -l 4 binlog.000024
00000000: fe62 696e .bin
We see fe 62 69 6e — this is an unencrypted binary log. The ASCII representation on the right shows .bin (the 0xFE byte isn't a printable character, so xxd shows a dot).
Checkpoint: Position 0-3 = Magic number. The first event starts at position 4.
Every event in the binary log (version 4+) shares a common 19-byte header structure. This header tells us the event's type, size, and position in the log:
| Field | Size | Description |
|---|
| Timestamp | 4 bytes | Unix epoch seconds when the event was created |
| Event Type | 1 byte | Identifies how to interpret the payload |
| Server ID | 4 bytes | The server that originated this event (prevents circular replication) |
| Event Size | 4 bytes | Total size of this event in bytes (header + payload) |
| Next Position | 4 bytes | File offset where the next event begins |
| Flags | 2 bytes | Event-specific flags (bit field) |
Let's read the first event header from our binary log. The first event starts at position 4 (right after the magic number):
$ xxd -s 4 -l 19 -c 19 binlog.000024
00000004: 6e0f 3568 0f01 0000 007a 0000 007e 0000 0000 00 n.5h.....z...~....
The hex bytes are: 6e0f3568 0f 01000000 7a000000 7e000000 0000
Let's decode each field:
Remember, this is little-endian! We read it as 0x68350f6e:
0x68350f6e = 1748307822 (decimal)
Converting to a human-readable date: 2025-05-27 01:03:42 UTC
The event type is 0x0f = 15, which is FORMAT_DESCRIPTION_EVENT. This is always the first event in a binary log file — it describes the format of all subsequent events.
Little-endian: 0x00000001 = 1
This is the server_id of the MySQL instance that created this event.
Little-endian: 0x0000007a = 122 bytes
This is the total size of the event, including the 19-byte header and any checksum.
Little-endian: 0x0000007e = 126
The next event in the file starts at byte position 126. We can verify: 4 (start) + 122 (size) = 126 ✓
0x0000 = no special flags set for this event.
Let's visualize our decoding:
Position 4-22 (Event Header):
6e0f3568 0f 01000000 7a000000 7e000000 0000
│ │ │ │ │ │
│ │ │ │ │ └─→ Flags: 0x0000
│ │ │ │ └───────────→ Next Position: 126
│ │ │ └────────────────────→ Event Size: 122 bytes
│ │ └─────────────────────────────→ Server ID: 1
│ └────────────────────────────────→ Event Type: 15 (FORMAT_DESCRIPTION_EVENT)
└─────────────────────────────────────────→ Timestamp: 1748307822 (2025-05-27 01:03:42)
Here are the event types we'll encounter in this series:
| Type ID | Hex | Name | Description |
|---|
| 2 | 0x02 | QUERY_EVENT | DDL statements, transaction BEGIN |
| 4 | 0x04 | ROTATE_EVENT | Log rotation marker |
| 15 | 0x0f | FORMAT_DESCRIPTION_EVENT | Binary log format descriptor |
| 16 | 0x10 | XID_EVENT | Transaction commit marker |
| 19 | 0x13 | TABLE_MAP_EVENT | Table structure for row events |
| 30 | 0x1e | WRITE_ROWS_EVENT | Row insert data |
| 31 | 0x1f | UPDATE_ROWS_EVENT | Row update data (before + after) |
| 32 | 0x20 | DELETE_ROWS_EVENT | Row delete data |
| 33 | 0x21 | GTID_LOG_EVENT | Global Transaction ID |
| 35 | 0x23 | PREVIOUS_GTIDS_LOG_EVENT | GTIDs from prior logs |
Every event in a binary log follows this pattern:
┌───────────────────────────────────────────────────┐
│ Common Header (19 bytes) │
├───────────────────────────────────────────────────┤
│ Event-Specific Payload (variable) │
├───────────────────────────────────────────────────┤
│ CRC32 Checksum (4 bytes) │
└───────────────────────────────────────────────────┘
The 4-byte CRC32 checksum at the end is included when checksums are enabled (default since MySQL 5.6.2). The checksum covers the entire event from header through payload.
Using the header information, we can traverse the entire binary log:
- Start at position 4 (after magic number)
- Read the 19-byte header
- Extract
event_size to know total event length
- Extract
next_position to find the next event
- Repeat until end of file
Here's how the first few events chain together in our log:
Position 0: Magic Number (4 bytes)
Position 4: FORMAT_DESCRIPTION_EVENT → Next: 126
Position 126: PREVIOUS_GTIDS_LOG_EVENT → Next: 197
Position 197: GTID_LOG_EVENT → Next: 276
Position 276: QUERY_EVENT (CREATE TABLE) → Next: 458
...
Here are some commands to explore:
xxd -l 4 binlog.000024
xxd -s 4 -l 19 binlog.000024
xxd -s 126 -l 19 binlog.000024
Or with Python:
import struct
from datetime import datetime
with open('binlog.000024', 'rb') as f:
magic = f.read(4)
print(f"Magic: {magic.hex()} = {magic}")
header = f.read(19)
timestamp, event_type, server_id, event_size, next_pos, flags = \
struct.unpack('<IBIIIH', header)
print(f"\nEvent Header:")
print(f" Timestamp: {timestamp} ({datetime.utcfromtimestamp(timestamp)})")
print(f" Event Type: {event_type}")
print(f" Server ID: {server_id}")
print(f" Event Size: {event_size}")
print(f" Next Position: {next_pos}")
print(f" Flags: 0x{flags:04x}")
Now that we understand the file header and common event header structure, we're ready to decode specific event types. In the next post, we'll examine the FORMAT_DESCRIPTION_EVENT — the critical first event that tells MySQL (and us) how to interpret everything that follows.
Next up: Part 3: FORMAT_DESCRIPTION_EVENT — The Self-Describing Event
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.
