binlog的幾種復制形式

MySQL?5.5?中對于二進制日志?(binlog)?有?3?種不同的格式可選：Mixed,Statement,Row，默認格式是?Statement。總結一下這三種格式日志的優缺點。

MySQL?Replication?復制可以是基于一條語句?(Statement?Level)?，也可以是基于一條記錄?(Row?Level)，可以在?MySQL?的配置參數中設定這個復制級別，不同復制級別的設置會影響到?Master?端的?bin-log?日志格式。

1.?Row

日志中會記錄成每一行數據被修改的形式，然后在?slave?端再對相同的數據進行修改。

優點：在?row?模式下，bin-log?中可以不記錄執行的?SQL?語句的上下文相關的信息，僅僅只需要記錄哪一條記錄被修改了，修改成什么樣了。所以?row?的日志內容會非常清楚的記錄下每一行數據修改的細節，非常容易理解。而且不會出現某些特定情況下的存儲過程或?function?，以及?trigger?的調用和觸發無法被正確復制的問題。

缺點：在?row?模式下，所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容，比如有這樣一條?update?語句：

1 ?	UPDATE?product?SET?owner_member_id?=?'b'?WHERE?owner_member_id?=?'a'

執行之后，日志中記錄的不是這條?update?語句所對應的事件?(MySQL?以事件的形式來記錄?bin-log?日志)?，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。自然，bin-log?日志的量就會很大。尤其是當執行?alter?table?之類的語句的時候，產生的日志量是驚人的。因為?MySQL?對于?alter?table?之類的表結構變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表。那么該表的每一條記錄都會被記錄到日志中。

2.?Statement

每一條會修改數據的?SQL?都會記錄到?master?的?bin-log?中。slave?在復制的時候?SQL?進程會解析成和原來?master?端執行過的相同的?SQL?再次執行。

優點：在?statement?模式下，首先就是解決了?row?模式的缺點，不需要記錄每一行數據的變化，減少了?bin-log?日志量，節省?I/O?以及存儲資源，提高性能。因為他只需要記錄在?master?上所執行的語句的細節，以及執行語句時候的上下文的信息。

缺點：在?statement?模式下，由于他是記錄的執行語句，所以，為了讓這些語句在?slave?端也能正確執行，那么他還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息，以保證所有語句在?slave?端杯執行的時候能夠得到和在?master?端執行時候相同的結果。

另外就是，由于?MySQL?現在發展比較快，很多的新功能不斷的加入，使?MySQL?的復制遇到了不小的挑戰，自然復制的時候涉及到越復雜的內容，bug?也就越容易出現。在?statement?中，目前已經發現的就有不少情況會造成?MySQL?的復制出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep()?函數在有些版本中就不能被正確復制，在存儲過程中使用了?last_insert_id()?函數，可能會使?slave?和?master?上得到不一致的?id?等等。由于?row?是基于每一行來記錄的變化，所以不會出現類似的問題。

3.?mixed

從官方文檔中看到，之前的?MySQL?一直都只有基于?statement?的復制模式，直到?5.1.5?版本的?MySQL?才開始支持?row?復制。從?5.0?開始，MySQL?的復制已經解決了大量老版本中出現的無法正確復制的問題。但是由于存儲過程的出現，給?MySQL?Replication?又帶來了更大的新挑戰。

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

其他參考信息

除以下幾種情況外，在運行時可以動態改變?binlog?的格式：
?????·存儲流程或者觸發器中間；
?????·啟用了?NDB；
?????·當前會話使用?row?模式，并且已打開了臨時表；

如果?binlog?采用了?Mixed?模式，那么在以下幾種情況下會自動將?binlog?的模式由?statement?模式變為?row?模式：
?????·當?DML?語句更新一個?NDB?表時；
?????·當函數中包含?UUID()?時；
?????·?2?個及以上包含?AUTO_INCREMENT?字段的表被更新時；
?????·?執行?INSERT?DELAYED?語句時；
?????·??用?UDF?時；
?????·??視圖中必須要求運用?row?時，例如建立視圖時使用了?UUID()?函數；

