MySQL Binlog Mixed模式记录成Row格式

zp7412

　　概念：
　　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 |+---------------+-------+　　测试语句：

http://common.cnblogs.com/images/copycode.gif

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;+----+------+------+|>　　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

http://common.cnblogs.com/images/copycode.gif

　　Binlog 记录图：

http://images2015.cnblogs.com/blog/163084/201604/163084-20160426154047580-100253863.png

　　问题来了，我想要出来的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，看到里面讲到事务隔离级别，那就看看隔离级别的情况：

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

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

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

　　再执行测试里的SQL，发现这时候Mixed的binlog记录了Statement格式，正常了，符合预期了。难道就是这个事务隔离级别的问题引起的？在手册里发现了这句：
NoteIn 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 nonmatchingrows are>
　　If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.
　　展开可以看例子：

http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif View Code

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