第3章　復制格式詳解

通過前面的兩章，我們了解了復制技術的使用場景及其基本原理與實現，知道復制是通過二進制日志記錄在主從庫之間的流轉來實現的。第1章對二進制日志的記錄格式做了簡要的介紹，本章將詳細闡述復制格式。

3.1　復制格式概述

復制功能之所以能夠正常工作，是因為寫入二進制日志的事件是從主庫讀取，然后在從庫上回放的。根據事件的類型，事件以不同的格式被記錄在二進制日志中。復制格式由系統變量binlog_format控制（主要針對DML語句生效）。根據主庫中記錄的二進制日志格式以及系統變量binlog_format的不同值，在配置使用時可以將復制劃分為如下幾種格式（在第1章中有所提及，詳見1.4節“復制格式”）：

? 使用statement格式的二進制日志時，主庫會將SQL語句文本寫入二進制日志。在從庫上執行SQL語句，然后將主庫的數據變更應用到從庫中，這稱為基于statement（語句）的復制，簡稱為SBR。

? 使用row格式的二進制日志時，主庫會將產生的事件（一組事件）寫入二進制日志，以事件來表示數據的變更。將這些表示數據變更的事件復制到從庫，然后在從庫中應用這些事件，把主庫數據同步到從庫，這稱為基于row（行）的復制，簡稱為RBR。

? 還可以使用statement和row的混合（mixed）格式的二進制日志，具體為statement還是row格式，由記錄的內容決定。默認使用的是statement格式，根據語句以及使用的存儲引擎，在特殊情況下會自動切換到row格式。這種使用mixed格式二進制日志的復制，簡稱為MBR。

在MySQL 5.7.7之前，默認的二進制日志采用statement格式。在MySQL 5.7.7及更高的版本中，默認的二進制日志變更為row格式。MySQL NDB Cluster 7.5中默認的二進制日志為mixed格式。要注意，MySQL NDB Cluster的復制始終使用基于row的格式，NDB存儲引擎與基于statement的復制不兼容。

通過系統變量binlog_format來控制二進制日志的格式時，可以在會話（session）或全局（global）級別動態修改其值。在會話級別修改時，修改的值只對當前會話生效，會話斷開即失效，而且修改的值對其他會話不可見；在全局級別修改時，修改的值對修改之后新建立的所有客戶端連接生效，對之前已建立的客戶端連接不生效（包括執行全局級別修改操作的連接本身）。動態修改的值在數據庫進程重啟后會丟失，如果要對這個值進行持久化，就需要在配置文件中進行設置。

注意：在某些情況下不能動態修改二進制日志格式，否則容易導致復制失敗。例如，事務內不允許修改會話級別的二進制日志格式。

要修改系統變量binlog_format在全局級別和會話級別的值，用戶必須擁有SUPER權限。通常，對于大部分會話而言，修改系統變量的值不需要用戶具有SUPER權限，但在某些會話中修改它們可能會在會話之外產生影響（例如，系統變量binlog_format、sql_log_bin和sql_log_off），因此用戶需要擁有SUPER權限。

基于statement和基于row的復制各自有不同的問題和限制。有關它們的優缺點對比詳見3.2節。

使用基于statement的復制，可能會遇到復制存儲過程或觸發器的問題（在主從數據庫上各自執行這些語句會導致主從庫的數據不一致），可以通過使用基于row的復制來避免這些問題。更多信息詳見3.2節。

3.2　復制格式明細

3.2.1　基于statement和基于row的復制的優缺點

每一種二進制日志格式都有優點和缺點。對于大多數用戶而言，混合復制是兼具數據完整性和較高性能的最佳選擇。但是，執行某些任務時，需要根據實際情況來選擇使用statement或者row二進制日志格式，本節對不同復制格式的優缺點進行對比，供讀者在決策時參考。

1. 基于statement的復制的優點

? 技術成熟。

? 寫入日志文件的數據較少。當更新或刪除操作涉及多行時，可以大大減少存儲空間，在利用二進制日志備份與恢復數據時也可以快速完成。

? 日志文件中包含所有的數據變更的原始語句，可用于數據庫審計。

2. 基于statement的復制的缺點

? 一些執行結果不確定的DML語句，不能使用基于statement的復制，否則可能會造成主從庫的數據不一致。