設定主從復制模式：

1 2 3 4 ?

log-bin=mysql-bin #binlog_format="STATEMENT" #binlog_format="ROW" binlog_format="MIXED"

也可以在運行時動態修改?binlog?的格式。例如：

1 2 3 4 5 6 ?

mysql>?SET?SESSION?binlog_format?=?'STATEMENT'; mysql>?SET?SESSION?binlog_format?=?'ROW'; mysql>?SET?SESSION?binlog_format?=?'MIXED'; mysql>?SET?GLOBAL?binlog_format?=?'STATEMENT'; mysql>?SET?GLOBAL?binlog_format?=?'ROW'; mysql>?SET?GLOBAL?binlog_format?=?'MIXED';

兩種模式的對比：
Statement?優點

·歷史悠久，技術成熟；

·產生的?binlog?文件較小；

·binlog?中包含了所有數據庫修改信息，可以據此來審核數據庫的安全等情況；
·binlog?可以用于實時的還原，而不僅僅用于復制；
·主從版本可以不一樣，從服務器版本可以比主服務器版本高；

?

Statement?缺點：
·不是所有的?UPDATE?語句都能被復制，尤其是包含不確定操作的時候；
·調用具有不確定因素的?UDF?時復制也可能出現問題；
·運用以下函數的語句也不能被復制：
???*?LOAD_FILE()
???*?UUID()
???*?USER()
???*?FOUND_ROWS()
??*?SYSDATE()?(除非啟動時啟用了?–sysdate-is-now?選項)
·INSERT?…?SELECT?會產生比?RBR?更多的行級鎖；
·復制須要執行全表掃描?(WHERE?語句中沒有運用到索引)?的?UPDATE?時，須要????????比?row?請求更多的行級鎖；
·對于有?AUTO_INCREMENT?字段的?InnoDB?表而言，INSERT?語句會阻塞其他?INSERT?語句；
·對于一些復雜的語句，在從服務器上的耗資源情況會更嚴重，而?row?模式下，只會對那個發生變化的記錄產生影響；
·存儲函數(不是存儲流程?)在被調用的同時也會執行一次?NOW()?函數，這個可?以說是壞事也可能是好事；
·確定了的?UDF?也須要在從服務器上執行；
·數據表必須幾乎和主服務器保持一致才行，否則可能會導致復制出錯；
·執行復雜語句如果出錯的話，會消耗更多資源；

?

Row?優點
·任何情況都可以被復制，這對復制來說是最安全可靠的；
·和其他大多數數據庫系統的復制技能一樣；
·多數情況下，從服務器上的表如果有主鍵的話，復制就會快了很多；
·復制以下幾種語句時的行鎖更少：
????*?INSERT?…?SELECT
????*?包含?AUTO_INCREMENT?字段的?INSERT
???*?沒有附帶條件或者并沒有修改很多記錄的?UPDATE?或?DELETE?語句
·執行?INSERT，UPDATE，DELETE?語句時鎖更少；
·從服務器上采用多線程來執行復制成為可能；

Row?缺點
·生成的?binlog?日志體積大了很多；
·復雜的回滾時?binlog?中會包含大量的數據；
·主服務器上執行?UPDATE?語句時，所有發生變化的記錄都會寫到?binlog?中，而?statement?只會寫一次，這會導致頻繁發生?binlog?的寫并發請求；
·UDF?產生的大?BLOB?值會導致復制變慢；
·不能從?binlog?中看到都復制了寫什么語句(加密過的)；
·當在非事務表上執行一段堆積的?SQL?語句時，最好采用?statement?模式，否則很容易導致主從服務器的數據不一致情況發生；

另外，針對系統庫?MySQL?里面的表發生變化時的處理準則如下：
·如果是采用?INSERT，UPDATE，DELETE?直接操作表的情況，則日志格式根據?binlog_format?的設定而記錄；
·如果是采用?GRANT，REVOKE，SET?PASSWORD?等管理語句來做的話，那么無論如何都要使用?statement?模式記錄；
·使用?statement?模式后，能處理很多原先出現的主鍵重復問題；

?