MySQL日誌格式 binlog_format

標籤：

MySQL 5.5 中對於二進位日誌 (binlog) 有 3 種不同的格式可選：Mixed,Statement,Row，預設格式是 Statement。總結一下這三種格式日誌的優缺點。

MySQL Replication 複製可以是基於一條語句 (Statement Level) ，也可以是基於一條記錄 (Row Level)，可以在 MySQL 的配置參數中設定這個複製層級，不同複製層級的設定會影響到 Master 端的 bin-log 日誌格式。

1. Row
日誌中會記錄成每一行資料被修改的形式，然後在 slave 端再對相同的資料進行修改。

優點：在 row 模式下，bin-log 中可以不記錄執行的 SQL 陳述式的上下文相關的資訊，僅僅只需要記錄那一條記錄被修改了，修改成什麼樣了。所以 row 的日誌內容會非常清楚的記錄下每一行資料修改的細節，非常容易理解。而且不會出現某些特定情況下的預存程序或 function ，以及 trigger 的調用和觸發無法被正確複製的問題。

缺點：在 row 模式下，所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容，比如有這樣一條 update 語句：

[sql] view plaincopy

1. UPDATE product SET owner_member_id = ‘b‘ WHERE owner_member_id = ‘a‘  

執行之後，日誌中記錄的不是這條 update 語句所對應的事件 (MySQL 以事件的形式來記錄 bin-log 日誌) ，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。自然，bin-log 日誌的量就會很大。尤其是當執行 alter table 之類的語句的時候，產生的日誌量是驚人的。因為 MySQL 對於 alter table 之類的表結構變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表。那麼該表的每一條記錄都會被記錄到日誌中。

2. Statement
每一條會修改資料的 SQL 都會記錄到 master 的 bin-log 中。slave 在複製的時候 SQL 進程會解析成和原來 master 端執行過的相同的 SQL 再次執行。

優點：在 statement 模式下，首先就是解決了 row 模式的缺點，不需要記錄每一行資料的變化，減少了 bin-log 日誌量，節省 I/O 以及儲存資源，提高效能。因為他只需要記錄在 master 上所執行的語句的細節，以及執行語句時候的內容相關的資訊。

缺點：在 statement 模式下，由於他是記錄的執行語句，所以，為了讓這些語句在 slave 端也能正確執行，那麼他還必須記錄每條語句在執行的時候的一些相關資訊，也就是上下文資訊，以保證所有語句在 slave 端杯執行的時候能夠得到和在 master 端執行時候相同的結果。另外就是，由於 MySQL 現在發展比較快，很多的新功能不斷的加入，使 MySQL 的複製遇到了不小的挑戰，自然複製的時候涉及到越複雜的內容，bug 也就越容易出現。在 statement 中，目前已經發現的就有不少情況會造成 MySQL 的複製出現問題，主要是修改資料的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep() 函數在有些版本中就不能被正確複製，在預存程序中使用了 last_insert_id() 函數，可能會使 slave 和 master 上得到不一致的 id 等等。由於 row 是基於每一行來記錄的變化，所以不會出現類似的問題。

3. Mixed

從 5.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種複製模式：Mixed，實際上就是前兩種模式的結合。

在 Mixed 模式下，MySQL 會根據執行的每一條具體的 SQL 陳述式來區分對待記錄的日誌形式，也就是在 statement 和 row 之間選擇一種。

新版本中的 statment 還是和以前一樣，僅僅記錄執行的語句。而新版本的 MySQL 中對 row 模式也被做了最佳化，並不是所有的修改都會以 row 模式來記錄，比如遇到表結構變更的時候就會以 statement 模式來記錄，如果 SQL 陳述式確實就是 update 或者 delete 等修改資料的語句，那麼還是會記錄所有行的變更。

其他參考資訊

除以下幾種情況外，在運行時可以動態改變 binlog 的格式：
1. 儲存流程或者觸發器中間；
2. 啟用了 NDB；
3. 當前會話使用 row 模式，並且已開啟了暫存資料表；

如果 binlog 採用了 Mixed 模式，那麼在以下幾種情況下會自動將 binlog 的模式由 statement 模式變為 row 模式：
1. 當 DML 語句更新一個 NDB 表時；
2. 當函數中包含 UUID() 時；
3. 2 個及以上包含 AUTO_INCREMENT 欄位的表被更新時；
4. 執行 INSERT DELAYED 語句時；
5. 用 UDF 時；
6. 視圖中必須要求運用 row 時，例如建立視圖時使用了 UUID() 函數；

 

http://blog.csdn.net/mycwq/article/details/17136997

MySQL日誌格式 binlog_format