背景:
一個簡單的主從結構,主的binlog format是Mixed模式,在執行一條簡單的導入語句時,通過mysqlbinlog導出發現記錄的Binlog全部變成了Row的格式(明明設定的是Mixed),現在就說說在什麼情況下Binlog的Mixed模式會轉變記錄成Row格式。
概念:
binlog format有三種形式:Statement、Mixed、Row,具體的資訊可以自行到網上搜查。
分析(本文碰到的案例):
檢視MySQL binlog format
[email protected] : dba_test 02:33:39>show variables like 'binlog_format%'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
測試語句:
[email protected] : 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)
[email protected] : dba_test 02:24:23>insert into tmp_test values(1,'aaa',11);
Query OK, 1 row affected (0.02 sec)
[email protected] : dba_test 02:25:17>insert into tmp_test values(2,'bbb',22);
Query OK, 1 row affected (0.02 sec)
[email protected] : dba_test 02:25:23>insert into tmp_test values(3,'ccc',33);
Query OK, 1 row affected (0.01 sec)
[email protected] : dba_test 02:25:28>insert into tmp_test values(4,'ddd',44);
Query OK, 1 row affected (0.01 sec)
[email protected] : dba_test 02:25:34>insert into tmp_test values(5,'eee',55);
Query OK, 1 row affected (0.01 sec)
[email protected] : 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)
[email protected] : 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)
[email protected] : 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,看到裡面講到事務隔離級别,那就看看隔離級别的情況:
[email protected] : dba_test 05:46:56>select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.01 sec)
[email protected] : dba_test 06:36:45>select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED |
+------------------------+
1 row in set (0.01 sec)
看到隔離級别是送出讀,即不可重複讀。把事務隔離級别設定成預設的 REPEATABLE READ:
[email protected] : dba_test 06:41:02>set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.14 sec)
[email protected] : 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.
展開可以看例子:
[email protected] : dba_test 06:49:27>select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.01 sec)
[email protected] : 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)
[email protected] : dba_test 06:49:34>insert into tmp_test_bak values(1,'aaa',11);
Query OK, 1 row affected (0.13 sec)
[email protected] : dba_test 06:49:41>insert into tmp_test_bak values(2,'bbb',22);
Query OK, 1 row affected (0.11 sec)
[email protected] : dba_test 06:49:41>insert into tmp_test_bak values(3,'ccc',33);
Query OK, 1 row affected (0.11 sec)
[email protected] : dba_test 06:49:42>insert into tmp_test_bak values(4,'ddd',44);
Query OK, 1 row affected (0.11 sec)
[email protected] : 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)
#############################
#############################
[email protected] : dba_test 06:49:43>set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
[email protected] : dba_test 06:50:21>flush logs;
Query OK, 0 rows affected (0.21 sec)
[email protected] : 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)
[email protected] : dba_test 06:50:49>insert into tmp_test values(1,'aaa',11);
Query OK, 1 row affected (0.13 sec)
[email protected] : dba_test 06:50:54>insert into tmp_test values(2,'bbb',22);
Query OK, 1 row affected (0.11 sec)
[email protected] : dba_test 06:50:54>insert into tmp_test values(3,'ccc',33);
Query OK, 1 row affected (0.11 sec)
[email protected] : dba_test 06:50:54>insert into tmp_test values(4,'ddd',44);
Query OK, 1 row affected (0.11 sec)
[email protected] : 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的記錄格式行為。
~~~~~~~~~~~~~~~
萬物之中,希望至美
~~~~~~~~~~~~~~~