程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【mysql】關於binlog格式，mysqlbinlog

【mysql】關於binlog格式，mysqlbinlog

編輯：MySQL綜合教程

【mysql】關於binlog格式，mysqlbinlog

寫在前面的話

1、推薦用mixed，默認使用statement，基於上下文 set session/global binlog_format=mixed;

2、二進制日記錄了數據庫執行更改的操作，如Insert，Update，Delete等。不包括Select等不影響數據庫記錄的操作

3、MySQL記錄的日志有三種模式：STATEMENT、ROW、MIXED

4、二進制主要的功能有：復制（Replication）和恢復（Recovery）

5、ROW與STATEMENT不同之處主要在於，服務器負載/一致性兩個方面

復制的歷史

mysql-3.2 開始支持基於命令的復制，也就是statement-based replication。
mysql-5.1 開始支持基於行的復制和混合復制，也就是row-based replication和mixed-based relication。
mysql-5.5 開始支持semi-synchronous的復制，也叫半同步復制，目的在於事務環境下保持主從一致。
mysql-5.6 開始支持並行復制，但是其並行只是基於schema的
mysql-5.7 開始支持基於組提交的並行復制

復制的基本過程如下：

（1）Slave上面的IO進程連接上Master，並請求從指定日志文件的指定位置（或者從最開始的日志）之後的日志內容
（2）Master接收到來自Slave的IO進程的請求後，通過負責復制的IO進程根據請求信息讀取制定日志指定位置之後的日志信息，返回給Slave 的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經到Master端的bin-log文件的名稱以及bin-log的位置
（3）Slave的IO進程接收到信息後，將接收到的日志內容依次添加到Slave端的relay-log文件的最末端，並將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中
（4）Slave的Sql進程檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，並在自身執行

redolog 和 undolog

undolog 實現原子性。每當操作數據前，首先將數據備份到一個地方（這個存儲數據備份的地方稱為undolog）。然後再修改數據。如果出現了錯誤或者用戶執行了rollback語句，可以利用undolog中的備份將數據恢復到事務開始之前的狀態。

redolog 實現持久性。每當操作數據前，將數據真正更改時，先前相關操作寫入重做日志。這樣當斷電，或者一些意外，導致後續任務無法完成時，系統恢復後，可以繼續完成這些更改執行這些操作來恢復數據。比如某一時刻數據庫down機了，有兩個事務，一個事務已經提交，另一個事務正在處理數據庫重啟的時候就要根據日志進行前滾及回退，把已提交事務的更改寫到數據文件，未提交事務的更改恢復到事務開始前的狀態。

Row based replication-RBR

日志中會記錄成每一行數據被修改的形式，然後在slave端再對相同的數據進行回放，和其他大多數數據庫系統的復制技能一樣

優點：bin-log中可以不記錄執行的sql語句的上下文相關的信息，僅僅只需要記錄那一條記錄被修改了，修改成什麼樣了。所以row level的日志內容會非常清楚的記錄下每一行數據修改的細節

1、任何情況都可以被復制，這對復制來說是最安全可靠的，不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題 2、多數情況下，從服務器上的表如果有主鍵的話，復制就會快了很多，執行 INSERT，UPDATE，DELETE 語句時鎖更少，復制以下幾種語句時的行鎖更少

INSERT ... SELECT
包含 AUTO_INCREMENT 字段的 INSERT
沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

缺點：所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容

比如有這樣一條update語句：update product set owner_member_id = 100 where owner_member_id = 1 ，執行之後，日志中記錄的不是這條update語句所對應額事件(MySQL以事件的形式來記錄bin-log日志)，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。

執行alter table之類的語句的時候，產生的日志量是驚人的。因為MySQL對於alter table之類的表結構變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表。那麼該表的每一條記錄都會被記錄到日志中

binlog 大了很多，復雜的回滾時 binlog 中會包含大量的數據
執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，這會導致頻繁發生 binlog 的並發寫
UDF 產生的大 BLOB 值會導致復制變慢

注：采用 RBR 模式後，能處理很多原先出現的主鍵重復問題

Statement based replication-SBR

每一條會修改數據的sql都會記錄到 master的bin-log中，而且記錄每條語句在執行的時候的一些相關信息，也就是上下文信息

優點：不需要記錄每一行數據的變化，減少bin-log日志量，節約IO，提高性能。主從版本可以不一樣，從服務器版本可以比主服務器版本高

缺點：由於記錄的執行語句，所以為了讓這些語句在slave端也能正確執行，那麼他還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息

