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
= 4SERVER_ID_OFFSET
= 5EVENT_LEN_OFFSET
= 9LOG_POS_OFFSET
= 13FLAGS_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 fromSHOW 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
.