MySQL Binlog Mixed模式记录成Row格式

背景:

      一个简单的主从结构,主的binlog format是Mixed模式,在执行一条简单的导入语句时,通过mysqlbinlog导出发现记录的Binlog全部变成了Row的格式(明明设置的是Mixed),现在就说说在什么情况下Binlog的Mixed模式会转变记录成Row格式。

概念:

      binlog format有三种形式:Statement、Mixed、Row,具体的信息可以自行到网上搜查。

分析(本文碰到的案例):

查看MySQL binlog format

dba@192.168.111.4 : dba_test 02:33:39>show variables like 'binlog_format%';                                                                                                   +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

测试语句:

dba@192.168.111.4 : dba_test 02:24:14>create table tmp_test(id int,name varchar(64),age int,primary key(id)) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

dba@192.168.111.4 : dba_test 02:24:23>insert into tmp_test values(1,'aaa',11);
Query OK, 1 row affected (0.02 sec)

dba@192.168.111.4 : dba_test 02:25:17>insert into tmp_test values(2,'bbb',22);
Query OK, 1 row affected (0.02 sec)

dba@192.168.111.4 : dba_test 02:25:23>insert into tmp_test values(3,'ccc',33);
Query OK, 1 row affected (0.01 sec)

dba@192.168.111.4 : dba_test 02:25:28>insert into tmp_test values(4,'ddd',44);
Query OK, 1 row affected (0.01 sec)

dba@192.168.111.4 : dba_test 02:25:34>insert into tmp_test values(5,'eee',55);
Query OK, 1 row affected (0.01 sec)

dba@192.168.111.4 : dba_test 02:25:42>select * from tmp_test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aaa  |   11 |
|  2 | bbb  |   22 |
|  3 | ccc  |   33 |
|  4 | ddd  |   44 |
|  5 | eee  |   55 |
+----+------+------+
5 rows in set (0.01 sec)

dba@192.168.111.4 : dba_test 02:25:50>create table tmp_test_bak(id int,name varchar(64),age int,primary key(id)) engine = innodb;
Query OK, 0 rows affected (0.03 sec)

dba@192.168.111.4 : dba_test 02:26:31>insert into tmp_test_bak select * from tmp_test;   ###记录成了Row模式
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

Binlog 记录图:

问题来了,我想要出来的binlog format是Statement,而不是Row。而一条insert into tb select * from ta的简单语句在Mixed模式下记录了Row模式的binlog。原因是什么?

首先确实在一些特定的情况下,Mixed会被转换成Row模式

. 当 DML 语句更新一个 NDB 表时;
. 当函数中包含 UUID() 时;
. 2 个及以上包含 AUTO_INCREMENT 字段的表被更新时;
. 执行 INSERT DELAYED 语句时;
. 用 UDF 时;
. 视图中必须要求运用 row 时,例如建立视图时使用了 UUID() 函数;

上面来自网络,有兴趣的可以自己测试测试。而对于本文中的sql,符合不了上面的条件,但binlog也记录成了Row格式。所以还是很奇怪为什么binlog格式被转换了,日常工作的时候有遇到过执行一条sql,会报一个warning:

Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT

难道因为这个导致转换的?因为上面的SQL可以重现,没有报warning,所以这个情况排除。根据经验想到了一个参数:innodb_locks_unsafe_for_binlog,看到里面讲到事务隔离级别,那就看看隔离级别的情况:

dba@192.168.111.4 : dba_test 05:46:56>select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.01 sec)

dba@192.168.111.4 : dba_test 06:36:45>select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.01 sec)

看到隔离级别是提交读,即不可重复读。把事务隔离级别设置成默认的 REPEATABLE READ:

dba@192.168.111.4 : dba_test 06:41:02>set session transaction isolation level REPEATABLE READ;                                                                              
Query OK, 0 rows affected (0.14 sec)

dba@192.168.111.4 : dba_test 06:41:42>select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

再执行测试里的SQL,发现这时候Mixed的binlog记录了Statement格式,正常了,符合预期了。难道就是这个事务隔离级别的问题引起的?在手册里发现了这句:

