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

136次閱讀

共計(jì) 2387 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。

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

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

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

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

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