mysql5.7主從同步中binlog同步模式是怎樣的

今天就跟大家聊聊有關(guān)mysql5.7主從同步中binlog同步模式是怎樣的，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

　　mysql5.7主從同步中binlog同步模式(binlog_format)中MIXED模式效率和穩(wěn)定性最好。

　　mysql復(fù)制主要有三種方式：基于SQL語句的復(fù)制(statement-based replication, SBR)，基于行的復(fù)制(row-based replication, RBR)，混合模式復(fù)制(mixed-based replication, MBR)。對(duì)應(yīng)的，binlog的格式也有三種：STATEMENT，ROW，MIXED。

　　一、同步模式

　　1.STATEMENT模式(SBR)

　　每一條會(huì)修改數(shù)據(jù)的sql語句會(huì)記錄到binlog中。優(yōu)點(diǎn)是并不需要記錄每一條sql語句和每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點(diǎn)是在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會(huì)出現(xiàn)問題)

　　2.ROW模式(RBR)

　　不記錄每條sql語句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點(diǎn)是會(huì)產(chǎn)生大量的日志，尤其是alter table的時(shí)候會(huì)讓日志暴漲。

　　3.MIXED模式(MBR)

　　以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語句選擇日志保存方式。

　　二、binlog復(fù)制配置

　　在mysql的配置文件my.cnf中，可以通過一下選項(xiàng)配置binglog相關(guān)

　　binlog_format           = MIXED                         //binlog日志格式，mysql默認(rèn)采用statement，建議使用mixed
　　log-bin                 = /data/mysql/mysql-bin.log    //binlog日志文件
　　expire_logs_days        = 7                           //binlog過期清理時(shí)間
　　max_binlog_size         = 100m                       //binlog每個(gè)日志文件大小
　　binlog_cache_size       = 4m                        //binlog緩存大小
　　max_binlog_cache_size   = 512m                     //最大binlog緩存大小

　　三、MIXED說明

　　對(duì)于執(zhí)行的SQL語句中包含now()這樣的時(shí)間函數(shù)，會(huì)在日志中產(chǎn)生對(duì)應(yīng)的unix_timestamp()*1000的時(shí)間字符串，slave在完成同步時(shí)，取用的是sqlEvent發(fā)生的時(shí)間來保證數(shù)據(jù)的準(zhǔn)確性。另外對(duì)于一些功能性函數(shù)slave能完成相應(yīng)的數(shù)據(jù)同步，而對(duì)于上面指定的一些類似于UDF函數(shù)，導(dǎo)致Slave無法知曉的情況，則會(huì)采用ROW格式存儲(chǔ)這些Binlog，以保證產(chǎn)生的Binlog可以供Slave完成數(shù)據(jù)同步。

　　現(xiàn)在來比較以下 SBR 和 RBR 2中模式各自的優(yōu)缺點(diǎn)：

　　SBR 的優(yōu)點(diǎn)：

　　歷史悠久，技術(shù)成熟

　　binlog文件較小

　　binlog中包含了所有數(shù)據(jù)庫更改信息，可以據(jù)此來審核數(shù)據(jù)庫的安全等情況

　　binlog可以用于實(shí)時(shí)的還原，而不僅僅用于復(fù)制

　　主從版本可以不一樣，從服務(wù)器版本可以比主服務(wù)器版本高

　　SBR 的缺點(diǎn)：

　　不是所有的UPDATE語句都能被復(fù)制，尤其是包含不確定操作的時(shí)候。

　　調(diào)用具有不確定因素的 UDF 時(shí)復(fù)制也可能出問題

　　使用以下函數(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ù)制需要進(jìn)行全表掃描(WHERE 語句中沒有使用到索引)的 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ù)一樣

　　多數(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í)的處理規(guī)則如下：

　　如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據(jù) binlog_format 的設(shè)定而記錄

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

　　注：采用 RBR 模式后，能解決很多原先出現(xiàn)的主鍵重復(fù)問題。

看完上述內(nèi)容，你們對(duì)mysql5.7主從同步中binlog同步模式是怎樣的有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。