Note
In MySQL 5.7, when READ COMMITTED isolation level is used, or the deprecated innodb_locks_unsafe_for_binlog system variable is enabled, 
there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching 
rows are released after MySQL has evaluated the WHERE condition.

If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

展开可以看例子:

dba@192.168.111.4 : dba_test 06:49:27>select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.01 sec)

dba@192.168.111.4 : dba_test 06:49:29>create table tmp_test_bak(id int,name varchar(64),age int,primary key(id)) engine = innodb;                                                
Query OK, 0 rows affected (0.14 sec)

dba@192.168.111.4 : dba_test 06:49:34>insert into tmp_test_bak values(1,'aaa',11);
Query OK, 1 row affected (0.13 sec)

dba@192.168.111.4 : dba_test 06:49:41>insert into tmp_test_bak values(2,'bbb',22);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:49:41>insert into tmp_test_bak values(3,'ccc',33);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:49:42>insert into tmp_test_bak values(4,'ddd',44);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:49:42>insert into tmp_test_bak values(5,'eee',55);
Query OK, 1 row affected (0.13 sec)

BINLOG:
root@dba:/var/log/mysql# mysqlbinlog mysql-bin.000007 |grep "tmp_test_bak"
create table tmp_test_bak(id int,name varchar(64),age int,primary key(id)) engine = innodb
insert into tmp_test_bak values(1,'aaa',11)
insert into tmp_test_bak values(2,'bbb',22)
insert into tmp_test_bak values(3,'ccc',33)
insert into tmp_test_bak values(4,'ddd',44)
insert into tmp_test_bak values(5,'eee',55)

#############################
#############################

dba@192.168.111.4 : dba_test 06:49:43>set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)

dba@192.168.111.4 : dba_test 06:50:21>flush logs;                                                                                                                                

Query OK, 0 rows affected (0.21 sec)

dba@192.168.111.4 : dba_test 06:50:23>create table tmp_test(id int,name varchar(64),age int,primary key(id)) engine = innodb;
Query OK, 0 rows affected (0.14 sec)

dba@192.168.111.4 : dba_test 06:50:49>insert into tmp_test values(1,'aaa',11);
Query OK, 1 row affected (0.13 sec)

dba@192.168.111.4 : dba_test 06:50:54>insert into tmp_test values(2,'bbb',22);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:50:54>insert into tmp_test values(3,'ccc',33);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:50:54>insert into tmp_test values(4,'ddd',44);
Query OK, 1 row affected (0.11 sec)

dba@192.168.111.4 : dba_test 06:50:54>insert into tmp_test values(5,'eee',55);
Query OK, 1 row affected (0.12 sec)

BINLOG:
root@pubmed2:/var/log/mysql# mysqlbinlog -vv mysql-bin.000008 |grep "tmp_test"
create table tmp_test(id int,name varchar(64),age int,primary key(id)) engine = innodb
#160426 18:50:54 server id 1  end_log_pos 395     Table_map: `pubmed_data`.`tmp_test` mapped to number 44
### INSERT INTO `pubmed_data`.`tmp_test`
#160426 18:50:54 server id 1  end_log_pos 597     Table_map: `pubmed_data`.`tmp_test` mapped to number 44
### INSERT INTO `pubmed_data`.`tmp_test`
#160426 18:50:54 server id 1  end_log_pos 799     Table_map: `pubmed_data`.`tmp_test` mapped to number 44
### INSERT INTO `pubmed_data`.`tmp_test`
#160426 18:50:54 server id 1  end_log_pos 1001     Table_map: `pubmed_data`.`tmp_test` mapped to number 44
### INSERT INTO `pubmed_data`.`tmp_test`
#160426 18:50:55 server id 1  end_log_pos 1203     Table_map: `pubmed_data`.`tmp_test` mapped to number 44
### INSERT INTO `pubmed_data`.`tmp_test`
View Code

经过测试,在5.1、5.5、5.6都有这个情况,可能这个本身就不是问题。:)

总结:

      除了上面说的一些条件影响binlog的记录格式外(Statement 到 Row),事务隔离也影响这binlog的记录格式行为

posted @ 2016-04-26 19:08  jyzhou  阅读(10793)  评论(0编辑  收藏  举报