? UDF（用戶自定義函數，即用戶創建的函數）和存儲過程執行的結果也不確定，因為具體的返回值受傳入的參數值的影響。

? 在DML語句中，使用不帶ORDER BY的LIMIT子句時，由于在主從庫之間執行的排序結果可能不同，所以執行結果是不確定的（如果使用混合復制，會自動使用row格式記錄執行DML語句后對數據所做的變更，而不是記錄DML語句本身）。

? 使用statement格式的日志時，一些內置的函數無法正確復制，如下：

? LOAD_FILE()

? UUID()、UUID_SHORT()

? USER()

? FOUND_ROWS()

? SYSDATE()（主庫和從庫都使用--sysdate-is-now選項啟動時適用）

? GET_LOCK()

? IS_FREE_LOCK()

? IS_USED_LOCK()

? MASTER_POS_WAIT()

? RAND()

? RELEASE_LOCK()

? SLEEP()

? VERSION()

注意：無法正確復制不一定就不允許執行，在READ-COMMITTED（讀提交）隔離級別下，某些函數不允許執行，而在REPEATABLE-READ（可重復讀）隔離級別下卻允許執行，但不一定保證能夠正確復制。凡是在執行語句時產生了警告信息的，都需要留意。

? INSERT INTO ... SELECT語句在基于statement的復制中需要的行級鎖比基于row的復制多。

? 未使用索引的UPDATE語句需要進行表掃描，基于statement的復制可能比基于row的復制鎖定的行數更多。

? 對于復雜語句，必須在主從庫之間先評估數據的一致性（DML語句），基于row的復制則不存在這個風險，因為主庫的二進制日志只記錄發生數據變更的行，而從庫執行這些二進制日志時也只會執行發生數據變更的行，而不是執行實際的復雜語句本身。

提示：

使用基于statement的復制時：

? 從MySQL 5.7開始，類似NOW()的函數（這里指的是一些從系統變量timestamp獲取時間值的函數）可以正確地在主從庫之間進行復制（對于這些時間函數，在MySQL 5.6及其之前的版本在REPEATABLE-READ隔離級別下也是允許執行的，但在READ-COMMITTED隔離級別下基本不允許執行）。因為二進制日志中每個Query_ log_event（一個事件類型）都會記錄時間戳（例如，SET TIMESTAMP=1555828207/!/;），所以對于使用時間戳的一些函數，可以在二進制日志中直接記錄SQL語句文本，而且可以確保主從庫的一致性。

? 如果碰到無法正確復制的語句，在REPEATABLE-READ隔離級別下將發出警告信息，并正常執行語句，但在READ-COMMITTED和READ-UNCOMMITTED（讀未提交）隔離級別下不允許執行。例如警告信息：“Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.”，可以使用SHOW WARNINGS語句查看。

3. 基于row的復制的優點

? 可以正確復制所有數據的變更，這是最安全的復制格式。

注意：會更新MySQL系統庫數據的GRANT、REVOKE、TRIGGER、PROCEDURE、VIEW等操作，都是使用statement格式復制到從庫的。而CREATE TABLE ... SELECT之類的語句的復制，會被拆分為兩個步驟：建表操作使用statement格式的日志記錄；涉及數據插入操作時，會使用row格式的日志記錄。但GTID復制模式不允許執行CREATE TABLE ... SELECT語句，因為兩步操作會導致產生兩個不同的GTID（在GTID機制下，二進制日志中的每一個操作都會生成一個單獨的GTID）。從邏輯上來說，這顯然是不合理的，所以啟用GTID之后，GTID的使用限制中有不允許執行該語句這一條。

? 對于以下類型的語句，從庫需要的行鎖更少，實現了更高的并發性：

? INSERT INTO ... SELECT

? 使用了AUTO_INCREMENT（自增字段）的INSERT語句（這里指的是INSERT語句在對定義了自增字段的表執行插入數據時，不指定自增字段名和自增字段值，讓其自動分配）。

