動態和靜態設定binlog模式及詳解

許願流星1號發表於2015-12-02

 靜態設定binlog格式:

vi my.cnf
  log-bin = mysql-bin
  #binlog_format = "STATEMENT"
  #binlog_format = "ROW"
  binlog_format = "MIXED"

  動態修改binlog格式:

  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';

 

 

 

mysql中binlog_format模式與配置詳解

 

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相關

 程式碼如下

複製程式碼

binlog_format           = MIXED                         //binlog日誌格式,mysql預設採用statement,建議使用mixed

log-bin                 = /data/mysql/mysql-bin.log    //binlog日誌檔案

expire_logs_days        = 7                           //binlog過期清理時間

max_binlog_size         = 100m                       //binlog每個日誌檔案大小

binlog_cache_size       = 4m                        //binlog快取大小

max_binlog_cache_size   = 512m                     //最大binlog快取大小

 

set @@global.max_binlog_size=209715200;(200m)

set @@global.binlog_cache_size=4194304;

set @@global.max_binlog_cache_size=419430400;

三 MIXED說明

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

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

STATEMENT模式(SBR) 的優點:

歷史悠久,技術成熟

binlog檔案較小

binlog中包含了所有更改資訊,可以據此來稽核資料庫的安全等情況

binlog可以用於實時的還原,而不僅僅用於複製

主從版本可以不一樣,從伺服器版本可以比主伺服器版本高

 

STATEMENT模式(SBR)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 也需要在從伺服器上執行

資料表必須幾乎和主伺服器保持一致才行,否則可能會導致複製出錯

執行復雜語句如果出錯的話,會消耗更多資源

ROW模式(RBR)RBR 的優點:

任何情況都可以被複制,這對複製來說是最安全可靠的

和其他大多數資料庫系統的複製技術一樣

多數情況下,從伺服器上的表如果有主鍵的話,複製就會快了很多

複製以下幾種語句時的行鎖更少:

* INSERT ... SELECT

* 包含 AUTO_INCREMENT 欄位的 INSERT

* 沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

執行 INSERT,UPDATE,DELETE 語句時鎖更少

從伺服器上採用多執行緒來執行復製成為可能

ROW模式(RBR)RBR 的缺點:

binlog 大了很多

複雜的回滾時 binlog 中會包含大量的資料

主伺服器上執行 UPDATE 語句時,所有發生變化的記錄都會寫到 binlog 中,而 SBR 只會寫一次,這會導致頻繁發生 binlog 的併發寫問題

UDF 產生的大 BLOB 值會導致複製變慢

無法從 binlog 中看到都複製了寫什麼語句

當在非事務表上執行一段堆積的SQL語句時,最好採用 SBR 模式,否則很容易導致主從伺服器的資料不一致情況發生

 

另外,針對系統庫 mysql 裡面的表發生變化時的處理規則如下:

如果是採用 INSERT,UPDATE,DELETE 直接操作表的情況,則日誌格式根據 binlog_format 的設定而記錄

如果是採用 GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那麼無論如何都採用 SBR 模式記錄

注:採用 RBR 模式後,能解決很多原先出現的主鍵重複問題。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29893219/viewspace-1851546/,如需轉載,請註明出處,否則將追究法律責任。

相關文章