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.

---

The Binary Log File Header (Magic Number)

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.

---

The Common Event Header (19 bytes)

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:

Timestamp: 6e0f3568

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

Event Type: 0f

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.

Server ID: 01000000

Little-endian: 0x00000001 = 1

This is the server_id of the MySQL instance that created this event.

Event Size: 7a000000

Little-endian: 0x0000007a = 122 bytes

This is the total size of the event, including the 19-byte header and any checksum.

Next Position: 7e000000

Little-endian: 0x0000007e = 126

The next event in the file starts at byte position 126. We can verify: 4 (start) + 122 (size) = 126 ✓

Flags: 0000

0x0000 = no special flags set for this event.

---

Putting It Together

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)

---

Common Event Types

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

---

The Event Structure Pattern

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.

---

Navigating the Binary Log

Using the header information, we can traverse the entire binary log:

1. Start at position 4 (after magic number)
2. Read the 19-byte header
3. Extract event_size to know total event length
4. Extract next_position to find the next event
5. 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
...

---

Try It Yourself

Here are some commands to explore:

# View the magic number
xxd -l 4 binlog.000024

# View the first event header
xxd -s 4 -l 19 binlog.000024

# View the second event header (at position 126)
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}")

---

What's Next?

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.