? 在UPDATE或DELETE語句中，WHERE條件字段未使用索引時，可能導致全表掃描，但大多數被掃描的行實際上都不會被修改，只有滿足WHERE條件值的行才會真正被修改。采用statement格式的二進制日志中記錄的是原始SQL語句，這時如果該語句無法使用索引，則會掃描并鎖定全表的所有數據；而如果采用row格式，則二進制日志中記錄的是逐行數據變更，從庫在回放這些二進制日志時也逐行回放，不會鎖住所有行。這得益于從MySQL 5.6開始引入的一個新特性：在row格式下，如果表存在主鍵或唯一索引，那么可以通過特殊的優化算法找到能夠唯一標志行的主鍵值或唯一索引值，從而避免對不需要修改的行加鎖。查找算法由系統變量slave_rows_search_algorithms進行設置。關于該特性，可參考高鵬的“復制”專欄，登錄簡書網站搜索“第24節：從庫數據查找和參數slave_ rows_search_algorithms”。

提示：綜上所述，對于任何INSERT、UPDATE或DELETE語句，從庫需要的行鎖可能都會更少。

4. 基于row的復制的缺點

? 生成更多的二進制日志數據，因為基于row的復制會將每行數據的變更都寫入二進制日志。利用二進制日志進行備份和恢復的時間也會更長。此外，二進制日志的文件鎖也會因為需要更長的時間來寫入數據而被持有更久的時間，這可能會影響數據庫的并發能力。可以使用系統變量binlog_row_image = minimal來減少二進制日志的寫入量。

? 如果要生成大字段的BLOB值，使用基于row的復制比使用基于statement的復制耗費的時間更長，因為前者記錄了BLOB字段的具體值，而不是生成數據的語句。

? 無法直接看到從庫中執行的語句，但是可以使用mysqlbinlog工具的--base64-output= decode-rows和--verbose選項進行查看，或者在主庫中啟用系統變量binlog_rows_ query_log_events，它會在二進制日志中寫入一個Rows_query_log_event類型的事件來記錄原始的語句文本，可以使用mysqlbinlog工具的-vv選項來查看。

? 對于使用MyISAM存儲引擎的表，當INSERT語句操作多行數據，在從庫中重放該INSERT語句時，可能需要更多的表級鎖，即在基于row的復制中，MyISAM引擎的并發性能會受到很大影響。

3.2.2　使用row格式的二進制日志進行復制

所使用的二進制日志格式不同，在二進制日志文件中記錄的日志量及其寫入時間也各不相同。在實際使用場景中，需要根據應用程序和環境來選擇。

在基于row的復制中，不會復制臨時表，臨時表只能被創建臨時表的線程訪問，因此沒有必要記錄到二進制日志中。但如果使用基于statement的復制，則二進制日志中會記錄對臨時表的操作語句，而實際上把它們記錄到二進制日志中也沒有什么用處。

注意：從MySQL 5.7.25開始，MySQL會跟蹤創建每個臨時表時生效的二進制日志格式。如果是statement格式，則當客戶端會話連接斷開時，會記錄DROP TEMPORARY TABLE IF EXISTS語句；如果是row格式，則不會記錄該語句。在之前的版本中，無論二進制日志被設置為何種格式，當客戶端連接斷開時都會在二進制日志中記錄DROP TEMPORARY TABLE IF EXISTS語句，以確保主從庫都會刪除該臨時表。

在基于row的復制中，當修改的行數較多時，可能會將行數據的變更拆分到多個事件中，因此在主庫中的非事務表修改多行，在從庫中進行重放時會更頻繁地持有表級鎖。如果是不同的表，可能一定程度上能增加并發性；如果是相同的表，則不能增加并發性。

由于基于row的復制是將行數據的變更都記錄到二進制日志中，因此日志量可能會迅速增加，而且在從庫中進行重放時可能需要更長的時間，所以當應用程序訪問從庫時，應用開發人員需要清楚訪問從庫可能出現數據延遲的情況。

通過mysqlbinlog工具解析二進制日志，可以看到其中使用了BINLOG語句（這里指的是二進制日志文件中用于記錄Base64編碼的BINLOG語句）來顯示row格式的行數據變更信息。此語句將事件內容顯示為不容易讀懂的Base64編碼字符串，可以結合使用mysqlbinlog工具的--base64-output=decode-rows和--verbose選項，將這個字符串解析為更適合人閱讀的格式，以便更容易使用二進制日志來恢復誤刪除的數據或從故障中恢復。

