2. Interpreting Events
Sometimes, the standard information printed by mysqlbinlog is not sufficient for spotting a
problem, so it is necessary to go into the details of the event and
investigate its content. To handle such situations, you can pass
the --hexdump option to
tell mysqlbinlog to write the actual
bytes of the events.
Before going into the details of the events, here are some general
rules about the format of the data in the binary log:
Integer data
Integer fields in the binary log are printed in
little-endian order, so you have to read integer fields backward.
This means that, for example, the 32-bit block 03 01 00 00
represents the hexadecimal number 103.
String data
String data is usually stored both with length data and
null-terminated. Sometimes, the length data appears just before
the string and sometimes it is stored in the post header.
The most common of all the events is the Query event, so let’s
concentrate on it first. Example 3 shows the output for such
an event.
Example 3. Output when using option - -hexdump
$ sudo mysqlbinlog \
> --force-if-open \
> --hexdump \
> --base64-output=never \
> /var/lib/mysql1/mysqld1-bin.000038
.
.
.
1 # at 496
2 #100123 7:21:33 server id 1 end_log_pos 643
3 # Position Timestamp Type Master ID Size Master Pos Flags
4 # 1f0 6d 95 5a 4b 02 01 00 00 00 93 00 00 00 83 02 00 00 10 00
5 # 203 06 00 00 00 00 00 00 00 04 00 00 1a 00 00 00 40 |................|
6 # 213 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std|
7 # 223 04 08 00 08 00 08 00 74 65 73 74 00 49 4e 53 45 |.......test.INSE|
8 # 233 52 54 20 49 4e 54 4f 20 75 73 65 72 28 6e 61 6d |RT.INTO.employee|
9 # 243 65 2c 65 6d 61 69 6c 2c 70 61 73 73 77 6f 72 64 |.name.email.pass|
10 # 253 29 0a 20 20 56 41 4c 55 45 53 20 28 27 6d 61 74 |word....VALUES..|
11 # 263 73 27 2c 27 6d 61 74 73 40 65 78 61 6d 70 6c 65 |.mats...mats.exa|
12 # 273 2e 63 6f 6d 27 2c 40 70 61 73 73 77 6f 72 64 29 |mple.com...passw|
13 # 283 6f 72 64 29 |ord.|
14 # Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1264227693/*!*/;
INSERT INTO employee(name,email,password)
VALUES ('mats','mats@example.com',@password)
|
The first two lines and line 13 are comments listing basic
information that we discussed earlier. Notice that when you use the
--hexdump option, the general information and the event-specific information
are split into two lines, whereas they are merged in the normal
output.
Lines 3 and 4 list the common header:
Timestamp
The timestamp of the event as an integer, stored in
little-endian format.
Type
A single byte representing the type of the event.
Master ID
The server ID of the server that wrote the event, written as
an integer. For the event shown in Example 3, the server ID is
1.
Size
The size of the event in bytes, written as an
integer.
Master Pos
The same as end_log_pos;
that is, the start of the event following this event.
Flags
This field has 16 bits reserved for general flags concerning
the event. The field is mostly unused, but it stores the binlog-in-use flag. As you can see in
Example 3,
the binlog-in-use
flag is set, meaning that the binary log is not closed properly
(in this case, because we didn’t flush the logs before calling
mysqlbinlog).
2.1. Query event post header and body
The Query event is by far the
most used and also the most complicated event issued by the server.
Part of the reason is that it has to carry a lot of information about
the context of the statement when it was executed. As already
demonstrated, integer variables, user variables, and random seeds are
covered using specific events, but it is also necessary to provide
other information, which is part of this event.
The post header for the Query
event consists of five fields. Recall that these fields are of fixed
size and that the length of the post header is given in the Format description event for the binlog
file, meaning that later MySQL versions may add additional fields if
the need should arise.
Thread ID
A four-byte unsigned integer representing the thread ID
that executed the statement. Even though the thread ID is not
always necessary to execute the statement correctly, it is
always written into the event.
Execution time
The number of seconds from the start of execution of the
query to when it was written to the binary log, expressed as a
four-byte unsigned integer.
Database name length
The length of the database name, stored as an unsigned
one-byte integer. The database name is stored in the event body,
but the length is given here.
Error code
The error code resulting from execution of the statement,
stored as a two-byte unsigned integer. This field is included
because, in some cases, statements have to be logged to the
binary log even when they fail.
Status variables length
The length of the block in the event body storing the
status variables, stored as a two-byte unsigned integer. This
status block is sometimes used with a Query event to store various status
variables, such as SQL_MODE.
The event body consists of the following fields, which are all
of variable length.
Status variables
A sequence of status variables. Each status variable is
represented by a single integer followed by the value of the
status variable. The interpretation and length of each status
variable value depends on which status variable it concerns.
Status variables are not always present; they are added only
when necessary. Some examples of status variables follow:
Q_SQL_MODE_CODE
The value of SQL_MODE used when executing the
statement.
Q_AUTO_INCREMENT
This status variable contains the values of auto_increment_increment and
auto_increment_offset
used for the statement, assuming that they are not the
default of 1.
Q_CHARSET
This status variable contains the character set code
and collation used by the connection and the server when
the statement was executed.
Current database
The name of the current database, stored as a null-terminated
string. Notice that the length of the database name is given in
the post header.
Statement text
The statement that was executed. The length of the
statement can be computed from the information in the common
header and the post header. This statement is normally identical
to the original statement written, but in some cases, the
statement is rewritten before it is stored in the binary log.
2.2. Format description event post header and body
The Format_description event
records important information about the binlog file
format, the event format, and the server. Since it has to remain
robust between versions—it
should still be possible to interpret it even if the binlog format
changes—there are some
restrictions on which changes are allowed.
One of the more important restrictions is that the common header
of both the Format_description
event and the Rotate event is
fixed at 19 bytes. This means that it is not possible to extend the
event with new fields in the common header.
The post header and event body for the Format_description event contain the
following fields:
Binlog file version
The version of the binlog file format used by this file.
For MySQL versions 5.0 and later, this is 4.
Server version string
A 50-byte string storing server version information. This
is usually the three-part version number followed by information
about the options used for the build, “5.1.37-1ubuntu5-log,” for
instance.
Creation time
A four-byte integer holding the creation time—the number
of seconds since the epoch—of the first binlog file written by
the server since startup. For later binlog files written by the server, this field
will be zero.
This scheme allows a slave to determine that the server
was restarted and that the slave should reset state and
temporary data—for example, close any open transactions and drop
any temporary tables it has created.
Common header length
The length of the common header for all events in the
binlog file except the Format_description and Rotate events. As described earlier,
the length of the common header for the Format_description and Rotate events is fixed at 19
bytes.
Post-header lengths
This is the only variable-length field of the
Format_description log event. It holds an
array containing the size of the post header for each event in
the binlog file as a one-byte integer. The value 255 is reserved
as the length for the field, so the maximum length of a post
header is 254 bytes.