另外就是，很多的新功能不斷的加入，使MySQL得復制遇到了不小的挑戰。目前已經發現的就有不少情況會造成MySQL的復制出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep() 函數在有些版本中就不能真確復制，在存儲過程中使用了last_insert_id()函數，可能會使slave和master上得到不一致的id等等

1、不是所有的UPDATE語句都能被復制，尤其是包含不確定操作的時候

2、調用具有不確定因素的 UDF 時復制也可能出問題，確定了的 UDF 也須要在從服務器上執行

3、運用以下函數的語句也不能被復制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

4、INSERT ... SELECT 會產生比 RBR 更多的行級鎖，復制須要執行全表掃描(WHERE 語句中沒有運用到索引)的 UPDATE 時，須要比 RBR 請求更多的行級鎖

6、對於有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句

7、對於一些復雜的語句，在從服務器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響

8、存儲函數(不是存儲流程 )在被調用的同時也會執行一次 NOW() 函數，這個可以說是壞事也可能是好事

10、數據表必須幾乎和主服務器保持一致才行，否則可能會導致復制出錯

11、只支持REPEATABLE READ可重復讀和以上的隔離級別

Mixed based replication-MBR

觸發器（TRIGGER）：
ROW
主上有，從上沒有，復制正常，數據正常。
主上有，從上也有，復制正常，數據正常。

STATEMENT/MIXED
主上有，從上沒有，復制正常，數據不正常，觸發器裡面的sql語句沒有執行。
主上有，從上也有，復制正常，數據正常。

函數（FUNCTION）：
ROW
主上有，從上沒有，復制正常，數據正常。日志裡記錄的是UDF轉換過的結果。
主上有，從上也有，復制正常，數據正常。

STATEMENT/MIXED
主上有，從上沒有，復制報錯。從不識別UDF函數。
主上有，從上也有，復制正常，數據正常。

存儲過程（STORED PROCEDURES）
ROW
主上有，從上沒有，復制正常，數據正常。記錄的不是call sp，而是SP裡面的sql。
主上有，從上也有，復制正常，數據正常。

STATEMENT/MIXED
主上有，從上沒有，復制正常，數據正常。記錄的不是call sp，而是SP裡面的sql。
主上有，從上也有，復制正常，數據正常。

事件（event）：
ROW
主上有，從上沒有，復制正常，數據正常。記錄的不是計劃，而是EVENT裡面的sql。
主上有，從上也有，復制正常，數據正常。（默認，DISABLE ON SLAVE），ALTER EVENT event_name ENABLE/DISABLE

STATEMENT/MIXED
主上有，從上沒有，復制正常，數據正常。記錄的不是計劃，而是EVENT裡面的sql。
主上有，從上也有，復制正常，數據正常。（默認，DISABLE ON SLAVE）, ALTER EVENT event_name ENABLE/DISABLE

從5.1.8版本開始，MySQL提供了除Statement Level和Row Level之外的第三種復制模式：Mixed，實際上就是前兩種模式的結合。在Mixed模式下，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。新版本中的Statment level還是和以前一樣，僅僅記錄執行的語句。而新版本的MySQL中隊row level模式也被做了優化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改數據的語句，那麼還是會記錄所有行的變更。

以下幾種情況下會自動將binlog的模式由 SBR 模式改成 RBR 模式

當DML語句更新一個NDB表時
當函數中包含 uuid(),user(),current_user(),found_rows(),row_count(),等不確定函數
2個及以上包含 AUTO_INCREMENT 字段的表被更新時
行任何 INSERT DELAYED 語句時
使用了用戶定於的函數（UDF）時
視圖中必須要求運用 RBR 時，例如建立視圖是運用了 UUID() 函數
使用了臨時表(temporary table)

新的基於行模式，用法是 binlog-row-image=minimal 設置該選項後，對於DML操作修改的數據，只有發生變化的字段才保存到binlog裡面，（insert和delete還是全字段）。這提高了master和slave的復制吞吐量，減小了binlog的磁盤占用，網絡資源和內存使用。

另外，針對系統庫 mysql 裡面的表發生變化時的處理准則如下：

1、如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄 2、如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都采用 SBR 模式記錄 3、blockhole引擎不支持row格式，ndb引擎不支持statement格式

參考文章

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html https://dev.mysql.com/doc/refman/5.7/en/binary-log-formats.html https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/ https://www.percona.com/blog/2010/07/20/estimating-replication-capacity/ http://my.oschina.net/zijian1315/blog/202599 http://www.360doc.com/content/14/1107/14/12904276_423333021.shtml https://www.percona.com/blog/2011/12/16/statement-based-replication-with-stored-functions-triggers-and-events/ http://www.tuicool.com/articles/vuUbyy