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

 靜態設定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相關

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 模式後，能解決很多原先出現的主鍵重複問題。