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 ----
-------------------------------------------------------------------------------------------------------
版权全部,文章同意转载。但必须以链接方式注明源地址。否则追究法律责任!
最新文章
- 微信支付(.NET版)
- 更换项目jdk版本
- VMWARE里启动kylin16.0时出现'SMBus Host Controller not enabled'(还未进入系统)
- 最喜欢的VS 键盘快捷键摘抄
- POJ 3274 Gold Balanced Lineup
- No.013 Roman to Integer
- 精通C#(第6版)
- Javascript图片轮播
- VMware vSphere 服务器虚拟化之十六 桌面虚拟化之VMware Horizon View
- Matlab: 作图
- 2.1JAVA基础复习——JAVA语言的基础组成注释和常量变量
- STM32CubeMX HAL库串口+DMA数据发送不定长度数据接收
- Python 中的object takes no parameters错误
- 业务侧有大量timeout请求超时日志
- npm私服搭建
- 详解 Python3 正则表达式(四)
- jenkins的时间与服务器的时间不一致
- flask框架下的jinja2模板引擎(1)(模板渲染)
- [Linux 学习] Centos 图形和命令行界面切换
- 0x01 MySQL What's DataBase
热门文章
- TaoCode-淘宝的SVN开源托管平台
- iOS8开发~Swift(五)Swift与OC混编
- JavaScript中获取Map集合中的key和value值(前提是:既不知道key为什么值,也不知道value有哪些值)
- colorscheme-如何vim颜色风格
- vue --- 全局守卫
- chage---修改帐号和密码的有效期限
- 【CS Round #39 (Div. 2 only) D】Seven-segment Display
- Struts2+Spring+Hibernate step by step 06 整合Hibernate
- 用 OPENSSL 生成不同格式的密钥
- mk-编译信息的意义