Impact of MySQL parameter binlog-do-db on binlogs writing

Impact of MySQL parameter binlog-do-db on binlogs writing
1. Environment Description

Objective: When binlog-do-db is set in the database, the impact on binlog write under different binlog_format = statement | row | mixed may be poor in master-slave replication, because the binlog write is incomplete, it is very likely that the master will be inconsistent.

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

SuSE 11 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

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)

Check whether the above operation has been written into the 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*/;

It can be seen that the binlog-do-db = bosco1 is specified, the transaction isolation level RR + binlog_format = statement or row is specified, and other databases (non-bosco1 databases) are used) all operations under are not recorded in binlogs, even for tables under binlog-do-db = bosco1, and DDL is not recorded. 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)

Check whether the above operation has been written into the 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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

It can be seen that the binlog-do-db = bosco1 is specified, and the transaction isolation level RR + binlog_format = row:
All DDL/DML operations on tables in the database itself are recorded in binlogs when the specified database (bosco1 database) is used. When operating on tables in other databases, DML operations are not recorded only when DDL operations are recorded.
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)

Check whether the above operation has been written into the 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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

It can be seen that the binlog-do-db = bosco1 is specified, the transaction isolation level RR + binlog_format = statement is specified, and the specified database (bosco1 database) is used) all operations in the tables in all databases under the following DML will be recorded in binlogs, even if the operation is not in the binlog-do-db = bosco1 specified database table, and DDL will also be recorded. In addition, the same result is returned under binlog_format = mixed.

If you are interested, you can also test binlog-ignore-db. I believe it will be surprising.

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

-- Bosco QQ: 375612082

---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!