程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL參數binlog-do-db對binlogs寫入的影響

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

編輯:MySQL綜合教程

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


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 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)

那麼來查看一下上面的操作有沒有寫入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*/;
可見,指定了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 	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*/;
可見,指定了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 	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*/;
可見,指定了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 ----
-------------------------------------------------------------------------------------------------------
版權所有,文章允許轉載,但必須以鏈接方式注明源地址,否則追究法律責任!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved