MySQL parameter binlog-do-db affects binlogs writing, mysqlbinlogdodb

MySQL parameter binlog-do-db affects binlogs writing, mysqlbinlogdodb
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 SBR / RBR / MBR
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)

Then check if the above operation is written into 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: 4
DELIMITER;
# End of log file
ROLLBACK / * added by mysqlbinlog * /;
/ *! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE * /;
/ *! 50530 SET @@ SESSION.PSEUDO_SLAVE_MODE = 0 * /;
It can be seen that if binlog-do-db = bosco1 is specified and the transaction isolation level is RR + binlog_format = statement or row, all operations under other databases (non-bosco1 database) will not be recorded in binlogs, even if the operation is binlog- do-db = table under bosco1; and DDL will not be 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)

Then check if the above operation is written into 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 * /;
It can be seen that binlog-do-db = bosco1 is specified, and the transaction isolation level RR + binlog_format = row:
When using the specified database (bosco1 database) to operate the tables in its own library, all DDL / DML operations are recorded in binlogs. When operating tables in other libraries, only DDL operations are recorded, and DML operations are not 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)
Then check if the above operation is written into 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 * /;
It can be seen that if binlog-do-db = bosco1 is specified and the transaction isolation level is RR + binlog_format = statement, all operations in the tables under all databases under the specified database (bosco1 database) will be recorded in binlogs, even Operate non-binlog-do-db = bosco1 to specify the table under the database; and DDL will also be recorded. In addition, the same result is obtained under binlog_format = mixed.

Interested friends can also test the binlog-ignore-db, I believe it will be surprised.
Blog address: http://blog.csdn.net/hw_libo/article/details/40476577

-Bosco QQ: 375612082

---- END ----
-------------------------------------------------- -------------------------------------------------- ---
Copyright, the article is allowed to be reprinted, but the source address must be indicated in the form of a link, otherwise legal responsibility will be held!


Why MySQL binlog
The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn 't replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form.) Now you do the following: Now do the following: $ mysqlmysql delete from garbage.junk; mysql use garbage; mysql update production.users set disabled = 1 where user = "root"; You just broke replication, twice. Once, because your slave is going to execute the first query and there's no such table "garbage .junk "on the slave. The second time, silently, because the update to production.users isn't replicated, so now the root user isn't disabled on the slave. The replication will break 2 times, the first time because the slave tries Go ahead, you give the first statement, but there is no such table "garbage.junk" on the slave, the second time, implicitly, because the production.users will not be copied because the root account It is not disabled on the slave. Why? Because binlog-ignore-db doesn't do what you think. The phrase I used earlier, "data in the garbage database isn't replicated," is a fallacy. That's not what it does. In fact, itfilters out binary logging for statements issued from connections whose default database is "garbage." In other words, filtering is not based on the contents of the query-it is based on what database you USE.The other configuration options I mentioned work similarly. The binlog-do-db and binlog-ignore ... the rest of the text >>
 
MySQL master-slave replication: why does adding binlog-do-db in myini file cause synchronization failure? solve

binlog_do_db: Binary logs need to record which database update statements.
In addition, if you look at it, it is underlined. But I do n’t know if the dash will work!