MySQL參數binlog-do-db對binlogs寫入的影響

標籤：binlog-do-db   binlog-ignore-db   binlog_format   mysql binlogs   

1. 環境描述

目的：當資料庫中設定了binlog-do-db時，在不同的binlog_format=statement | row | mixed 下對binlog的寫入影響，這個在主從複製中會有一些坑，由於binlog的寫入不完全，極有可能會導致主從不一致的情況的。

blog地址：http://blog.csdn.net/hw_libo/article/details/40476577

SuSE 11 sp1 x86_64  +  MySQL 5.5.37

參數設定：

binlog-do-db = bosco1

測試範例1：

use bosco2;create table bosco1.bosco1_tb01(id int);create table bosco2.bosco2_tb01(id int);insert into bosco1.bosco1_tb01(id) values(1);insert into bosco2.bosco2_tb01(id) values(1);

測試範例2：

use bosco1;create table bosco1.bosco1_tb01(id int);create table bosco2.bosco2_tb01(id int);insert into bosco1.bosco1_tb01(id) values(1);insert into bosco2.bosco2_tb01(id) values(1);

2. 測試1：use bosco2及SBR/RBR/MBR下

binlog-do-db=bosco1;MySQL [(none)]> use bosco2;Database changedMySQL [bosco2]> select @@tx_isolation,@@binlog_format;+-----------------+-----------------+| @@tx_isolation  | @@binlog_format |+-----------------+-----------------+| REPEATABLE-READ | STATEMENT       |+-----------------+-----------------+1 row in set (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);Query OK, 0 rows affected (0.01 sec)MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);Query OK, 1 row affected (0.01 sec)MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);Query OK, 1 row affected (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.01 sec)

那麼來查看一下上面的操作有沒有寫入binlog中：

# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000013/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#141026  1:41:09 server id 1303308  end_log_pos 107 Start: binlog v 4, server v 5.5.37-log created 141026  1:41:09# at 107#141026  1:43:02 server id 1303308  end_log_pos 150 Rotate to mysql-bin.000014  pos: 4DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可見，指定了binlog-do-db=bosco1，交易隔離等級RR + binlog_format=statement或是row，在使用其他database（非bosco1資料庫）下的所有操作都不會記錄到binlogs中，即使是操作binlog-do-db=bosco1下的表；而且DDL也不會被記錄。

3. 測試2：use bosco1及RBR下

binlog-do-db=bosco1;MySQL [bosco2]> use bosco1;MySQL [bosco1]> select @@tx_isolation,@@binlog_format;+-----------------+-----------------+| @@tx_isolation  | @@binlog_format |+-----------------+-----------------+| REPEATABLE-READ | ROW             |+-----------------+-----------------+1 row in set (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);Query OK, 0 rows affected (0.01 sec)MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);Query OK, 1 row affected (0.01 sec)MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);Query OK, 1 row affected (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.01 sec)

那麼來查看一下上面的操作有沒有寫入binlog中：

# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000006……SET @@session.collation_database=DEFAULT/*!*/;create table bosco1.bosco1_tb01(id int)/*!*/;# at 211#141026  1:37:44 server id 1303308  end_log_pos 315 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258664/*!*/;create table bosco2.bosco2_tb01(id int)/*!*/;# at 315#141026  1:37:44 server id 1303308  end_log_pos 385 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258664/*!*/;BEGIN/*!*/;# at 385# at 437#141026  1:37:44 server id 1303308  end_log_pos 437 Table_map: `bosco1`.`bosco1_tb01` mapped to number 49#141026  1:37:44 server id 1303308  end_log_pos 471 Write_rows: table id 49 flags: STMT_END_F### INSERT INTO `bosco1`.`bosco1_tb01`### SET###   @1=1# at 471#141026  1:37:44 server id 1303308  end_log_pos 498 Xid = 200COMMIT/*!*/;# at 498#141026  1:37:49 server id 1303308  end_log_pos 541 Rotate to mysql-bin.000011  pos: 4DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可見，指定了binlog-do-db=bosco1，交易隔離等級RR + binlog_format=row：
在使用指定的database（bosco1資料庫）下操作本身庫中的表所有DDL/DML操作都會記錄到binlogs中，而操作其他庫中的表時，只有DDL操作被記錄下來，DML操作都不會記錄。

4. 測試3：use bosco1及SBR/MBR下

binlog-do-db=bosco1;MySQL [bosco2]> use bosco1;MySQL [bosco1]> select @@tx_isolation,@@binlog_format;+-----------------+-----------------+| @@tx_isolation  | @@binlog_format |+-----------------+-----------------+| REPEATABLE-READ | STATEMENT       |+-----------------+-----------------+1 row in set (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);Query OK, 0 rows affected (0.00 sec)MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);Query OK, 1 row affected (0.00 sec)MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);Query OK, 1 row affected (0.00 sec)MySQL [bosco1]> flush logs;Query OK, 0 rows affected (0.00 sec)

那麼來查看一下上面的操作有沒有寫入binlog中：

# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000008……SET @@session.collation_database=DEFAULT/*!*/;create table bosco1.bosco1_tb01(id int)/*!*/;# at 211#141026  1:33:43 server id 1303308  end_log_pos 315 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258423/*!*/;create table bosco2.bosco2_tb01(id int)/*!*/;# at 315#141026  1:33:48 server id 1303308  end_log_pos 385 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258428/*!*/;BEGIN/*!*/;# at 385#141026  1:33:48 server id 1303308  end_log_pos 494 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258428/*!*/;insert into bosco1.bosco1_tb01(id) values(1)/*!*/;# at 494#141026  1:33:48 server id 1303308  end_log_pos 521 Xid = 188COMMIT/*!*/;# at 521#141026  1:33:50 server id 1303308  end_log_pos 591 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258430/*!*/;BEGIN/*!*/;# at 591#141026  1:33:50 server id 1303308  end_log_pos 700 Querythread_id=14exec_time=0error_code=0SET TIMESTAMP=1414258430/*!*/;insert into bosco2.bosco2_tb01(id) values(1)/*!*/;# at 700#141026  1:33:50 server id 1303308  end_log_pos 727 Xid = 189COMMIT/*!*/;# at 727#141026  1:33:58 server id 1303308  end_log_pos 770 Rotate to mysql-bin.000009  pos: 4DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可見，指定了binlog-do-db=bosco1，交易隔離等級RR + binlog_format=statement，在使用指定的database（bosco1資料庫）下操作所有資料庫下的表中的所有操作DML都會記錄到binlogs中，即使是操作非binlog-do-db=bosco1指定資料庫下的表；而且DDL也會被記錄。另外在binlog_format=mixed下也是一樣的結果。

有興趣的朋友，也可以測試下binlog-ignore-db，相信也會大吃一驚的。

blog地址：http://blog.csdn.net/hw_libo/article/details/40476577

-- Bosco  QQ：375612082

---- END ----
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

MySQL參數binlog-do-db對binlogs寫入的影響