MySQL binlog三種格式的優缺點和修改方法介紹

MySQL binlog的格式有三種，基於SQL語句的複製(statement-based replication, SBR)，基於行的複製(row-based replication, RBR)，
混合模式複製(mixed-based replication, MBR)。相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。它主要用於mysql的複製技術。

STATEMENT是基於sql語句級別的binlog,每一條修改資料的sql都會被儲存到binlog裡；ROW是基於行級別的,他會記錄每一行記錄的變化,就是將每一行的修改都記錄到
binlog裡面,記錄的非常詳細，但sql語句並沒有在binlog裡,在replication裡面也不會因為儲存過程觸發器等造成Master-Slave資料不一致的問題,但是有個致命的缺點
日誌量比較大.由於要記錄每一行的資料變化,當執行update語句後面不加where條件的時候或alter table的時候,產生的日誌量是相當的大。MIXED:在預設情況下是
statement,但是在某些情況下會切換到row狀態，如當一個DML更新一個ndb引擎表，或者是與時間使用者相關的函式等。

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 模式記錄。


MBR融合了兩種記錄模式的優點。故個人推薦使用MBR模式。

檢視binlog格式方法:
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)


如何修改binlog格式為MBR模式：

不重啟修改方法（只對該登入會話有效,重新登入無效）
mysql> SET SESSION binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

重啟MySQL服務修改方法:
編輯/etc/my.cnf檔案，加入以下設定：
binlog_format=mixed  
重啟服務後永久生效。