MySQL參數binlog-do-db對binlogs寫入的影響，mysqlbinlogdodb

MySQL參數binlog-do-db對binlogs寫入的影響，mysqlbinlogdodb
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 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*/;

可見，指定了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=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*/;

可見，指定了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 ----
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

為何 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:現在做下面的事情:$ 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.複製會broke2次, 第一次，因為 slave嘗試著去之西你給第一條語句，但是slave上並沒有這樣的表"garbage.junk" , 第二次, 隱含的, 因為 對 production.users不會被 複製,因為 root帳號並沒有在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......餘下全文>>
 
mysql主從複製：為何在myini檔案中添加binlog-do-db就同步失敗？解決

binlog_do_db：二進位日誌需要記錄哪些資料庫的更新語句。
另外，你看一下，是底線。但不知道虛線是否管用！