江北区城乡建设部网站首页,搜狗seo查询,站长网站素材网,市场推广工作内容背景#xff1a; 一个简单的主从结构#xff0c;主的binlog format是Mixed模式#xff0c;在执行一条简单的导入语句时#xff0c;通过mysqlbinlog导出发现记录的Binlog全部变成了Row的格式#xff08;明明设置的是Mixed#xff09;#xff0c;现在就说说在什么情况下Bi…背景 一个简单的主从结构主的binlog format是Mixed模式在执行一条简单的导入语句时通过mysqlbinlog导出发现记录的Binlog全部变成了Row的格式明明设置的是Mixed现在就说说在什么情况下Binlog的Mixed模式会转变记录成Row格式。 概念 binlog format有三种形式Statement、Mixed、Row具体的信息可以自行到网上搜查。 分析本文碰到的案例 查看MySQL binlog format dba192.168.111.4 : dba_test 02:33:39show variables like binlog_format%; ----------------------
| Variable_name | Value |
----------------------
| binlog_format | MIXED |
---------------------- 测试语句 dba192.168.111.4 : dba_test 02:24:14create table tmp_test(id int,name varchar(64),age int,primary key(id)) engine innodb;
Query OK, 0 rows affected (0.05 sec)dba192.168.111.4 : dba_test 02:24:23insert into tmp_test values(1,aaa,11);
Query OK, 1 row affected (0.02 sec)dba192.168.111.4 : dba_test 02:25:17insert into tmp_test values(2,bbb,22);
Query OK, 1 row affected (0.02 sec)dba192.168.111.4 : dba_test 02:25:23insert into tmp_test values(3,ccc,33);
Query OK, 1 row affected (0.01 sec)dba192.168.111.4 : dba_test 02:25:28insert into tmp_test values(4,ddd,44);
Query OK, 1 row affected (0.01 sec)dba192.168.111.4 : dba_test 02:25:34insert into tmp_test values(5,eee,55);
Query OK, 1 row affected (0.01 sec)dba192.168.111.4 : dba_test 02:25:42select * 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)dba192.168.111.4 : dba_test 02:25:50create table tmp_test_bak(id int,name varchar(64),age int,primary key(id)) engine innodb;
Query OK, 0 rows affected (0.03 sec)dba192.168.111.4 : dba_test 02:26:31insert 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看到里面讲到事务隔离级别那就看看隔离级别的情况 dba192.168.111.4 : dba_test 05:46:56select global.tx_isolation;
-----------------------
| global.tx_isolation |
-----------------------
| READ-COMMITTED |
-----------------------
1 row in set (0.01 sec)dba192.168.111.4 : dba_test 06:36:45select session.tx_isolation;
------------------------
| session.tx_isolation |
------------------------
| READ-COMMITTED |
------------------------
1 row in set (0.01 sec) 看到隔离级别是提交读即不可重复读。把事务隔离级别设置成默认的 REPEATABLE READ dba192.168.111.4 : dba_test 06:41:02set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.14 sec)dba192.168.111.4 : dba_test 06:41:42select 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. 展开可以看例子 dba192.168.111.4 : dba_test 06:49:27select session.tx_isolation;
------------------------
| session.tx_isolation |
------------------------
| REPEATABLE-READ |
------------------------
1 row in set (0.01 sec)dba192.168.111.4 : dba_test 06:49:29create table tmp_test_bak(id int,name varchar(64),age int,primary key(id)) engine innodb;
Query OK, 0 rows affected (0.14 sec)dba192.168.111.4 : dba_test 06:49:34insert into tmp_test_bak values(1,aaa,11);
Query OK, 1 row affected (0.13 sec)dba192.168.111.4 : dba_test 06:49:41insert into tmp_test_bak values(2,bbb,22);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:49:41insert into tmp_test_bak values(3,ccc,33);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:49:42insert into tmp_test_bak values(4,ddd,44);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:49:42insert into tmp_test_bak values(5,eee,55);
Query OK, 1 row affected (0.13 sec)BINLOG:
rootdba:/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)#############################
#############################dba192.168.111.4 : dba_test 06:49:43set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)dba192.168.111.4 : dba_test 06:50:21flush logs; Query OK, 0 rows affected (0.21 sec)dba192.168.111.4 : dba_test 06:50:23create table tmp_test(id int,name varchar(64),age int,primary key(id)) engine innodb;
Query OK, 0 rows affected (0.14 sec)dba192.168.111.4 : dba_test 06:50:49insert into tmp_test values(1,aaa,11);
Query OK, 1 row affected (0.13 sec)dba192.168.111.4 : dba_test 06:50:54insert into tmp_test values(2,bbb,22);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:50:54insert into tmp_test values(3,ccc,33);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:50:54insert into tmp_test values(4,ddd,44);
Query OK, 1 row affected (0.11 sec)dba192.168.111.4 : dba_test 06:50:54insert into tmp_test values(5,eee,55);
Query OK, 1 row affected (0.12 sec)BINLOG:
rootpubmed2:/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的记录格式行为。转载于:https://www.cnblogs.com/zhoujinyi/p/5436250.html
