20.7.2 Event Header Fields

20.7.2.1 Event Flags

Each event starts with a header of size LOG_EVENT_HEADER_LEN. The value of this constant is 13 in MySQL 3.23 (v1 format), and 19 in MySQL 4.0 and up (v3 format and up). The value is larger as of 4.0 because next position and flags fields were added to the header format then:

• v1: 13 bytes: timestamp + type code + server ID + event length

• v3: 19 bytes: v1 fields + next position + flags

• v4: 19 bytes or more: v3 fields + possibly other information

The header for any version is a superset of the header for all earlier versions:

• The first 13 bytes for v3 and v4 are the same as those for v1.

• The first 19 bytes for v4 are the same as those for v3.

Because the event header in a newer binary log format starts with the header of the old formats, headers in different formats are backward compatible.

v1 event header:

+============================+
| timestamp         0 : 4    |
+----------------------------+
| type_code         4 : 1    |
+----------------------------+
| server_id         5 : 4    |
+----------------------------+
| event_length      9 : 4    |
+============================+

The 13 bytes of the v1 header are also present in the header of all subsequent binary log versions.

v3 event header:

+============================+
| timestamp         0 : 4    |
+----------------------------+
| type_code         4 : 1    |
+----------------------------+
| server_id         5 : 4    |
+----------------------------+
| event_length      9 : 4    |
+----------------------------+
| next_position    13 : 4    |
+----------------------------+
| flags            17 : 2    |
+============================+

Compared to v1, the header in v3 and up contains two additional fields, for a total of 19 bytes.

v4 event header:

+============================+
| timestamp         0 : 4    |
+----------------------------+
| type_code         4 : 1    |
+----------------------------+
| server_id         5 : 4    |
+----------------------------+
| event_length      9 : 4    |
+----------------------------+
| next_position    13 : 4    |
+----------------------------+
| flags            17 : 2    |
+----------------------------+
| extra_headers    19 : x-19 |
+============================+

The v4 format includes an extra_headers field; this is a mechanism for adding extra fields to the header without breaking the format. This extension mechanism is implemented via the format description event that appears as the first event in the file. (See Binary Log Versions for details.) Currently, x = 19, so the extra_headers field is empty; thus, the v4 header is the same as the v3 header.

Note: The extra_headers field does not appear in the FORMAT_DESCRIPTION_EVENT or ROTATE_EVENT header.

The offsets of several fields within the event header are available as constants in log_event.h:

• EVENT_TYPE_OFFSET = 4

• SERVER_ID_OFFSET = 5

• EVENT_LEN_OFFSET = 9

• LOG_POS_OFFSET = 13

• FLAGS_OFFSET = 17

The header fields contain the following information:

• timestamp

4 bytes. This is the time at which the statement began executing. It is represented as the number of seconds since 1970 (UTC), like the TIMESTAMP SQL data type.

• type_code

1 byte. The type of event. 1 means START_EVENT_V3, 2 means QUERY_EVENT, and so forth. These numbers are defined in the enum Log_event_type enumeration in log_event.h. (See Event Classes and Types.)

• server_id

4 bytes. The ID of the mysqld server that originally created the event. It comes from the server-id option that is set in the server configuration file for the purpose of replication. The server ID enables endless loops to be avoided when circular replication is used (with option --log-slave-updates on). Suppose that M1, M2, and M3 have server ID values of 1, 2, and 3, and that they are replicating in circular fashion: M1 is the master for M2, M2 is the master for M3, and M3 is that master for M1. The master/server relationships look like this:

M1---->M2
 ^      |
 |      |
 +--M3<-+

A client sends an INSERT statement to M1. This is executed on M1 and written to its binary log with an event server ID of 1. The event is sent to M2, which executes it and writes it to its binary log; the event is still written with server ID 1 because that is the ID of the server that originally created the event. The event is sent to M3, which executes it and writes it to its binary log, still with server ID 1. Finally, the event is sent to M1, which sees server ID = 1 and understands this event originated from itself and therefore must be ignored.

• event_length

4 bytes. The total size of this event. This includes both the header and data parts. Most events are less than 1000 bytes, except when using LOAD DATA INFILE (where events contain the loaded file, so they can be big).

• next_position (not present in v1 format).

4 bytes. The position of the next event in the master's binary log. The format differs between binlogs and relay logs, and depending on the version of the server (and for relay logs, depending on the version of the master):

• binlog on a v3 server: Offset to the beginning of the event, counting from the beginning of the binlog file. In other words, equal to the value of tell() just before the event was written.

So the first event of the binlog has next_position = 4, and for events n and n+1, it holds that next_position(n+1) = next_position(n) + event_length(n).

• relay log on a v3 server where the master uses v1: Probably 0, but I can't test this because I don't know how to run a 3.23 server.

• relay log on a v3 server where the master uses v3: Offset to the beginning of the event as it was in the master's binlog file, counting from the beginning of the master's binlog file.

The slave's relay log can be different from the master's binlog, so next_position can be different from the offset of the event in the relay log, counting from the beginning of the relay log file. However, if both event n and event n+1 originate from the master, it holds that next_position(n+1) = next_position(n) + event_length(n);

• binlog on a v4 server: Offset to the end of the event, counting from the beginning of the binlog file. In other words, equal to the value of tell() just after the event was written.

So the first event of the binlog has next_position = 4 + event_length, and for events number n and n+1, it holds that next_position(n+1) = next_position(n) + event_length(n+1).

• relay log on a v4 server: Offset to the end of the event as it was in the master's binlog file, counting from the beginning of the master's binlog file.

The slave's relay log can be different from the master's binlog, so next_position can be different from the offset of the event in the relay log, counting from the beginning of the relay log file. However, if both event n and event n+1 originate from the master, it holds that next_position(n+1) = next_position(n) + event_length(n+1).

The next_position is used on the slave in two cases:

• for SHOW SLAVE STATUS to be able to show coordinates of the last executed event in the master's coordinate system.

• for START SLAVE UNTIL MASTER_LOG_FILE=x, MASTER_LOG_POS=y, so that the master's coordinates can be used.

In 5.0 and up, next_position is called "end_log_pos" in the output from mysqlbinlog and SHOW BINLOG EVENTS. In 4.1, next_position is called "log_pos" in the output from mysqlbinlog and "orig_log_pos" in the output from SHOW BINLOG EVENTS.

• flags (not present in v1 format)

2 bytes. The possible flag values are described at Event Flags.

• extra_headers (not present in v1, v3 formats)

Variable-sized. The size of this field is determined by the format description event that occurs as the first event in the file. Currently, the size is 0, so, in effect, this field never actually occurs in any event. At such time as the size becomes nonzero, this field still will not appear in events of type FORMAT_DESCRIPTION_EVENT or ROTATE_EVENT.