mysql--二進位日誌(bin-log)三種格式介紹及分析

標籤：max   細節   資訊   帶來   優點   sla   發展   儲存   mysq   

 

一、Mysql binlog日誌有三種格式，分別為ROW、Statement以及MiXED。

Row Level
Binary Log會記錄成【每一行資料被修改的形式】，然後在Slave端再對相同的資料進行修改。
如果修改了表的結構，那麼binlog日誌記錄的是重新建立表，在插入欄位、update等動作陳述式，而不是的alter的動作。

優點：在Row Level模式下，Binnary Log可以不記錄執行的Query語句的上下文相關資訊，只要記錄哪一行修改了，修改成什麼樣子。Row Level會詳細的記錄下每一行資料的修改細節，而且不會出現某個特定情況下的預存程序，或Function，以及Trigger的調用和觸發無法被正確複製問題。

缺點：所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。


Statment Level
Statment Level【每一條會修改的SQL語句】都會記錄到Master的Binnary中。Slave端在複製的時候，SQL線程會解析成和原來Master端執行過相同的SQL語句，並再次執行。

優點：首先，解決了Row Level下的缺點，不須要記錄每一行的資料變化，減少了Binnary Log日誌量，節約了IO成本，提高了效能。(相比row能節約多少效能與日誌量，這個取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況，其所產生的日誌量會增加多少，以及帶來的IO效能問題。)

缺點：由於它是記錄的執行語句，為了讓這些語句在Slave端也能正確執行。那麼它還必須記錄每條語句在執行時的一些相關資訊，即上下文資訊，以保證所有語句在Slave端被執行的時候能夠得到和在Master端執行時相同的結果。另外，由於MySQL發展比較快，很多新功能不斷加入，使得MySQL複製遇到了不小的挑戰，複製時設計的內容越複雜，越容易出bug。在Statement Level下，目前已發現不少的情況下會造成MySQL的複製問題。主要是在修改資料使用了某些特定的函數貨功能後，出現，比如：Sleep()函數在有些版本中就不能正確的複製，在預存程序中使用了last_insert_id()函數，可能會使Slave和Master的到不一致的ID，等等。
　　使用以下函數的語句也無法被複製：
　　* LOAD_FILE()
　　* UUID()
　　* USER()
　　* FOUND_ROWS()
　　* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
　　同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖
 
Mixed Level
在Mixed模式下， 是以上兩種level的混合使用。
一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。除了MySQL認為通過Statement方式可能造成複製過程中Master和Slave之間產生不一致資料。(如特殊Procedure和Funtion的使用，UUID()函數的使用等特殊情況)時，它會選擇ROW的模式來記錄變更之外，都會使用Statement方式。

二、Binlog基本配製與格式設定
1.基本配製
Mysql BInlog日誌格式可以通過mysql的my.cnf檔案的屬性binlog_format指定。如以下：
　　binlog_format           = MIXED                 //binlog日誌格式
　　log_bin                  =目錄/mysql-bin.log    //binlog日誌名
　　expire_logs_days    = 7                           //binlog到期清理時間
　　max_binlog_size    100m                         //binlog每個記錄檔大小

2.Binlog日誌格式選擇
Mysql預設是使用Statement日誌格式，推薦使用MIXED.
由於一些特殊使用，可以考慮使用ROWED，如自己通過binlog日誌來同步資料的修改，這樣會節省很多相關操作。對於binlog資料處理會變得非常輕鬆,相對mixed，解析也會很輕鬆(當然前提是增加的日誌量所帶來的IO開銷在容忍的範圍內即可)。

3.mysqlbinlog格式選擇
mysql對於日誌格式的選定原則:如果是採用 INSERT，UPDATE，DELETE 等直接動作表的情況，則日誌格式根據 binlog_format 的設定而記錄,如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何 都採用 SBR 模式記錄。

mysql--二進位日誌(bin-log)三種格式介紹及分析