解析MYSQL BINLOG 二進位制格式(3)--QUERY_EVENT

原創：轉載請說明出處謝謝！
上接
http://blog.itpub.net/7728585/viewspace-2133188/ 解析MYSQL BINLOG 二進位制格式(1)--準備工作
http://blog.itpub.net/7728585/viewspace-2133189/ 解析MYSQL BINLOG 二進位制格式(2)--FORMAT_DESCRIPTION_EVENT

class:Query_log_event
event:QUERY_EVENT
event_code:02

參考源：
1、log_event.h 中關於class Query_log_event的解釋
2、log_event.cc
3、internals-en.epub

文件和原始碼解釋都為如果發生改變資料庫的語句都會在這部分顯示
Query_log_event is created for each query that modifies the
database, unless the query is logged row-based.
但是需要改考慮這裡的修改，如果按照DDL和DML分，
DDL：我們知道BINLOG只是記錄了語句就在這部分顯示
DML：DML會記錄這部分因為這裡考慮為ROW-FORMAT格式的沒有語句他更改記錄在
Table_map_log_event/TABLE_MAP_EVENT typecode=19
Write_rows_log_event/WRITE_ROW_EVENT typecode=30
Update_rows_log_event/UPDATE_ROW_EVENT typecode=31
Delele_rows_log_event/DELETE_ROW_EVENT typecode=32
事件中了。但是他會記錄一個BEGIN如：
#170209 7:12:19 server id 93157 end_log_pos 585 CRC32 0x3e6c10f8 Query thread_id=2 exec_time=1 error_code=0
SET TIMESTAMP=1486595539/*!*/;
BEGIN
/*!*/;
及這裡的
00000240 42 45 47 49 4e f8 10 6c 3e d3 a5 9b 58 13 e5 6b |BEGIN..l>...X..k|

1、fixed data part
4 bytes:一個用於指定執行緒id的值原始碼中叫做slave_proxy_id，主要用於
建立臨時表的避免重複
4 bytes:語句的執行時間，單位秒
1 bytes:執行語句的預設資料庫名字長度，原始碼解釋為
The length of the name of the currently selected database
2 bytes:執行語句的錯誤碼，如果是0就表示沒有錯誤，主要用於一些無事物引擎比如MyISAM引擎一個語句
如insert select 執行了一部分的情況，當slave進行復制的時候檢查這個錯誤碼如果不相同則
停止複製，所有的錯誤碼寫到了mysqld_error.h 中可以自行參考
原始碼解釋為：
Error code generated by the master. If the master fails, the
slave will fail with the same error code, except for the error
codes ER_DB_CREATE_EXISTS == 1007 and ER_DB_DROP_EXISTS == 1008.
2 bytes:variable data part中status_vars block的長度，詳見variable data part
原始碼解釋為：
The length of the status_vars block of the Body, in bytes
2、variable data part
var-size:status_vars block 可能存在多個叫做status variable的鍵值對中，每個
status variable包含一個1 bytes的variable值餘下的具體值，關於各種的
具體值附在最後為原始碼註釋擷取，當然internals-en.epub也有一部分下面是
internals-en.epub中的部分：
Q_FLAGS_CODE=0:4 bytes,只在MYSQL 5.0寫入，不考慮
Q_SQL_MODE_CODE=1:8 bytes,他是一個每一位代表SQL_MODE中的一個值，參考
最後原始碼的解釋
Q_CATALOG_CODE=2:只在MYSQL 5.0.0到5.0.3使用不考慮
Q_AUTO_INCREMENT=3:2 bytes非負整數用於表示引數auto_increment_increment
和auto_increment_offset,這個只會在auto_increment大於
1的時候出現
Q_CHARSET_CODE=4:6 bytes用於表示character_set_client,collation_connection
和collation_server引數(totally 2+2+2=6 bytes)參考最後原始碼
解釋
Q_TIME_ZONE_CODE=5:用於描述time zone資訊
Q_CATALOG_NZ_CODE=6:用於描述catalog name，長度佔用一個位元組，隨後這個值為std
Q_LC_TIME_NAMES_CODE=7:2 bytes 非負整數，只有當lc_time_names不設定為en_US的時候使用
Q_CHARSET_DATABASE_CODE=8:2 bytes 非負整數為collation_database系統變數，5.7原始碼解釋
說這部分新版本不一定使用。

當然這裡還有很多，詳細參考後面給出的原始碼解釋和internals-en.epub相關部分

var-size:資料庫名字，以0X00結尾，既然0X00結尾那麼其長度為資料庫名字實際長度+1
原始碼描述為：
db_len+1 The currently selected database, as a null-terminated string.
var-size：這就是真正的語句，文件中說這個域的長度不固定，當然固定因為他是具體的語句
但是他的長度可以透過下面的計算方式得出：
總長度(event_header中)-event_header長度(v4為19)-fixed data part(13)-
status_vars block的長度 - 預設資料庫名字長度來得到因為這些定值

實際解析:
我使用了語句 create table testctas1 as select * from testcast，當然我是5.7關閉了GTID
不然這個語句會報錯的

這樣會發現這個語句在binlog中轉變為2部分
1、create table DDL部分
2、DML部分

DML部分我們先不考慮，下面是CREATE TABLE DDL部分的解析

binlog的Query_log_event 二進位制部分

點選(此處)摺疊或開啟

00000100 d3 a5 9b 58 02 e5 6b 01 00 bd 00 00 00 | ..X..k......|
00000110 c0 01 00 00 00 00 02 00 00 00 01 00 00 00 04 00 |................|
00000120 00 1a 00 00 00 00 00 00 01 00 00 20 40 00 00 00 |........... @...|
00000130 00 06 03 73 74 64 04 21 00 21 00 21 00 74 65 73 |...std.!.!.!.tes|
00000140 74 00 43 52 45 41 54 45 20 54 41 42 4c 45 20 60 |t.CREATE TABLE `|
00000150 74 65 73 74 63 74 61 73 31 60 20 28 0a 20 20 60 |testctas1` (. `|
00000160 69 64 31 60 20 69 6e 74 28 31 31 29 20 44 45 46 |id1` int(11) DEF|
00000170 41 55 4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 69 64 |AULT NULL,. `id|
00000180 32 60 20 69 6e 74 28 31 31 29 20 44 45 46 41 55 |2` int(11) DEFAU|
00000190 4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 6e 61 6d 65 |LT NULL,. `name|
000001a0 60 20 76 61 72 63 68 61 72 28 32 30 29 20 44 45 |` varchar(20) DE|
000001b0 46 41 55 4c 54 20 4e 55 4c 4c 0a 29 66 a6 84 c9 |FAULT NULL.)f...|

