The effect of MySQL parameter binlog-do-db on Binlogs writes

1. Environmental description

Purpose: When BINLOG-DO-DB is set in the database, it is in a different binlog_format=statement | Row | Mixed under the binlog of the write effect, this in the master-slave replication will have some pits, because the binlog write incomplete, it is very likely to lead to an inconsistent situation.

Blog Address: http://blog.csdn.net/hw_libo/article/details/40476577

SuSE One-SP1 x86_64 + MySQL 5.5.37

Parameter settings:

Binlog-do-db = Bosco1

Test Example 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);

Test Example 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. Test 1:use Bosco2 and 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)

Then check to see if the above operation is written in 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*/;D elimiter/*!*/;# 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 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*/;

visible, Binlog-do-db=bosco1 specified, transaction ISOLATION level RR + binlog_format=statement or row, All operations under the use of other database (non-BOSCO1 databases) are not recorded in Binlogs, even if the table under Binlog-do-db=bosco1 is operating, and the DDL is not logged.

3. Test 2:use Bosco1 and 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)

Then check to see if the above operation is written in 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=0er Ror_code=0set timestamp=1414258664/*!*/;  begin/*!*/;# at 385# in 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 flags:stmt_end_f### INSERT into ' bo Sco1 '. ' 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 filerollb ACK/* Added by Mysqlbinlog */;/*!50003 SET [Email protected]_completion_type*/;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

Visible, Binlog-do-db=bosco1 specified, transaction ISOLATION level RR + Binlog_format=row:
All DDL/DML operations in the tables in the library are logged to binlogs using the specified database (BOSCO1), and when the tables in other libraries are manipulated, only the DDL operations are logged and DML operations are not logged.

4. Test 3:use Bosco1 and 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)

Then check to see if the above operation is written in 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=0er Ror_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 TIMESTA Mp=1414258428/*!*/;insert into BOSCO1.BOSCO1_TB01 (ID) VALUES (1)/*!*/;# at 494#141026 1:33:48 server ID 1303308 end_log_p OS 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-querythread_id=14exec_time=0error_code=0set TIMESTA mp=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*/;

visible, Binlog-do-db=bosco1 specified, transaction ISOLATION level RR + binlog_format=statement, All operations in tables under all databases under the specified database (BOSCO1 database) are recorded in Binlogs, even if the operation is not binlog-do-db=bosco1 the tables under the specified databases, and the DDL is logged. In addition, under the binlog_format=mixed is the same result.

Interested friends, can also test the next binlog-ignore-db, I believe will be surprised.

Blog Address: http://blog.csdn.net/hw_libo/article/details/40476577

--Bosco qq:375612082

----END----
-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!

The effect of MySQL parameter binlog-do-db on Binlogs writes