review了一次binlog_format的可選配置項
binlog_format 主要是用來調整binlog的記錄格式,官方文件說明提供了三種可選配置,本文對比一下三者的區別。官方文件上面沒有明確說明三者的區別,看了一篇文章寫得挺好的,摘錄一下
- STATEMENT
- 記錄每條執行SQL
- 每一條會修改資料的sql語句會記錄到binlog中。
- 優點是並不需要記錄每一條sql語句和每一行的資料變化,減少了binlog日誌量,節約IO,提高效能。
- 缺點是在某些情況下會導致master-slave中的資料不一致(如sleep()函式, last_insert_id(),以及user-defined functions(udf)等會出現問題)
- ROW
- 記錄什麼資料被修改成什麼樣子
- 不記錄每條sql語句的上下文資訊,僅需記錄哪條資料被修改了,修改成什麼樣了。而且不會出現某些特定情況下的儲存過程、或function、或trigger的呼叫和觸發無法被正確複製的問題。缺點是會產生大量的日誌,尤其是alter table的時候會讓日誌暴漲
- MIX
- 以上兩種模式的混合使用,一般的複製使用STATEMENT模式儲存binlog,對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog,MySQL會根據執行的SQL語句選擇日誌儲存方式。
三者的比較
MIXED說明:
對於執行的SQL語句中包含now()這樣的時間函式,會在日誌中產生對應的unix_timestamp()*1000的時間字串,slave在完成同步時,取用的是sqlEvent發生的時間來保證資料的準確性。另外對於一些功能性函式slave能完成相應的資料同步,而對於上面指定的一些類似於UDF函式,導致Slave無法知曉的情況,則會採用ROW格式儲存這些Binlog,以保證產生的Binlog可以供Slave完成資料同步。
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 模式後,能解決很多原先出現的主鍵重複問題。
參考:
https://www.cnblogs.com/langtianya/p/5504774.html
MySQL官方文件
相關文章
- 【Database】可選的資料來源配置項Database
- GRUB配置預設選擇上一次的啟動項
- VSFTPD的配置選項FTP
- 常見的nginx的配置選項Nginx
- Win10系統如何刪除右鍵選單中的“配置可交換顯示卡”選項Win10
- Swoole server配置選項Server
- 管理 MySQL Shell 配置選項MySql
- 實用的可選項(Optional)擴充套件套件
- audit物件許可權時的not exists選項!物件
- kafka文件: 配置選項翻譯Kafka
- springboot tomcat的配置選項大全Spring BootTomcat
- Oracle建立索引選擇合適的可選項及效率問題Oracle索引
- Win10電腦右鍵選單中“配置可交換顯示卡”選項不見了如何解決Win10
- Swift - 可選項判斷 'guard let / if let' 的使用技巧Swift
- linux 靜態地址配置選項Linux
- fn project 執行時配置選項Project
- c# listbox可顯示多個子項的列表控制元件(可單多選子項)C#控制元件
- 垂直可伸縮的導航選單例項程式碼單例
- 圓形可滑動選單(可以動態新增選單項)
- php.ini 核心配置選項說明PHP
- FFmpeg音視訊編譯配置選項編譯
- FreeBSD 預設核心配置選項(轉)
- webstorm 配置webserver選項,讓其他ip裝置可訪問(如手機等測試效果)WebORMServer
- 刪除Windows 10右鍵選單中的授予訪問許可權選項Windows訪問許可權
- 觀察下面的選項,選出正確的選項。
- 程式碼review工具:Review BoardView
- mysql binlog_format和臨時表MySqlORM
- 2.4.13 Step 12: (可選) 執行指令碼來安裝額外選項指令碼
- 點選文字框彈出可檢索下拉選單程式碼例項
- NFS介紹、NFS服務端安裝配置、NFS配置選項NFS服務端
- 記一次ios系統select標籤第一個選項不能被選中的bugiOS
- Android 圖片選擇器,豐富的配置選項,極大程度的簡化使用Android
- Windows10系統右鍵選單新建選項不見了的解決方法Windows
- (譯) 在可執行檔案中新增選單項 - by Fenri
- 又遇到「GPT寫的review」了?看看北大&密歇根的這個研究工作GPTView
- xp系統程式的相容選項卡不見了怎麼辦?
- code ReviewView
- 選擇優化選項的方案優化