mysqlbinlog解析如下：
# at 259
#170209 7:12:19 server id 93157 end_log_pos 448 CRC32 0xc984a666 Query thread_id=2 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1486595539/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `testctas1` (
`id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
)
/*!*/;

--event header:
這部分不想過多解析，如果不懂看前面的文章，因為都是一樣的簡單解析
d3 a5 9b 58: timestamp，小端顯示
02: event_type為02
e5 6b 01 00:service_id,小端顯示0X016be5及十進位制93157
mysql> show variables like '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 93157 |
+---------------+-------+
bd 00 00 00:event長度
c0 01 00 00:下一個event位置
00 00:flags
--fixed data part

02 00 00 00:slave_proxy_id小端顯示0X00000002 這個和mysqlbinlog 解析的 thread_id=2以及SET @@session.pseudo_thread_id=2/*!*/;一致
01 00 00 00:執行時間小端顯示0X00000001 這個和mysqlbinlog 解析的exec_time=1
04:預設資料庫長度我的資料庫名字為test當然長度也就是0X04也就是4
00 00:執行錯誤碼，我這裡沒有錯誤全是0X00
1a 00:status_vars block及0X001a及26，如果仔細數一下後面的status_vars block的大小確實是26，其實我就是按照這個分割開的。

--variable data part

00 00 00 00 00 01 00 00 20 40 00 00 00 00 06 03
73 74 64 04 21 00 21 00 21 00 :
這部分是非常重要的status_vars block需要一個鍵值對一個解釋
-00 00 00 00 00:為Q_FLAGS2_CODE,
   文件上說它只在MYSQL 5.0中寫入
   鍵為00 值為00 00 00 00
-01 00 00 20 40 00 00 00 00：
為Q_SQL_MODE_CODE,
鍵為01 值為00 00 20 40 00 00 00 00 小端顯示
為0X40200000則換算一下為1075838976
可以看到這個和MYSQLBINLOG解析的
SET @@session.sql_mode=1075838976/*!*/;
可以看到一致，關於這個也可以看看最後的
關於原始碼的Q_SQL_MODE_CODE解釋
-06 03 73 74 64：為Q_CATALOG_NZ_CODE，
鍵值06 值為03 73 74 64，0X03為長度，0X73 0X74 0X64就是
std的ASCII
-04 21 00 21 00 21 00:為Q_CHARSET_CODE
鍵值04 值為21 00 21 00 21 00，這個都是0X0021 就是33
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
可以看到和MYSQLBINLOG解析一致

74 65 73 74 00:這部分是資料庫的實際名字以0X00結尾0X74 0X65 0X73 0X74就是test的ASCII值
和MYSQLBINLOG解析的use `test`/*!*/; 一致

43 52 45 41 54 45 20 54 41 42 4c 45 20 60
74 65 73 74 63 74 61 73 31 60 20 28 0a 20 20 60
69 64 31 60 20 69 6e 74 28 31 31 29 20 44 45 46
41 55 4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 69 64
32 60 20 69 6e 74 28 31 31 29 20 44 45 46 41 55
4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 6e 61 6d 65
60 20 76 61 72 63 68 61 72 28 32 30 29 20 44 45
46 41 55 4c 54 20 4e 55 4c 4c 0a 29 :
這部分就是實際的語句的文字值裡面全是ASCII二進位制顯示而已

點選(此處)摺疊或開啟

00000140 74 00 43 52 45 41 54 45 20 54 41 42 4c 45 20 60 |t.CREATE TABLE `|
00000150 74 65 73 74 63 74 61 73 31 60 20 28 0a 20 20 60 |testctas1` (. `|
00000160 69 64 31 60 20 69 6e 74 28 31 31 29 20 44 45 46 |id1` int(11) DEF|
00000170 41 55 4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 69 64 |AULT NULL,. `id|
00000180 32 60 20 69 6e 74 28 31 31 29 20 44 45 46 41 55 |2` int(11) DEFAU|
00000190 4c 54 20 4e 55 4c 4c 2c 0a 20 20 60 6e 61 6d 65 |LT NULL,. `name|
000001a0 60 20 76 61 72 63 68 61 72 28 32 30 29 20 44 45 |` varchar(20) DE|
000001b0 46 41 55 4c 54 20 4e 55 4c 4c 0a 29 66 a6 84 c9 |FAULT NULL.)f...|

這裡也能看到。

66 a6 84 c9: CRC32校驗碼

後記：
Query_log_event 是binlog中的關鍵的EVENT，DDL記錄了語句並且記錄了很多關於語句的引數環境資訊可以看看

use `test`/*!*/;
SET TIMESTAMP=1486595539/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

mysqlbinlog解析出來的這些大部分是不是都在本文提到過？

關於ROW_format的DML的Query_log_event雖然沒有具體語句但是它確實存在，記錄語句為BEGIN，這個在以後的文章中會在解析。

點選(此處)摺疊或開啟

@anchor Table_query_log_event_status_vars
<table>
<caption>Status variables for Query_log_event</caption>
<tr>
<th>Status variable</th>
<th>1 byte identifier</th>
<th>Format</th>
<th>Description</th>
</tr>
<tr>
<td>flags2</td>
<td>Q_FLAGS2_CODE == 0</td>
<td>4 byte bitfield</td>
<td>The flags in @c thd->options, binary AND-ed with @c
OPTIONS_WRITTEN_TO_BIN_LOG. The @c thd->options bitfield contains
options for "SELECT". @c OPTIONS_WRITTEN identifies those options
that need to be written to the binlog (not all do). Specifically,
@c OPTIONS_WRITTEN_TO_BIN_LOG equals (@c OPTION_AUTO_IS_NULL | @c
OPTION_NO_FOREIGN_KEY_CHECKS | @c OPTION_RELAXED_UNIQUE_CHECKS |
@c OPTION_NOT_AUTOCOMMIT), or 0x0c084000 in hex.
These flags correspond to the SQL variables SQL_AUTO_IS_NULL,
FOREIGN_KEY_CHECKS, UNIQUE_CHECKS, and AUTOCOMMIT, documented in
the "SET Syntax" section of the MySQL Manual.
This field is always written to the binlog in version >= 5.0, and
never written in version < 5.0.
</td>
</tr>
<tr>
<td>sql_mode</td>
<td>Q_SQL_MODE_CODE == 1</td>
<td>8 byte bitfield</td>
<td>The @c sql_mode variable. See the section "SQL Modes" in the
MySQL manual, and see sql_priv.h for a list of the possible
flags. Currently (2007-10-04), the following flags are available:
<pre>
MODE_REAL_AS_FLOAT==0x1
MODE_PIPES_AS_CONCAT==0x2
MODE_ANSI_QUOTES==0x4
MODE_IGNORE_SPACE==0x8
MODE_NOT_USED==0x10
MODE_ONLY_FULL_GROUP_BY==0x20
MODE_NO_UNSIGNED_SUBTRACTION==0x40
MODE_NO_DIR_IN_CREATE==0x80
MODE_POSTGRESQL==0x100
MODE_ORACLE==0x200
MODE_MSSQL==0x400
MODE_DB2==0x800
MODE_MAXDB==0x1000
MODE_NO_KEY_OPTIONS==0x2000
MODE_NO_TABLE_OPTIONS==0x4000
MODE_NO_FIELD_OPTIONS==0x8000
MODE_MYSQL323==0x10000
MODE_MYSQL323==0x20000
MODE_MYSQL40==0x40000
MODE_ANSI==0x80000
MODE_NO_AUTO_VALUE_ON_ZERO==0x100000
MODE_NO_BACKSLASH_ESCAPES==0x200000
MODE_STRICT_TRANS_TABLES==0x400000
MODE_STRICT_ALL_TABLES==0x800000
MODE_NO_ZERO_IN_DATE==0x1000000
MODE_NO_ZERO_DATE==0x2000000
MODE_INVALID_DATES==0x4000000
MODE_ERROR_FOR_DIVISION_BY_ZERO==0x8000000
MODE_TRADITIONAL==0x10000000
MODE_NO_AUTO_CREATE_USER==0x20000000
MODE_HIGH_NOT_PRECEDENCE==0x40000000
MODE_PAD_CHAR_TO_FULL_LENGTH==0x80000000
</pre>
All these flags are replicated from the server. However, all
flags except @c MODE_NO_DIR_IN_CREATE are honored by the slave;
the slave always preserves its old value of @c
MODE_NO_DIR_IN_CREATE. For a rationale, see comment in
@c Query_log_event::do_apply_event in @c log_event.cc.
This field is always written to the binlog.
</td>
</tr>
<tr>
<td>catalog</td>
<td>Q_CATALOG_NZ_CODE == 6</td>
<td>Variable-length string: the length in bytes (1 byte) followed
by the characters (at most 255 bytes)
</td>
<td>Stores the client's current catalog. Every database belongs
to a catalog, the same way that every table belongs to a
database. Currently, there is only one catalog, "std".
This field is written if the length of the catalog is > 0;
otherwise it is not written.
auto_increment
Q_AUTO_INCREMENT == 3
two 2 byte unsigned integers, totally 2+2=4 bytes
The two variables auto_increment_increment and
auto_increment_offset, in that order. For more information, see
"System variables" in the MySQL manual.
This field is written if auto_increment > 1. Otherwise, it is not
written.
charset
Q_CHARSET_CODE == 4
three 2 byte unsigned integers, totally 2+2+2=6 bytes
The three variables character_set_client,
collation_connection, and collation_server, in that order.
character_set_client is a code identifying the character set and
collation used by the client to encode the query.
collation_connection identifies the character set and collation
that the master converts the query to when it receives it; this is
useful when comparing literal strings. collation_server is the
default character set and collation used when a new database is
created.
See also "Connection Character Sets and Collations" in the MySQL
5.1 manual.
All three variables are codes identifying a (character set,
collation) pair. To see which codes map to which pairs, run the
query "SELECT id, character_set_name, collation_name FROM
COLLATIONS".
Cf. Q_CHARSET_DATABASE_CODE below.
This field is always written.
time_zone
Q_TIME_ZONE_CODE == 5
Variable-length string: the length in bytes (1 byte) followed
by the characters (at most 255 bytes).
The time_zone of the master.
See also "System Variables" and "MySQL Server Time Zone Support"
in the MySQL manual.
This field is written if the length of the time zone string is >
0; otherwise, it is not written.
lc_time_names_number
Q_LC_TIME_NAMES_CODE == 7
2 byte integer
A code identifying a table of month and day names. The
mapping from codes to languages is defined in @c sql_locale.cc.
This field is written if it is not 0, i.e., if the locale is not
en_US.
charset_database_number
Q_CHARSET_DATABASE_CODE == 8
2 byte integer
The value of the collation_database system variable (in the
source code stored in @c thd->variables.collation_database), which
holds the code for a (character set, collation) pair as described
above (see Q_CHARSET_CODE).
collation_database was used in old versions (???WHEN). Its value
was loaded when issuing a "use db" query and could be changed by
issuing a "SET collation_database=xxx" query. It used to affect
the "LOAD DATA INFILE" and "CREATE TABLE" commands.
In newer versions, "CREATE TABLE" has been changed to take the
character set from the database of the created table, rather than
the character set of the current database. This makes a
difference when creating a table in another database than the
current one. "LOAD DATA INFILE" has not yet changed to do this,
but there are plans to eventually do it, and to make
collation_database read-only.
This field is written if it is not 0.
table_map_for_update
Q_TABLE_MAP_FOR_UPDATE_CODE == 9
8 byte integer
The value of the table map that is to be updated by the
multi-table update query statement. Every bit of this variable
represents a table, and is set to 1 if the corresponding table is
to be updated by this statement.
The value of this variable is set when executing a multi-table update
statement and used by slave to apply filter rules without opening
all the tables on slave. This is required because some tables may
not exist on slave because of the filter rules.
@subsection Query_log_event_notes_on_previous_versions Notes on Previous Versions
* Status vars were introduced in version 5.0. To read earlier
versions correctly, check the length of the Post-Header.
* The status variable Q_CATALOG_CODE == 2 existed in MySQL 5.0.x,
where 0<=x<=3. It was identical to Q_CATALOG_CODE, except that the
string had a trailing '\0'. The '\0' was removed in 5.0.4 since it
was redundant (the string length is stored before the string). The
Q_CATALOG_CODE will never be written by a new master, but can still
be understood by a new slave.
* See Q_CHARSET_DATABASE_CODE in the table above.
* When adding new status vars, please don't forget to update the
MAX_SIZE_LOG_EVENT_STATUS, and update function code_name
*/
class Query_log_event: public Log_event
{
LEX_STRING user;
LEX_STRING host;
protected:
Log_event::Byte* data_buf;
public:
const char* query;
const char* catalog;
const char* db;
/*
If we already know the length of the query string
we pass it with q_len, so we would not have to call strlen()
otherwise, set it to 0, in which case, we compute it with strlen()
*/
uint32 q_len;
uint32 db_len;
uint16 error_code;
ulong thread_id;
/*
For events created by Query_log_event::do_apply_event (and
Load_log_event::do_apply_event()) we need the *original* thread
id, to be able to log the event with the original (=master's)
thread id (fix for BUG#1686).
*/
ulong slave_proxy_id;
/*
Binlog format 3 and 4 start to differ (as far as class members are
concerned) from here.
*/
uint catalog_len; // <= 255 char; 0 means uninited
/*
We want to be able to store a variable number of N-bit status vars:
(generally N=32; but N=64 for SQL_MODE) a user may want to log the number
of affected rows (for debugging) while another does not want to lose 4
bytes in this.
The storage on disk is the following:
status_vars_len is part of the post-header,
status_vars are in the variable-length part, after the post-header, before
the db & query.
status_vars on disk is a sequence of pairs (code, value) where 'code' means
'sql_mode', 'affected' etc. Sometimes 'value' must be a short string, so
its first byte is its length. For now the order of status vars is:
flags2 - sql_mode - catalog - autoinc - charset
We should add the same thing to Load_log_event, but in fact
LOAD DATA INFILE is going to be logged with a new type of event (logging of
the plain text query), so Load_log_event would be frozen, so no need. The
new way of logging LOAD DATA INFILE would use a derived class of
Query_log_event, so automatically benefit from the work already done for
status variables in Query_log_event.
*/
uint16 status_vars_len;
/*
'flags2' is a second set of flags (on top of those in Log_event), for
session variables. These are thd->options which is & against a mask
(OPTIONS_WRITTEN_TO_BIN_LOG).
flags2_inited helps make a difference between flags2==0 (3.23 or 4.x
master, we don't know flags2, so use the slave server

解析MYSQL BINLOG 二進位制格式(3)--QUERY_EVENT

相關文章