當系統變量slave_exec_mode設置為IDEMPOTENT時，通常僅對MySQL NDB Cluster復制有用（在NDB存儲引擎中，該系統變量的默認值為IDEMPOTENT，使用其他引擎時，其默認值為STRICT）。如果slave_exec_mode設置為IDEMPOTENT，當找不到行記錄或者主鍵沖突時，會自動跳過發生錯誤的事件，這就意味著從庫上最終并沒有應用這些發生錯誤的事件數據，主從庫之間的數據一致性會被破壞。

不能在查詢語句中使用Server ID來過濾復制內容（這里指的是在DML語句的WHERE條件值中使用@@server_id系統變量），因為在row格式的二進制日志中會將其轉換為具體的值記錄下來，而不是記錄@@server_id變量字符串。

如果需要使用Server ID來過濾復制內容，可以在從庫中配置復制時，使用CHANGE MASTER TO語句的IGNORE_SERVER_IDS選項指定需要過濾的Server ID（對于row格式和statement格式的二進制日志的復制過濾都支持此方法）。不建議在DML語句中使用包含WHERE @@server_id <> id_value的子句來過濾復制內容，例如，WHERE @@server_id <> 1，因為對于row格式的二進制日志，這樣的語句不能正常進行復制過濾。如果確實需要在語句中使用系統變量server_id過濾語句，請使用statement格式的二進制日志。

關于數據庫級別的復制選項，在row格式和statement格式的二進制日志中，--replicate-do-db、--replicate-ignore-db和--replicate-rewrite-db選項的效果差別很大。通常情況下，不建議使用數據庫級的復制選項，而應該用表級復制選項，例如，--replicate-do-table和--replicate-ignore-table。

使用row格式的二進制日志時，如果從庫在更新非事務性表時停止了復制線程，則從庫中可能發生數據不一致（因為非事務表數據無法回滾）。因此，建議在使用基于row的復制時，所有表都使用事務存儲引擎（例如InnoDB）。另外，無論使用何種格式的復制和存儲引擎，建議在關閉從庫MySQL進程之前，使用STOP SLAVE或STOP SLAVE SQL_THREAD先停止復制線程，因為這樣能避免復制時出現的一些問題。

3.3　如何確定與記錄復制中的安全和不安全語句

MySQL復制中語句是否“安全”是指是否可以使用基于statement的格式（這里指的是在二進制日志文件中實際記錄的內容為statement格式，不是指設置系統變量binlog_format = statement）正確復制語句，如果能正確復制，則認為語句是安全的，否則就認為是不安全的。

? 某些執行結果不確定的函數被視為不安全（詳見下文）。

? 使用浮點數的函數（執行結果與硬件相關）的語句被認為不安全。

根據語句是否被認為是安全的，以及二進制日志的格式（即系統變量binlog_format的當前值），對語句有不同的處理方式。

? 使用row格式的日志時，對安全和不安全語句的處理沒有區別。

? 使用mixed格式的日志時，被視為不安全的語句在記錄到二進制日志時會自動轉換為row格式，被視為安全的語句在記錄到二進制日志時會使用statement格式。

? 使用statement格式的日志時，對標記為不安全的語句會生成警告，甚至拒絕執行，被標記安全的語句則被正常記錄。

每個被標記為不安全的語句，MySQL都會生成一個警告。在早期版本中，如果在主庫上執行大量不安全的語句（這里指的是會觸發警告的語句），可能會導致錯誤日志文件過大。為了防止這種情況的發生，MySQL 5.5.27及其之后的5.5發行版、MySQL 5.6.7及其之后的5.6發行版、MySQL 5.7及其以上發行版提供了一種警告抑制機制：在任意50秒的時間段內，當ER_BINLOG_UNSAFE_STATEMENT發出超過50次警告時，就會啟用警告抑制。

當某種警告達到50次時，在最后S秒內重復N次的最后一個警告將被寫入錯誤日志中，即一旦觸發警告抑制機制，則對于50秒內超過50次的警告，只將最后一次記錄到錯誤日志中。如果警告持續保持該頻率，則警告抑制持續有效，一旦警告頻率低于此閾值，則所有的警告信息將都正常記錄到錯誤日志中。警告抑制不會影響確定語句的安全性，也不會影響向客戶端發送警告信息，MySQL客戶端仍然會收到所有的警告信息。

