MySQL中binlog_format模式與配置詳解

www.111cn.net 編輯:kp878 來源:本站整理

mysql復制主要有三種方式：基于SQL語句的復制(statement-based replication, SBR)，基于行的復制(row-based replication, RBR)，混合模式復制(mixed-based replication, MBR)。對應的，binlog的格式也有三種：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一條會修改數據的sql語句會記錄到binlog中。優點是并不需要記錄每一條sql語句和每一行的數據變化，減少了binlog日志量，節約IO，提高性能。缺點是在某些情況下會導致master-slave中的數據不一致(如sleep()函數，last_insert_id()，以及user-defined functions(udf)等會出現問題)

② ROW模式（RBR）

不記錄每條sql語句的上下文信息，僅需記錄哪條數據被修改了，修改成什么樣了。而且不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正確復制的問題。缺點是會產生大量的日志，尤其是alter table的時候會讓日志暴漲。

③ MIXED模式（MBR）

以上兩種模式的混合使用，一般的復制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇日志保存方式。 binlog復制配置

在mysql的配置文件my.cnf中，可以通過一下選項配置binglog相關

三 MIXED說明

對于執行的SQL語句中包含now()這樣的時間函數，會在日志中產生對應的unix_timestamp()*1000的時間字符串，slave在完成同步時，取用的是sqlEvent發生的時間來保證數據的準確性。另外對于一些功能性函數slave能完成相應的數據同步，而對于上面指定的一些類似于UDF函數，導致Slave無法知曉的情況，則會采用ROW格式存儲這些Binlog，以保證產生的Binlog可以供Slave完成數據同步。

現在來比較以下 SBR 和 RBR 2中模式各自的優缺點：

SBR 的優點：binlog文件較小binlog中包含了所有數據庫更改信息，可以據此來審核數據庫的安全等情況binlog可以用于實時的還原，而不僅僅用于復制主從版本可以不一樣，從服務器版本可以比主服務器版本高

SBR 的缺點：

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

RBR 的優點：

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

RBR 的缺點：

binlog 大了很多復雜的回滾時 binlog中會包含大量的數據主服務器上執行 UPDATE語句時，所有發生變化的記錄都會寫到 binlog中，而 SBR只會寫一次，這會導致頻繁發生 binlog的并發寫問題UDF 產生的大 BLOB值會導致復制變慢無法從 binlog中看到都復制了寫什么語句當在非事務表上執行一段堆積的SQL語句時，最好采用SBR 模式，否則很容易導致主從服務器的數據不一致情況發生

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