Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the three forms of MySQL binlog format. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article in the three forms of MySQL binlog format can bring you some practical help.

Analysis (cases encountered in this article):

View MySQL binlog format

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

Test statement:

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 (1maxiaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Query OK, 1 row affected (0.02 sec)

Dba@192.168.111.4: dba_test 02:25:17 > insert into tmp_test values (2mcmcmbbbmpm22)

Query OK, 1 row affected (0.02 sec)

Dba@192.168.111.4: dba_test 02:25:23 > insert into tmp_test values (3m ccccturnal 33)

Query OK, 1 row affected (0.01sec)

Dba@192.168.111.4: dba_test 02:25:28 > insert into tmp_test values (4 recordings DDD Phone44)

Query OK, 1 row affected (0.01sec)

Dba@192.168.111.4: dba_test 02:25:34 > insert into tmp_test values.

Query OK, 1 row affected (0.01sec)

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 (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; # recorded as Row mode Query OK, 5 rows affected (0.03 sec)

Records: 5 Duplicates: 0 Warnings: 0

Binlog record chart:

The problem is, the binlog format I want out is Statement, not Row. A simple statement of insert into tb select * from ta records the binlog of Row mode in Mixed mode. What's the reason?

First of all, it is true that Mixed will be converted to Row mode in certain cases:

. When a DML statement updates an NDB table

. When the function contains UUID ()

. When 2 or more tables containing AUTO_INCREMENT fields are updated

. When executing an INSERT DELAYED statement

. When using UDF

. When row must be used in a view, for example, the UUID () function is used when creating a view

The above comes from the network, if you are interested, you can test it yourself. For the sql in this article, the above conditions are not met, but the binlog is also recorded in Row format. So it's still strange why the binlog format has been converted. When you encounter the execution of a sql in your daily work, you will report a warning:

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

Is it because of this that leads to the conversion? Because the above SQL can be reproduced, the warning is not reported, so this situation is excluded. Based on experience, I came up with a parameter: innodb_locks_unsafe_for_binlog. If you see the transaction isolation level in it, take a look at the isolation level:

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 (sec)

See that the isolation level is committed read, that is, it cannot be read repeatedly. Set the transaction isolation level to the default 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)

Then execute the SQL in the test, and find that the binlog of Mixed records the Statement format at this time, which is normal and meets the expectations. Is it caused by the problem of transaction isolation level? I found this sentence in the manual:

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.

To expand, you can see an example:

View Code

After testing, this situation can be found in 5.1, 5.5 and 5.6, which may not be a problem in itself. :)

MySQL binlog format three forms will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.