Mysql日誌
MySQL 的日誌包括錯誤日誌（ErrorLog），更新日誌（Update Log），二進位制日誌（Binlog），查詢日誌（Query Log），慢查詢日誌（Slow Query Log）等；
更新日誌是老版本的MySQL 才有的，目前已經被二進位制日誌替代；在預設情況下，系統僅僅開啟錯誤日誌，關閉了其他所有日誌，以達到儘可能減少IO損耗提高系統效能的目的，但是在一般稍微重要一點的實際應用場景中，都至少需要開啟二進位制日誌，因為這是MySQL很多儲存引擎進行增量備份的基礎，也是MySQL實現複製的基本條件；
下面介紹的就是二進位制日誌–Binlog
Binlog開啟
預設Binlog是關閉的，首先要開啟才能記錄日誌；
1.檢視是否開啟log_bin
|
1 2 3 4 5 6 |
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ |
2.開啟log_bin
在my.ini中新增配置：
|
1 |
log_bin=D:/mysql/bin-log.log |
3.重啟mysql，再次檢視
|
1 2 3 4 5 6 |
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ |
4.檢視Binlog
4.1顯示binlog的名稱和大小
|
1 2 3 4 5 6 |
mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | bin-log.000001 | 107 | +----------------+-----------+ |
4.2檢視生成的binlog
想用檢視到binlog可以往資料庫中更新資料庫(包括：插入，更新和刪除操作)，查詢sql是無法生成binlog的
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> show binlog events; +----------------+------+-------------+-----------+-------------+--------------- ------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+------+-------------+-----------+-------------+--------------- ------------------------------------------------+ | bin-log.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5. 5.29-log, Binlog ver: 4 | | bin-log.000001 | 107 | Query | 1 | 175 | BEGIN | | bin-log.000001 | 175 | Intvar | 1 | 203 | INSERT_ID=9 | | bin-log.000001 | 203 | Query | 1 | 315 | use `test`; in sert into user (age,name) values(100,"zhaohui") | | bin-log.000001 | 315 | Xid | 1 | 342 | COMMIT /* xid= 41 */ | +----------------+------+-------------+-----------+-------------+--------------- ------------------------------------------------+ |
Binlog相關引數
通過執行如下命令可以獲得關於Binlog 的相關引數：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 0 | +-----------------------------------------+----------------------+ |
1.binlog_cache_size
在事務過程中容納binlog SQL語句的快取大小；binlog快取是伺服器支援事務儲存引擎並且伺服器啟用了二進位制日誌(—log-bin選項)的前提下為每個Session分配的記憶體；
主要是用來提高binlog的寫速度；可以通過MySQL的以下個狀態變數來判斷當前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use
|
1 2 3 4 5 6 7 |
mysql> show status like 'Binlog_cache%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 4 | +-----------------------+-------+ |
Binlog_cache_use：使用緩衝區存放binlog的次數
Binlog_cache_disk_use：使用臨時檔案存放binlog的次數
2.binlog_stmt_cache_size
發生事務時非事務語句的快取的大小，可以通過MySQL 的以下個狀態變數來判斷當前的binlog_stmt_cache_size的狀況：Binlog_stmt_cache_use和Binlog_stmt_cache_disk_use
|
1 2 3 4 5 6 7 |
mysql> show status like 'binlog_stmt_cache%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 1 | +----------------------------+-------+ |
Binlog_stmt_cache_use：緩衝區存放binlog的次數
Binlog_stmt_cache_disk_use：臨時檔案存放binlog的次數
3.max_binlog_cache_size
和binlog_cache_size相對應，但是所代表的是binlog能夠使用的最大cache記憶體大小；binlog_cache_size對應的每個Session，max_binlog_cache_size對應所有Session；
當我們執行多語句事務的時候，所有Session的使用的記憶體超過max_binlog_cache_size的值時，系統可能會報出”Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的錯誤。
4.max_binlog_stmt_cache_size
同max_binlog_cache_size類似，非事務語句binlog能夠使用的最大cache記憶體大小。
5.max_binlog_size
Binlog日誌最大值，預設1G，。該大小並不能非常嚴格控制Binlog大小，尤其是當到達Binlog比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進入當前日誌，直到該事務結束。
6.sync_binlog
同步binlog快取中資料到磁碟的方式：
sync_binlog=0
當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟；
sync_binlog=n（區間0-4294967295）
當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟；
系統預設設定為0，這種情況下效能最好，風險最大，可能導致binlog_cache中的資料丟失；sync_binlog=1效能最差，風險最小。
7.binlog_format
設定binlog的格式，可選值：STATEMENT, ROW, or MIXED；預設是：STATEMENT
7.1 ROW模式
日誌中記錄成每一行資料被修改的形式，然後在slave端再對相同的資料進行修改；
在ROW模式下bin-log中可以不記錄執行的SQL語句的上下文相關的資訊，只需要記錄哪條資料被修改成什麼樣了，不會因為某些語法複製出現問題（比如function，trigger等）；
缺點是每行資料的修改都會記錄，最明顯的就是update語句，導致更新多少條資料就會產生多少事件，使bin-log檔案很大，而複製要網路傳輸，影響效能。
7.2 STATEMENT模式
每一條修改資料的sql都會被記錄到bin-log中，slave端再根據sql語句重現，解決了ROW模式的缺點，不會產生大量是bin-log資料；
缺點是為了讓sql能在slave端正確重現，需要記錄sql在執行的上下文資訊，另外一個問題就是在複製某些特殊的函式或者功能的時候會出現問題，比如sleep()函式。
7.3 MIXED模式
前面兩種模式的結合，根據不同的情況分別使用ROW模式和STATEMENT模式。
更多引數詳見：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html
Binlog結構和內容
日誌由一組二進位制日誌檔案（Binlog）,加上一個索引檔案（index）；Binlog是一個二進位制檔案集合，每個Binlog以一個4位元組的魔數開頭，接著是一組Events；
1.魔數：0xfe62696e對應的是0xfebin；
2.Event：每個Event包含header和data兩個部分；header提供了Event的建立時間，哪個伺服器等資訊，data部分提供的是針對該Event的具體資訊，如具體資料的修改；
3.第一個Event用於描述binlog檔案的格式版本，這個格式就是event寫入binlog檔案的格式；
4.其餘的Event按照第一個Event的格式版本寫入；
5.最後一個Event用於說明下一個binlog檔案；
6.Binlog的索引檔案是一個文字檔案，其中內容為當前的binlog檔案列表，比如：
|
1 |
D:\mysql\bin-log.000001 |
參考：https://dev.mysql.com/doc/internals/en/binary-log-structure-and-contents.html
Binlog的Event型別
官方提供的可能Event型別有36種，具體看下面的列舉：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
enum Log_event_type { UNKNOWN_EVENT= 0, START_EVENT_V3= 1, QUERY_EVENT= 2, STOP_EVENT= 3, ROTATE_EVENT= 4, INTVAR_EVENT= 5, LOAD_EVENT= 6, SLAVE_EVENT= 7, CREATE_FILE_EVENT= 8, APPEND_BLOCK_EVENT= 9, EXEC_LOAD_EVENT= 10, DELETE_FILE_EVENT= 11, NEW_LOAD_EVENT= 12, RAND_EVENT= 13, USER_VAR_EVENT= 14, FORMAT_DESCRIPTION_EVENT= 15, XID_EVENT= 16, BEGIN_LOAD_QUERY_EVENT= 17, EXECUTE_LOAD_QUERY_EVENT= 18, TABLE_MAP_EVENT = 19, PRE_GA_WRITE_ROWS_EVENT = 20, PRE_GA_UPDATE_ROWS_EVENT = 21, PRE_GA_DELETE_ROWS_EVENT = 22, WRITE_ROWS_EVENT = 23, UPDATE_ROWS_EVENT = 24, DELETE_ROWS_EVENT = 25, INCIDENT_EVENT= 26, HEARTBEAT_LOG_EVENT= 27, IGNORABLE_LOG_EVENT= 28, ROWS_QUERY_LOG_EVENT= 29, WRITE_ROWS_EVENT = 30, UPDATE_ROWS_EVENT = 31, DELETE_ROWS_EVENT = 32, GTID_LOG_EVENT= 33, ANONYMOUS_GTID_LOG_EVENT= 34, PREVIOUS_GTIDS_LOG_EVENT= 35, ENUM_END_EVENT /* end marker */ }; |
參考：https://dev.mysql.com/doc/internals/en/event-classes-and-types.html
Event結構官網提供了3個版本，分別是v1，v3，v4：
v1：用在MySQL 3.23
v3：用在MySQL 4.0.2-4.1
v4：用在MySQL 5.0之後
現在MySQL的版本基本都使用5.0之後的版本，可以直接看v4，具體如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
+=====================================+ | event | timestamp 0 : 4 | | header +----------------------------+ | | type_code 4 : 1 | | +----------------------------+ | | server_id 5 : 4 | | +----------------------------+ | | event_length 9 : 4 | | +----------------------------+ | | next_position 13 : 4 | | +----------------------------+ | | flags 17 : 2 | | +----------------------------+ | | extra_headers 19 : x-19 | +=====================================+ | event | fixed part x : y | | data +----------------------------+ | | variable part | +=====================================+ |
名字後面的兩個數字表示：offset : length即從第幾個位元組開始，後面多少個位元組用來存放資料
比如：timestamp(0 : 4)表示從第0個位元組開始，往後四個位元組用來存放timestamp
目前來說x=19，所有extra_headers是空的，y是fixed part的長度，不同的Event長度不一樣。
參考：https://dev.mysql.com/doc/internals/en/event-structure.html
Event簡要分析
1.從一個最簡單的例項來分析其中的Event，包括建立表，插入資料，更新資料，刪除資料；binlog_format使用的是預設的STATEMENT；
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `btest` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into btest values(1,100,'zhaohui'); update btest set name='zhaohui_new' where id=1; delete from btest where id=1; |
2.檢視所有的Events
|
1 |
show binlog events; |
上圖中一共出現了3中型別的Event，分別是Format_desc，Query和Xid，下面進行簡單的分析
2.1Format_desc_Event
官網格式如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
+=====================================+ | event | timestamp 0 : 4 | | header +----------------------------+ | | type_code 4 : 1 | = FORMAT_DESCRIPTION_EVENT = 15 | +----------------------------+ | | server_id 5 : 4 | | +----------------------------+ | | event_length 9 : 4 | >= 91 | +----------------------------+ | | next_position 13 : 4 | | +----------------------------+ | | flags 17 : 2 | +=====================================+ | event | binlog_version 19 : 2 | = 4 | data +----------------------------+ | | server_version 21 : 50 | | +----------------------------+ | | create_timestamp 71 : 4 | | +----------------------------+ | | header_length 75 : 1 | | +----------------------------+ | | post-header 76 : n | = array of n bytes, one byte per event | | lengths for all | type that the server knows about | | event types | +=====================================+ |
使用十六進位制方式開啟檔案bin-log.000001，以下是Format_desc_Event的十六進位制程式碼：
可以先看前面的4+103=107個位元組，4位元組是binlog的魔數，103位元組是Format_desc_Event，其中有19位元組是header；
注:Binlog日誌是小端位元組順序
0x5A0504AA四個位元組是timestamp；0x0F一個位元組表示type_code；0x00000001四個位元組為server_id；0x00000067四個位元組是event_length，對應的十進位制就是103；
0x0000006b四個位元組是next_position，即下一個Event的開始位置為107；ox0001兩個位元組是flags；header總計19位元組。
data總位元組數=103-19即84位元組，排除掉前面的57個位元組，剩餘27位元組表示post-header lengths for all event types；
post-header lengths：從START_EVENT_V3開始到第27個Event，每個Event的fixed part lengths；
Format_desc_Event位置是15，可以在圖中找到15的位置是0x54，對應十進位制是84，即fixed part lengths=84，而這個值剛好是57+27=84，所以Format_desc_Event不存在variable part；
參考：https://dev.mysql.com/doc/internals/en/binary-log-versions.html
2.2Query_Event
以下的create table產生的Query_Event的十六進位制程式碼：
header共19位元組，0x02一個位元組表示type_code（Query_Event=2）;0x00000101四個位元組是event_length，對應的十進位制就是257（pos=107，End_log_pos=364）;
Query_Event在post-header的第二個位置0x0d，所有fix part lengths=13；
variable part=257-19-13=225位元組
具體fix data和variable data：
|
1 2 3 4 5 6 7 8 9 10 11 |
+==============================================================+ | fix | The ID of the thread 19 : 4 | | data +-----------------------------------------------------+ | | The time in seconds 23 : 4 | | +-----------------------------------------------------+ | | The length of the name of the database 27 : 1 | | +-----------------------------------------------------+ | | The error code 28 : 2 | | +-----------------------------------------------------+ | | The length of the status variable block 30 : 2 | +==============================================================+ |
在建立表產生一個Query_Event，insert、update以及delete執行之後分別產生了2個Query_Event和一個Xid_Event。
更多詳細：https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
2.3Xid_Event
以下的更新資料產生的Xid_Event的十六進位制程式碼：
header共19位元組，0x10一個位元組表示type_code（XID_EVENT=16）;0x0000001b四個位元組是event_length，對應的十進位制就是27（pos=536，End_log_pos=563）;
2Xid_Event在post-header的第十六個位置0x00，所有fix part lengths=0；
variable part=27-19=8位元組
8位元組：The XID transaction number。
insert、update以及delete執行之後分別產生了Xid_Event，事務提交產生的事件。
更多詳細：https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
總結
本文主要對Mysql Binlog做了一個大體的介紹，包括：Binlog的引數，格式以及最重要的事件；事件數量比較多，從最簡單的增刪改查入手，介紹了幾個比較常見的事件；
後續會繼續學習其他事件，對Binlog有更加詳細的瞭解。
參考：