在statement格式的日志中，包含某些函數的語句被認為是不安全的，因為在主從數據庫中執行的結果可能不相同，在READ-UNCOMMITTED和READ-COMMITTED隔離級別下不允許執行，REPEATABLE-READ或SERIALIZABLE（串行）隔離級別允許執行，但是會收到警告信息。這些函數包括：FOUND_ROWS()、GET_LOCK()、IS_FREE_LOCK()、IS_ USED_LOCK()、LOAD_FILE()、MASTER_POS_WAIT()、PASSWORD()、RAND()、RELEASE_LOCK()、ROW_COUNT()、SESSION_USER()、SLEEP()、SYSDATE()、SYSTEM_ USER()、USER()、UUID()、UUID_SHORT()。

以下一些函數雖然執行結果也不確定，但是它們被視為安全的（實際上這些函數中除了使用系統變量timestamp獲取時間戳的時間函數之外，其他大多數函數在主從數據庫中的執行結果并不一致，在READ-UNCOMMITTED和READ-COMMITTED隔離級別下這些函數都不允許執行，在REPEATABLE-READ或SERIALIZABLE隔離級別下允許執行，但不會收到警告信息，使用這些函數時需要留意主從數據的一致性）。這些函數包括：CONNECTION_ID()、CURDATE()、CURRENT_DATE()、CURRENT_TIME()、CURRENT_ TIMESTAMP()、CURTIME()、LAST_INSERT_ID()、LOCALTIME()、LOCALTIMESTAMP()、NOW()、UNIX_TIMESTAMP()、UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP()。

執行語句中如果有對系統變量的引用，使用statement格式的日志時，將無法正確復制大多數的系統變量。

對于UDF，由于無法控制UDF的作用，因此必須假設在UDF中執行的語句是不安全的。

Fulltext plugin（全文索引插件）在不同的MySQL Server上的行為可能不同，在不同的語句中執行結果也可能不相同，因此，跟Fulltext plugin相關的所有語句都被視為不安全。

使用觸發器或存儲程序UPDATE一個具有AUTO_INCREMENT字段的表時，被認為不安全，因為更新行的順序在主從數據庫中可能不相同。另外，如果一個表具有復合主鍵，且復合主鍵包含一個AUTO_INCREMENT字段，而該字段又不是這個復合主鍵的第一字段時，那么對該表的INSERT操作也被認為不安全。

INSERT INTO ... ON DUPLICATE KEY UPDATE語句在具有多個唯一約束（主鍵 + 唯一索引 = 多個唯一約束）的表中執行時，被認為不安全。因為它對存儲引擎檢查索引鍵的順序很敏感，MySQL Server更新行的選擇依賴于唯一索引的檢查順序，而該順序是不確定的，所以在基于statement的復制中，該語句也會被標記為不安全。

使用LIMIT子句執行UPDATE操作時，未使用ORDER BY來指定檢索行的順序的語句，被視為不安全。

主從庫之間的系統日志表的內容可能不相同，當訪問或引用日志表時，可能返回不同的結果。

在同一個事務中，混合事務引擎與非事務引擎的讀/寫操作被認為不安全。

在事務中，對內部使用的一些記錄表的所有讀/寫操作都被認為是不安全的。

LOAD DATA被視為不安全，當binlog_format = mixed時，語句將以row格式記錄。要注意：當binlog_format = statement時（而且是REPEATABLE-READ或者SERIALIZABLE隔離級別），LOAD DATA不會生成警告，這一點與其他不安全的語句不同。

如果在主庫上并行提交的兩個XA（一種分布式事務的協議名稱）事務在從庫上按相反的順序執行，那么基于statement的復制可能會發生不安全的鎖依賴關系，這可能導致從庫發生死鎖，進而導致復制失敗。

? 當設置binlog_format = statement時，XA事務中的DML語句被標記為不安全，并生成警告。

? 當設置binlog_format = mixed或binlog_format = row時，XA事務中的DML語句將使用row格式的日志記錄，不存在該問題。

提示：

? 更多關于二進制日志的內容，可參考第26章“二進制日志文件的基本組成”。

? 更多關于隔離級別的內容，可參考《千金良方：MySQL性能優化金字塔法則》的第19章“事務概念基礎”。