MySQL中binlog三種格式的優(yōu)缺點是什么

這篇文章將為大家詳細講解有關MySQL中binlog三種格式的優(yōu)缺點是什么，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

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

STATEMENT是基于sql語句級別的binlog,每一條修改數(shù)據(jù)的sql都會被保存到binlog里；ROW是基于行級別的,他會記錄每一行記錄的變化,就是將每一行的修改都記錄到
binlog里面,記錄的非常詳細，但sql語句并沒有在binlog里,在replication里面也不會因為存儲過程觸發(fā)器等造成Master-Slave數(shù)據(jù)不一致的問題,但是有個致命的缺點
日志量比較大.由于要記錄每一行的數(shù)據(jù)變化,當執(zhí)行update語句后面不加where條件的時候或alter table的時候,產(chǎn)生的日志量是相當?shù)拇?。MIXED:在默認情況下是
statement,但是在某些情況下會切換到row狀態(tài)，如當一個DML更新一個ndb引擎表，或者是與時間用戶相關的函數(shù)等。

SBR 的優(yōu)點：
歷史悠久，技能成熟
binlog文件較小
binlog中包含了所有數(shù)據(jù)庫修改信息，可以據(jù)此來審核數(shù)據(jù)庫的安全等情況
binlog可以用于實時的還原，而不僅僅用于復制
主從版本可以不一樣，從服務器版本可以比主服務器版本高
SBR 的缺點：
不是所有的UPDATE語句都能被復制，尤其是包含不確定操作的時候。
調(diào)用具有不確定因素的 UDF 時復制也可能出疑問
運用以下函數(shù)的語句也不能被復制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)
INSERT … SELECT 會產(chǎn)生比 RBR 更多的行級鎖
復制須要執(zhí)行 全表掃描(WHERE 語句中沒有運用到索引)的 UPDATE 時，須要比 RBR 請求更多的行級鎖
對于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
對于一些復雜的語句，在從服務器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發(fā)生變化的記錄產(chǎn)生影響
存儲函數(shù)(不是存儲流程 )在被調(diào)用的同時也會執(zhí)行一次 NOW() 函數(shù)，這個可以說是壞事也可能是好事
確定了的 UDF 也須要在從服務器上執(zhí)行
數(shù)據(jù)表必須幾乎和主服務器保持一致才行，否則可能會導致復制出錯
執(zhí)行復雜語句如果出錯的話，會消耗更多資源


RBR 的優(yōu)點：
任何情況都可以被復制，這對復制來說是最安全可靠的
和其他大多數(shù)數(shù)據(jù)庫系統(tǒng)的復制技能一樣
多數(shù)情況下，從服務器上的表如果有主鍵的話，復制就會快了很多
復制以下幾種語句時的行鎖更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 沒有附帶條件或者并沒有修改很多記錄的 UPDATE 或 DELETE 語句
執(zhí)行 INSERT，UPDATE，DELETE 語句時鎖更少
從服務器上采用多線程來執(zhí)行復制成為可能
RBR 的缺點：
binlog 大了很多
復雜的回滾時 binlog 中會包含大量的數(shù)據(jù)
主服務器上執(zhí)行 UPDATE 語句時，所有發(fā)生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發(fā)生 binlog 的并發(fā)寫疑問
UDF 產(chǎn)生的大 BLOB 值會導致復制變慢
不能從 binlog 中看到都復制了寫什么語句(加密過的)
當在非事務表上執(zhí)行一段堆積的SQL語句時，最好采用 SBR 模式，否則很容易導致主從服務器的數(shù)據(jù)不一致情況發(fā)生
另外，針對系統(tǒng)庫 mysql 里面的表發(fā)生變化時的處理準則如下：
如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據(jù) binlog_format 的設定而記錄
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那么無論如何 都采用 SBR 模式記錄。


MBR融合了兩種記錄模式的優(yōu)點。故個人推薦使用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  
重啟服務后永久生效。

關于“MySQL中binlog三種格式的優(yōu)缺點是什么”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。