mysql+binlog+do+table_MySQL參数binlog-do-db对binlogs写入的影响

可见。指定了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=0

SET 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=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：

在使用指定的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=0

SET 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=0

SET TIMESTAMP=1414258428/*!*/;

BEGIN

/*!*/;

# at 385

#141026 1:33:48 server id 1303308 end_log_pos 494 Querythread_id=14exec_time=0error_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 Querythread_id=14exec_time=0error_code=0

SET TIMESTAMP=1414258430/*!*/;

BEGIN

/*!*/;

# at 591

#141026 1:33:50 server id 1303308 end_log_pos 700 Querythread_id=14exec_time=0error_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下也是一样的结果。

有兴趣的朋友。也能够測试下binlog-ignore-db，相信也会大吃一惊的。

-- Bosco  QQ：375612082

---- END ----

-------------------------------------------------------------------------------------------------------

版权全部，文章同意转载。但必须以链接方式注明源地址。否则追究法律责任!