DATABASE

MySQL : The Binary Log - The mysqlbinlog Utility (part 2) - Interpreting Events

12/13/2013 2:09:18 AM

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 For⁠⁠⁠mat_de⁠scrip⁠tion 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 For⁠⁠mat_de⁠scrip⁠tion and Rotate events. As described earlier, the length of the common header for the For⁠mat_de⁠scrip⁠tion and Ro⁠tate 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.

Other  
  •  SQL Server 2012 : Exploring SQL CLR - Examining and Managing CLR Types in a Database
  •  SQL Server 2012 : Exploring SQL CLR - Security
  •  SQL Server 2012 : Exploring SQL CLR - SQL CLR Types
  •  SQL Server 2012 : Exploring SQL CLR - CLR Aggregates
  •  SQL Server 2012 : Exploring SQL CLR - CLR Triggers
  •  My SQL : Replication for High Availability - Procedures (part 8) - Circular Replication
  •  My SQL : Replication for High Availability - Procedures (part 7) - Slave Promotion - Slave promotion in Python
  •  My SQL : Replication for High Availability - Procedures (part 6) - Slave Promotion - A revised method for promoting a slave
  •  My SQL : Replication for High Availability - Procedures (part 5) - Semisynchronous Replication - Configuring semisynchronous replication
  •  My SQL : Replication for High Availability - Procedures (part 4) - Dual Masters - Replicated disks using DRBD , Bidirectional replication
  •  
    Top 10
    Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
    Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
    OPEL MERIVA : Making a grand entrance
    FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
    BMW 650i COUPE : Sexy retooling of BMW's 6-series
    BMW 120d; M135i - Finely tuned
    PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
    PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    VIDEO TUTORIAL
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS