2011-12-16

[轉載][MySQL] Replication option binlog_format

轉載自:MYSQL5.1复制参数binlog_format

MySQL 5.1 中,在復制方面的改進就是引進了新的複制技術:基於行的複制。 簡言之,這種新技術就是關注表中發生變化的記錄,而非以前的照抄binlog 模式。 從MySQL 5.1.12 開始,可以用以下三種模式來實現:基於SQL語句的複制(statement-based replication, SBR),基於行的複制(row-based replication, RBR),混合模式複制(mixed-based replication, MBR)。 相應地,binlog的格式也有三種:STATEMENT,ROW,MIXED。 MBR 模式中,SBR 模式是默認的。


在運行時可以動態低改變binlog的格式,除了以下幾種情況:
  1. 存儲過程或者觸發器中間
  2. 啟用了NDB
  3. 當前會話試用RBR 模式,並且已打開了臨時表


如果binlog採用了MIXED 模式,那麼在以下幾種情況下會自動將binlog的模式由SBR 模式改成RBR 模式。
  1. 當DML語句更新一個NDB表時
  2. 當函數中包含UUID() 時
  3. 2個及以上包含AUTO_INCREMENT 字段的表被更新時
  4. 行任何INSERT DELAYED 語句時
  5. 用UDF 時
  6. 視圖中必須要求使用RBR 時,例如創建視圖是使用了UUID() 函數


設定主從復制模式的方法非常簡單,只要在以前設定複製配置的基礎上,再加一個參數:
binlog_format="STATEMENT" 
#binlog_format="ROW" 
#binlog_format="MIXED"


當然了,也可以在運行時動態修改binlog的格式。 例如
mysql> SET SESSION binlog_format = 'STATEMENT'; 
mysql> SET SESSION binlog_format = 'ROW'; 
mysql> SET SESSION binlog_format = 'MIXED'; 

mysql> SET GLOBAL binlog_format = 'STATEMENT'; 
mysql> SET GLOBAL binlog_format = 'ROW'; 
mysql> SET GLOBAL binlog_format = 'MIXED'; 


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

SBR 的優點:
  1. 歷史悠久,技術成熟
  2. binlog文件較小
  3. binlog中包含了所有數據庫更改信息,可以據此來審核數據庫的安全等情況
  4. binlog可以用於實時的還原,而不僅僅用於復制
  5. 主從版本可以不一樣,從服務器版本可以比主服務器版本高

SBR 的缺點:
  1. 不是所有的UPDATE語句都能被複製,尤其是包含不確定操作的時候。
  2. 調用具有不確定因素的UDF 時復制也可能出問題
  3. 使用以下函數的語句也無法被複製:
    • LOAD_FILE()
    • UUID()
    • USER()
    • FOUND_ROWS()
    • SYSDATE() (除非啟動時啟用了--sysdate-is-now 選項)
  4. INSERT ... SELECT 會產生比RBR 更多的行級鎖
  5. 複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的UPDATE 時,需要比RBR 請求更多的行級鎖
  6. 對於有AUTO_INCREMENT 字段的InnoDB表而言,INSERT 語句會阻塞其他INSERT 語句
  7. 對於一些複雜的語句,在從服務器上的耗資源情況會更嚴重,而RBR 模式下,只會對那個發生變化的記錄產生影響
  8. 存儲函數(不是存儲過程)在被調用的同時也會執行一次NOW() 函數,這個可以說是壞事也可能是好事
  9. 確定了的UDF 也需要在從服務器上執行
  10. 數據表必須幾乎和主服務器保持一致才行,否則可能會導致複製出錯
  11. 執行複雜語句如果出錯的話,會消耗更多資源


RBR 的優點:
  1. 任何情況都可以被複製,這對複制來說是最安全可靠的
  2. 和其他大多數數據庫系統的複制技術一樣
  3. 多數情況下,從服務器上的表如果有主鍵的話,複製就會快了很多
  4. 複製以下幾種語句時的行鎖更少:
    • INSERT ... SELECT
    • 包含AUTO_INCREMENT 字段的INSERT
    • 沒有附帶條件或者並沒有修改很多記錄的UPDATE 或DELETE 語句
  5. 執行INSERT,UPDATE,DELETE 語句時鎖更少
  6. 從服務器上採用多線程來執行複製成為可能

RBR 的缺點:
  1. binlog 大了很多
  2. 複雜的回滾時binlog 中會包含大量的數據
  3. 主服務器上執行UPDATE 語句時,所有發生變化的記錄都會寫到binlog 中,而SBR 只會寫一次,這會導致頻繁發生binlog 的並發寫問題
  4. UDF 產生的大BLOB 值會導致複製變慢
  5. 無法從binlog 中看到都複製了寫什麼語句
  6. 當在非事務表上執行一段堆積的SQL語句時,最好採用SBR 模式,否則很容易導致主從服務器的數據不一致情況發生


另外,針對系統庫 mysql 裡面的表發生變化時的處理規則如下:
  1. 如果是採用INSERT,UPDATE,DELETE 直接操作表的情況,則日誌格式根據binlog_format 的設定而記錄
  2. 如果是採用GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那麼無論如何都採用SBR 模式記錄

注:採用RBR 模式後,能解決很多原先出現的主鍵重複問題

0 回應: