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 @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: 4delimiter ;# end of log filerollback /* 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也不会被记录。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 query thread_id=14 exec_time=0 error_code=0set 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=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 completion_type=@old_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 query thread_id=14 exec_time=0 error_code=0set 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=0set 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=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 query thread_id=14 exec_time=0 error_code=0set 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=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 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下也是一样的结果。
有兴趣的朋友，也可以测试下binlog-ignore-db，相信也会大吃一惊的。blog地址：http://blog.csdn.net/hw_libo/article/details/40476577
-- bosco qq：375612082
---- end ----
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!