mysql的binlog格式

日誌格式

5.5預設為statement

可以在session級別修改，需要super許可權

Set session binlog_format=’MIXED’;

Set session sql_log_bin=0; --0不記錄log，預設為1

Master修改此引數不會影響到slave，但不停止複製就修改或只修改master可能會導致slave停止工作；

 

當master以statement/mixed執行且binlog有以row格式記錄的事件時，

Slave可根據實際情況自動切換至row，處理完後再切換為原來的設定；

 

 

Statement風險

當某些sql為nondeterministic時，會導致主備庫書記不一致；

http://dev.mysql.com/doc/refman/5.5/en/bugs.html

 

Create table … select

Create table部分以statement方式記錄，select部分取決於binlog_format設定

  

 

Row風險

不會記錄臨時表操作，如果正在使用row且操作臨時表，切換為statement會導致錯誤結果；

當使用InnoDB且事務隔離級別為read_commited/read_uncommited時，只能使用row，如果在執行時切換成statement會導致無法執行insert；

Row以塊的形式記錄進binlog，--binlog-row-event-max-size規定了其最大尺寸，預設為1024；

即便指定了row，某些sql仍會以statement形式記錄，比如DDL；

slave不支援MyISAM的併發insert；

不使用checksums，在處理binlog時因網路/磁碟等導致的錯誤可能無法被檢測，可在change master to中使用SSL用於checksuming；

當slave_exec_mode=idempotent(預設strict)時，因相應行不存在導致的錯誤無法被捕獲，則master/slave有不同步的風險；

 

Mixed

當Mysql認定當前sql為unsafe時，以row格式記錄

 

 

Unsafe sql

使用limit但不加order by的update/delete http://dev.mysql.com/doc/refman/5.5/en/replication-features-limit.html

呼叫load_file()/UUID()/USER()/RAND()/SLEEP()/SYSDATE()/master_pos_wait()等系統函式sql http://dev.mysql.com/doc/refman/5.5/en/replication-features-functions.html   

UDF

更新包含auto_increment列的表

引用日誌表的sql—主備庫的日誌表可能不同

Load data infile—5.5.6起，該sql被認為不安全

Insert delayed

在事務中操作非事務表

引用系統變數

 

Auto_increment注意事項

基本上statement級別可以正確的複製auto_increment/last_insert_id()/timestamp，但要注意以下幾點：

1

Slave/master上的表宣告auto_increment的必須為同一列，5.5.30之前必須如此，BUG#12669186

2

由trigger/function引發的auto_increment更新被認定是不安全的，5.5 BUG#45677

3

使用alter table新增auto_increment列可能導致主備庫資料順序不一致，因為行序號依賴儲存引擎和插入次序；

要消除此誤差，必須重建表

Create tabe t2 like t1; --create … like會跳過原表的外來鍵和data directory/index directory約束

Alter table t2 add id int auto_increment primary key;

Insert into t2 select * from t1 order by col1, col2; --order by必須列舉出t1所有列

Drop t1;

Alter table t2 rename t1;

 

 

Statement與row格式的區別還體現在replicate-to-db/binlog-to-db上

前者應用於slave，後者作用於master；

這兩個選項在statement和row級別複製時效果不同；

statement複製只檢查use db條件，如果符合即複製；而row則檢查實際表，即便use db不符合也可以複製；

案例

slave上設定replicate_do_db=sales；

master上執行

use prices;

update sales.january set amount=amount+1000;

如果binlog格式為statement不會執行，而row會執行；