目的:當數據庫中設置了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);
binlog-do-db=bosco1; MySQL [(none)]> use bosco2; Database changed MySQL [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)
# 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 @OLD_COMPLETION_TYPE=@@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: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_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也不會被記錄。
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 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258664/*!*/; create table bosco2.bosco2_tb01(id int) /*!*/; # at 315 #141026 1:37:44 server id 1303308 end_log_pos 385 Query thread_id=14 exec_time=0 error_code=0 SET 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 = 200 COMMIT/*!*/; # at 498 #141026 1:37:49 server id 1303308 end_log_pos 541 Rotate to mysql-bin.000011 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;可見,指定了binlog-do-db=bosco1,事務隔離級別RR + binlog_format=row:
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 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258423/*!*/; create table bosco2.bosco2_tb01(id int) /*!*/; # at 315 #141026 1:33:48 server id 1303308 end_log_pos 385 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258428/*!*/; BEGIN /*!*/; # at 385 #141026 1:33:48 server id 1303308 end_log_pos 494 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258428/*!*/; insert into bosco1.bosco1_tb01(id) values(1) /*!*/; # at 494 #141026 1:33:48 server id 1303308 end_log_pos 521 Xid = 188 COMMIT/*!*/; # at 521 #141026 1:33:50 server id 1303308 end_log_pos 591 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258430/*!*/; BEGIN /*!*/; # at 591 #141026 1:33:50 server id 1303308 end_log_pos 700 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1414258430/*!*/; insert into bosco2.bosco2_tb01(id) values(1) /*!*/; # at 700 #141026 1:33:50 server id 1303308 end_log_pos 727 Xid = 189 COMMIT/*!*/; # at 727 #141026 1:33:58 server id 1303308 end_log_pos 770 Rotate to mysql-bin.000009 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_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下也是一樣的結果。
blog地址:http://blog.csdn.net/hw_libo/article/details/40476577
-- Bosco QQ:375612082
---- END ----
-------------------------------------------------------------------------------------------------------
版權所有,文章允許轉載,但必須以鏈接方式注明源地址,否則追究法律責任!