MySQL binlog format三種形式介紹

本文主要給大家簡單講講MySQL binlog format三種形式，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL binlog format三種形式這篇文章可以給大家帶來一些實際幫助。

分析（本文碰到的案例）：

查看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格式，正常了，符合預期了。難道就是這個事務隔離級別的問題引起的？在手冊里發現了這句：

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 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.

展開可以看例子：

 View Code

經過測試，在5.1、5.5、5.6都有這個情況，可能這個本身就不是問題。:)

MySQL binlog format三種形式就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。