mysql中binlogformat模式與配置細解

2024-07-24 12:35:06

字體：大中小

來源：轉載

供稿：網友

　　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相關,代碼如下:

　　binlog_format = MIXED //binlog日志格式，mysql默認采用statement，建議使用mixed
　　log-bin = /data/mysql/mysql-bin.log    //binlog日志文件
　　expire_logs_days   = 7       //binlog過期清理時間
　　max_binlog_size = 100m      //binlog每個日志文件大小
　　binlog_cache_size = 4m   //binlog緩存大小 phpfensi.com
　　max_binlog_cache_size = 512m   //最大binlog緩存大小
　　三 MIXED說明

　　對于執行的SQL語句中包含now()這樣的時間函數,會在日志中產生對應的unix_timestamp()*1000的時間字符串,slave在完成同步時,取用的是sqlEvent發生的時間來保證數據的準確性,另外對于一些功能性函數slave能完成相應的數據同步,而對于上面指定的一些類似于UDF函數,導致Slave無法知曉的情況,則會采用ROW格式存儲這些Binlog,以保證產生的Binlog可以供Slave完成數據同步.

　　現在來比較以下 SBR 和 RBR 2中模式各自的優缺點.

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

　　注:采用 RBR 模式后,能解決很多原先出現的主鍵重復問題.

（編輯：武林網）

上一篇：MySQL數據備份Outofresourceswhenopening file時報錯

下一篇：在mysql中replace替換字符串返回結果空或0