由 之前的文章 可以了解到，二進制日志在復制中起到舉足輕重的作用，所以這一篇文章著重了解一下Mysql復制背后核心組件：二進制日志的廬山真面目。
二進制日志的結構
從概念上講，二進制日志是一系列二進制日志事件。它包括一系列的binlog文件和一個binlog索引文件，當前服務器正在寫入的binlog文件稱之為active binlog。其文件名是通過配置文件中的log-bin和log-bin-index來定義的。
每個binlog文件是由若干binlog事件組成，以Format_description事件開始，以Rotate事件作為文件尾。
Format_description事件包含寫binlog文件的服務器信息，以及關于文件狀態的關鍵信息。如果服務器關閉或者重新啟動，會創建一個新的binlog文件，同時寫入新的Format_description事件，這個事件是必須的，因為服務器關閉和重啟都會產生更新。服務器寫完binlog文件后，在文件結尾添加一個Rotate事件，該事件包含下一個binlog文件的文件名及其開始讀取的位置。除了Format_description和Rotate事件之外，binlog文件的其他事件都被分成 group進行管理。在事務存儲引擎中，每個組大致對應一個事務，對于非事務存儲引擎，每個語句本身就是一個組。通常情況下，每個組要么全部執行，要么去不執行。如果由于某種原因Slave在組執行的過程中停機，那么將從該組的起點而不是剛剛執行的語句開始復制。
binlog事件的結構
二進制日志版本4（binlog format 4）是在MySQL 5.0中引入，是專門為擴展而設計的。這里主要討論二進制日志版本4。（MySQL 3.23 4.0 4.1版本都是使用二進制日志版本3）
每個binlog事件由三個部分組成：
- 通用頭（common header）：大小固定。事件的基本信息，其中重要的字段是事件類型和事件大小。
- 提交頭（post header）：大小固定。提交頭與特定的事件類型相關
- 事件體（Event body）：大小可變。事件體存儲事件的主要數據，因事件類型不同而異。
具體看一下Format_description事件：
- binlog文件格式版本
- 服務器版本字符串：一般包括三部分，即版本號、連字符和其他構建項。例如：5.1.42-debug-log
- 通用頭的長度：存儲了通用頭的長度。這里是指Format_description事件，所以不同binlog文件該字段的值不同。除了Format_description和Rotate事件外，其他事件的通用頭長度都是可變的。 Format_description事件 的通用頭長度是不變的，是因為任何版本的服務器都需要讀取這個事件。 Rotate事件 的通用頭長度也是不變的，是因為Slave連接Master時首先要用到該事件。
- 提交頭的長度：binlog文件中所有事件的提交頭長度是不變的，該字段存儲了各個事件的提交頭長度構成的數組。由于不同服務器間的事件數目不同，所以這個字段前面還存儲了服務器的事件數目。
通過事件來記錄數據庫變更
首先，由于二進制日志是公共資源，所有線程都向它寫入語句，為了避免兩個線程同時更新二進制日志，在寫之前需要獲得一個互斥鎖Lock_log，寫完之后再釋放。
所有涉及到數據庫更新的語句都會以Query事件的形式寫入二進制日志中，除了實際執行的語句外，Query事件還包含執行語句必需的上下文附加信息。下面給出了如何記錄這些上下文信息
- 當前數據庫：在 Query事件 添加一個特殊字段記錄當前數據庫。
- 用戶自定義變量的值： User_var事件 記錄單個用戶自定義的變量的變量名及其值。
- RAND函數的種子： Rand事件 記錄Rand函數所用的隨機數種子。
- 當前時間：NOW，CURDATE，CURTIME，UNIX_TIMESTAMP和SYSDATE這五個函數會用到當前時間，針對這個事件會存儲一個時間戳，表示事件何時開始執行。
- AUTO_INCREMENT字段的插入值： Intvar事件 記錄在語句開始前，表內部的自動增量計數器的值。
- 調用LAST_INSERTED_ID的返回值： Intvar事件 記錄這個函數在語句的返回值。
- 線程ID：主要是涉及到臨時表的處理。線程ID也是作為一個獨立的字段存儲在Query事件中。
* 對于SYSDATE函數，它返回的是函數執行時的時間，這一點不同于NOW函數，NOW返回的是語句執行的時間。所以SYSDATE對于復制來說是不安全的，盡量少用。
LOAD DATA INFILE語句
LOAD DATA INFILE比較特殊，它的上下文是文件系統的文件。要正確地傳遞和執行LOAD DATA INFILE語句，需要引入新的事件類型：
- Begin_load_query ：這個事件開始傳輸文件中的數據
- Append_block ：如果這個文件超過了連接的數據包大小所允許的最大值，那么跟隨在Begin_load_query事件后面的一個或多個Append_block事件的系列包含著這個文件的剩余部分
- Execute_load_query ：Query事件的特殊變種，它包含了在Master上執行的LOAD DATA INFILE語句
對Master上執行的每個LOAD DATA INFILE語句而言，被讀取的文件被映射到一個支持內部文件的緩沖區，并在接下來的處理流程中使用。此外，一個唯一的文件ID被分配給該執行語句，并用于指向該語句讀取的文件。
當語句在執行的時，該文件的內容被寫入二進制日志，作為以Begin_load_query事件開頭的事件序列，Begin_load_query事件表示新文件的開始，且這個事件序列后面緊跟著零個或多個Append_block事件。每個寫入二進制的事件都不會超過包大小所允許的最大值，這個最大值由max-allowed_packet選項指定。
當整個文件讀取到表中后，通過寫Execute_load_query事件到二進制日志來終止語句的執行。這個事件包含了執行語句和分配給該執行語句的文件ID。請注意，這并非是用戶寫的原始語句，而是重新創建的。
* Mysql 5.0.3之前的版本使用的事件名有點不一樣，依次為 Load_log_event，Execute_log_event，Create_file_log_event
二進制日志過濾器
my.cnf中有兩個選項可用于過濾日志：binlog-do-db和binlog-ignore-db。這兩個選項可以使用多次。
MySQL過濾事件的方式對于不熟悉的人來說可能有點奇怪。Mysql過濾是在語句級完成的，binlog-*-db使用 當前數據庫 來決定是否應該過濾該語句，而不是由語句所影響的表所在的數據庫決定的。對于下面的例子，使用binlog-ignore-db=bad篩選bad數據庫，下例中一個都不會寫入日志。
USE
bad; INSERT INTO t1
VALUES
(
1
),(
2
);
USE
bad; INSERT INTO good.t2
VALUES
(
1
),(
2
);
USE
bad; UPDATE good.t1, ugly.t2 SET a = b;
至于為什么不是以語句所影響的表所在的數據庫來決定，可以嘗試分析一下。如果以這種邏輯，使用binlog-ignore-db=ugly篩選時，第三條語句到底要不要寫入日志呢？
為了避免在執行可能被過濾的語句時發生錯誤，請不要編寫那種表名，函數名或存儲過程名前面加數據庫名的語句，而是通過使用use來改變當前數據庫。
還有一個需要說明的是，只要設置了binlog-do-db，過濾器會無視binlog-ignore-db的設置。
當然對于MySQL復制來說，本身不建議使用過濾器，因為日志是不完整的。
?
二進制日志和安全
一般來說，一個有REPLICATION SLAVE權限的用戶擁有讀取Master上發生的所有事件的權限，因此為了安全應該保護該賬戶不被損害。具體預防的措施有：
- 盡可能使從防火墻外無法登錄該賬戶
- 記錄所有試圖登錄到該賬戶的日志，并將日志放置在一個單獨的安全服務器上
- 加密Master和Slave間所用的連接，例如MySQL的built-in SLL
- 敏感信息不要放入日志文件中，比如說密碼
#
第二種做法不會把明文密碼寫入到日志中，更安全些
UPDATE employee SET pass = PASSWORD(
'
foobar
'
)
SET
@pass
= PASSWORD(
'
foobar
'
);
UPDATE employee SET pass =
@pass
觸發器
為了在服務器上重放二進制日志，毫無問題的處理各種表的權限，有必要用SUPER權限的用戶執行所有語句。但觸發器沒有被定義使用SUPER權限，所以重要的是以正確的用戶作為觸發器的定義者去重新創建觸發器。CREATE TRIGGER提供了一個DEFINER子句，如果沒有給語句指定DEFINER，該語句添加DEFINER子句后被寫到二進制日志中，且使用當前用戶作為其定義者。
master>SHOW BINLOG EVENTS FROM
92236
LIMIT
1
\G
********************
1
. row ********************
Log_name: master-bin.
000038
Pos
:
92236
Event_type: Query
Server_id:
1
End_log_pos:
92491
Info:
use
`test`; CREATE DEFINER=`root`@`localhost` TRIGGER ...
調用觸發器的語句被記錄到二進制日志，但它沒有連接到特定的觸發器。相反，當Slave執行該語句時，它會自動執行受該語句影響的表相關聯的所有觸發器，這意味著可以在Master和Slave上有不同的觸發器。
存儲過程
存儲過程的定義語句的處理和觸發器是類似的，CREATE PROCETURE語句也有可選的子語句DEFINER，寫入二進制日志的時候，會強制加上該子句的。調用過程和觸發器不一樣。
#
定義存儲過程
delimiter $$
CREATE PROCEDURE employee_add(p_name CHAR(
64
), p_email CHAR(
64
), p_password CHAR(
64
))
MODIFIES SQL DATA
BEGIN
DECLARE pass CHAR(
64
);
set pass = PASSWORD(p_pass)
INSERT INTO employee(name, email, password)
VALUES
(p_name, p_email, pass);
END $$
delimiter ;
#
調用存儲過程
master> CALL employee_add(
'
chunk
'
,
'
chuck@example.com
'
,
'
abrakadabra
'
);
master> SHOW BINLOG EVENTS FROM
104033
\G
********************
1
. row ********************
Log_name: master-bin.
000038
Pos
:
104033
Event_type: Intvar
Server_id:
1
End_log_pos:
104061
Info: INSERT_ID=
1
********************
2
. row ********************
Log_name: master-bin.
000038
Pos
:
104061
Event_type: Query
Server_id:
1
End_log_pos:
104416
Info:
use
`test`; INSERT INTO employee(name, email, password)
VALUES
(
NAME_CONST(
'
p_name
'
,_latin1
'
chuck
'
COLLATE
'
latin1_swedish_ci
'
),
NAME_CONST(
'
p_email
'
,_latin1
'
chuck@example.com
'
COLLATE
'
latin1_swedish_ci
'
),
NAME_CONST(
'
pass
'
,_latin1
'
*FEB778934FDSFQOPL7...
'
COLLATE
'
latin1_swedish_ci
'
))
有四點需要注意：
- CALL語句沒有被寫入二進制日志。取而代之的是，執行語句作為調用的結果被寫入二進制日志。
- 該語句改寫為不包含任何對存儲過程的參數的引用。取而代之的是，使用NAME_CONST函數為每個參數創建一個單值的結果集
- 局部聲明的變量pass也被換成了NAME_CONST表達式
- 調用語句寫入二進制日志之前，上下文信息已經寫入日志，這里指Intvar事件
存儲函數
存儲過程的定義語句的處理和觸發器是類似的，CREATE FUNCTION語句也有可選的子語句DEFINER，寫入二進制日志的時候，會強制加上該子句的。調用的時候，存儲函數以與觸發器相同的方式被復制。有一點需要注意的就是，SELECT語句不會被寫入二進制日志，但是一個含有存儲函數的SELECT語句是個例外。
對于存儲函數還有一個需要提到的是權限問題。CREATE ROUTINE權限是定義一個存儲過程或存儲函數所必需的。嚴格說創建一個存儲程序不需要其他權限，但它通常根據定義者的權限執行。在Slave上的復制線程在不進行權限檢查的情況下執行，這留下了嚴重的安全漏洞。MySQL 5.0之前的版本沒有存儲程序，這樣不會有問題，因為在Master上違規的語句不會寫到二進制日志中。由于存儲過程被展開了，只有在Master上成功執行的語句才會寫進二進制日志，所以也不會有問題。而存儲函數有點不同，它并沒有被展開，也就是說有可能在Master和Slave上執行不同的程序分支，帶來潛在安全漏洞。在存儲函數定義時使用SQL SECURITY DEFINER而不是SQL SECURITY INVOKER可以防止這一點。因為這一點的考慮，MySQL默認要求SUPER權限來定義存儲函數。
Events
定義跟其他存儲程序一樣，也會有DEFINER子句。由于事件由事件調度器調用，因此它們總是以定義者執行從而不會存在存儲函數的安全漏洞。當事件被執行時，該語句被直接寫入二進制日志。由于事件是在Master上執行的，他們在Slave上是自動禁止的。但有時候如果需要升級Slave，就需要允許在Slave上執行這些事件。
UPDATE mysql.events SET status = ENABLED WHERE status = SLAVESIDE_DISABLED;
特殊結構
盡管基于語句的復制通常是簡單的，但一些特殊結構必須小心處理，才能很好的來保證Slave執行語句時的上下文跟Master上執行時是一樣的。
?
LOAD_FILE函數
LOAD_FILE函數讓你可以獲取一個文件，由于在復制過程中，它不會被傳輸，所以需要改寫。
INSERT INTO document(author, body)
VALUES
(
'
Fox
'
, LOAD_FILR(
'
index.html
'
));
#
可以用LOAD DATA FILE改寫
LOAD DATA INFILE
'
index.html
'
INTO TABLE document FIELDS TERMINATED BY
'
@*@
'
LINES TERMINATED BY
'
&%&
'
(author, body) SET author =
'
FOX
'
;
#
還可以用用戶定義變量改寫
SET
@document
= LOAD_FILE(
'
index.html
'
);
INSERT INTO document(author, body)
VALUES
(
'
Fox
'
,
@document
);
?
非事務性的變化和錯誤處理
如果有一個employee表是支持事務的InnoDB存儲引擎(主鍵是mail)，而跟蹤employee修改的log表是不支持事務的MyISAM存儲引擎。在其上定義兩個觸發器，一個在INSERT之前觸發tr_insert_before，插入一條記錄到log表，插入紀錄的狀態為FAIL；一個在INSERT之后觸發tr_insert_after，更改剛才插入紀錄的狀態為OK。連續插入兩條完全相同記錄時，tr_insert_before被觸發，tr_insert_after則不會被觸發。雖然employee失敗回滾了，但是log里面插入的數據卻沒辦法回滾，這是個問題。執行后二進制日志文件內容如下。
master> SET
@pass
= PASSWORD(
'
xyz
'
);
master> INSERT INTO employee (name, mail, password)
VALUES
(
'
hu
'
,
'
hu@fox.com
'
,
@pass
);
master> INSERT INTO employee (name, mail, password)
VALUES
(
'
hu
'
,
'
hu@fox.com
'
,
@pass
);
master> SHOW BINLOG EVENTS IN
'
local-bin.000023
'
********************
1
. row ********************
Log_name: master-bin.
000023
Pos
:
1252
Event_type: Query
Server_id:
1
End_log_pos:
1320
Info:
use
'
test
'
; BEGIN
********************
2
. row ********************
Log_name: master-bin.
000023
Pos
:
1320
Event_type: Intvar
Server_id:
1
End_log_pos:
1348
Info: LAST_INSERT_ID=
1
********************
3
. row ********************
Log_name: master-bin.
000023
Pos
:
1348
Event_type: User var
Server_id:
1
End_log_pos:
1426
Info: @
'
pass
'
=_utf 0x432423jklfslagklr... COLLATE utf8_general_ci
********************
4
. row ********************
Log_name: master-bin.
000023
Pos
:
1426
Event_type: Query
Server_id:
1
End_log_pos:
1567
Info:
use
'
test
'
; INSERT INTO employee ...
********************
5
. row ********************
Log_name: master-bin.
000023
Pos
:
1567
Event_type: Xid
Server_id:
1
End_log_pos:
1594
Info: COMMIT /* xid=
60
*/
********************
6
. row ********************
Log_name: master-bin.
000023
Pos
:
1594
Event_type: Query
Server_id:
1
End_log_pos:
1662
Info:
use
'
test
'
; BEGIN
********************
7
. row ********************
Log_name: master-bin.
000023
Pos
:
1662
Event_type: Intvar
Server_id:
1
End_log_pos:
1690
Info: LAST_INSERT_ID=
1
********************
8
. row ********************
Log_name: master-bin.
000023
Pos
:
1690
Event_type: User var
Server_id:
1
End_log_pos:
1768
Info: @
'
pass
'
=_utf 0x432423jklfslagklr... COLLATE utf8_general_ci
********************
9
. row ********************
Log_name: master-bin.
000023
Pos
:
1768
Event_type: Query
Server_id:
1
End_log_pos:
1909
Info:
use
'
test
'
; INSERT INTO employee ...
********************
10
. row ********************
Log_name: master-bin.
000023
Pos
:
1909
Event_type: Query
Server_id:
1
End_log_pos:
1980
Info:
use
'
test
'
; ROLLBACK
?
事務
由上面的二進制日志內容可以看到，執行事務的時候需要額外的處理。對于事務來說，為了使得每個事務的所有語句在一起，不是按照事務的開始順序而是提交順序記入二進制日志。為了確保每個事務都作為一個單元被寫入二進制日志，服務器需要將在不同線程中執行的語句分開，保存在一個事務緩存中，在事務提交的時候緩存被清空，同時事務緩存的內容被復制到二進制日志中。
那如何記錄非事務性的語句呢？有這么三條規則可以使用：
- 如果語句被標記成事務的，它將被寫入事務緩存
- 如果語句沒有被標記成事務性的，而且事務緩存中沒有語句，該語句將被直接寫入二進制日志
- 如果語句沒有被標記成事務性的，但是事務緩存中已有語句，該語句被寫入事務緩存
使用XA進行分布式事務處理
- 第一階段，每個存儲引擎被要求為提交做準備。在準備時，存儲引擎將它需要正確提交的一切信息寫入到安全的存儲器，然后返回一個OK消息。如果有一個存儲引擎的回答是否定的，則意味著它不能提交這個事務，提交被終止，而且所有的引擎都被通知回滾事務。
- 在所有的存儲引擎都返回OK的時候， 在第二階段開始之前，事務緩存被寫入二進制日志 。普通事務以帶有COMMIT的普通查詢事件結束，與此同時，XA事務則以一個包含XID的Xid事件結束。
二進制日志管理
到目前為止，所提到的事件都是Master上的數據的改動。有一些事件雖然不是代表在Master上修改數據，但它們卻會影響復制。比如在服務器停止的期間修改了數據文件之類，為了應對這些問題，也需要額外類型的事件。
二進制日志和系統崩潰安全
在數據庫崩潰的時候，保持數據庫和二進制日志相互一致性非常重要。換句話說，如果沒有寫入二進制日志，那么就應該沒有更改被提交到存儲引擎，反之亦然。
但對于非事務性引擎則有問題。例如，不可能保證二進制日志和MyISAM表之間的一致性，因為MyISAM是非事務性的，且MyISAM在試圖記錄語句之前就完成了修改。對于事務性存儲引擎則不一樣。正如前面所講，事件被寫入二進制日志是在釋放所有表鎖之前，所有改變傳輸到各個存儲引擎之后的。如果在存儲引擎釋放鎖之前系統宕機了，服務器在允許事務提交之前一定要確認寫進二進制日志的改變已經寫進實際表中，而這是需要和標準文件系統同步進行協調。
回憶一下XA，為了能安全應對宕機，當第一階段完成的時候，所有的數據都應該已經寫到了磁盤。這就意味著每次一個事務完成，系統頁緩存(page cache)就必須寫到磁盤，這種想法的代價很高，而且很多應用并不必須這樣。可以通過sync-binlog選項來控制數據寫磁盤的頻率，默認為0，也就是不寫磁盤的調度完全交給操作系統；設置n，表示每n次事務提交就寫一次磁盤。
?
binlog文件輪換（binlog file rotate）?
MySQL隔一段時間就會啟用一個新文件來保存二進制日志事件。把文件切換稱之為binlog file rotate。
主要有四種操作會導致文件輪換：
- 服務器停止：每次服務啟動都會啟用一個新的二進制日志文件。
- binlog文件大小達到最大值：這個值可以通過binlog-cache-size參數控制。
- 顯式刷新：FLUSH LOGS
- 服務器發生事故：有些事故需要特殊的人工干預，這都會在復制流程上形成一個"缺口"
- binlog-in-use標志位：服務器在寫二進制日志時有可能發生宕機，因此需要知道一個文件是否被正確的關閉。而且，如果一個文件本身損壞了，用它進行恢復會產生更多的問題。binlog-in-use就是用于標識一個文件的完整性，它在文件創建的時候被設置，在Rotate事件被寫入文件后被清除。
- 二進制日志文件格式版本號：
- 服務器版本：
Incidents
?所謂incident事件是指那些在服務器上沒有產生數據改變但卻必須要寫進二進制日志的事件，因為它們有可能影響到復制。大多數這種事件并不需要DBA干預，比如數據庫的重啟等。
- ?stop：這是一種表示服務器正常關機的事件。如果服務器宕機，就不會有stop事件。這個事件會在舊的二進制日志文件里，因為重啟會啟用新文件。該事件僅僅包含一個通用頭。當二進制日志在Slave上重放的時候，所有Stop事件都會被忽略。那這種事件有什么用呢，因為重啟復制前可能手動恢復一個備份或者修改了文件，這時候DBA在重放該日志文件的時候，可以找到該事件從而知道在哪里開始或者停止重放。
- Incident：該事件類型是在MySQL 5.1版本引入的。和Stop事件相比，該事件包含一個標志符來指定發生了哪種類型事故。它一般用來表示服務器被強制執行某個不被記入二進制日志的變更。比如，數據庫重新加載，某個非事務性事件太大而無法寫入二進制日志。MySQL Cluster在其中一個節點重新加載數據庫而因此不同步時也會產生該事件。當二進制日志在Slave上重放的時候，碰到Incident事件的時候將會停止復制。
刪除二進制文件
?有幾種方式可以刪除二進制文件：
1
：設置my.cnf的expire-logs-days參數
2
：PURGE BINARY LOGS BEFORE datetime;
3
：PURGE BINARY LOGS TO
'
filename
'
;
刪除二進制文件的機制：
開始刪除文件之前，服務器會把要刪除的文件列表寫到一個臨時文件（purge index file），然后才開始刪除文件，最后刪除該臨時文件。這樣即使在刪除日志文件過程中系統宕機也能在服務器再啟動時，繼續刪除未刪除的文件。在前面講到，purge index file也用于文件rotate的時候。
mysqlbinlog是一個可以查看binlog日志文件和relay日志文件內容的小程序。用mysqlbinlog工具來查看二進制日志內容的輸出是可以直接在服務器上執行的。該命令是分析日志的一個利器，可以查看所有日志的語句內容和事件內容，因此經常用于查錯。該命令的具體使用方法 參照官方文檔 。注意可以用使用--hexdump選項來查看二進制日志，不過需要了解一下 日志的數據格式 。比如二進制日志的整數字段是以little-Endian順序打印出來的，所以你必須從右往左讀。32位的block 03 01 00 00表示16進制的103。
?
?
?
---待續
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
