Selection of binlog format when using binlog in MySQL

mysql tutorial column introduces the choice of binlog format when using binlog.

1. Three modes of binlog

1.statement level mode

Every sql that modifies data will be recorded to the master in the bin-log. When the slave copies, the sql process will parse it into the same sql that was executed on the original master side and execute it again. Advantages: The advantages of statement level are that it first solves the shortcomings of row level. It does not need to record the changes of each row of data, reduces the amount of bin-log logs, saves IO, and improves performance. Because he only needs to record the details of the statements executed on the master and the context information when the statements are executed. Disadvantages: Since it is a recorded execution statement, in order for these statements to be executed correctly on the slave side, it must also record some relevant information when each statement is executed, that is, context information, to ensure that all statements are executed correctly. When executed on the slave side, the same result can be obtained as when executed on the master side. In addition, because mysql is developing rapidly now, many new functions have been added, which makes the replication of mysql encounter a lot of challenges. Naturally, the more complex content is involved in replication, the easier it is for bugs to appear. At the statement level, many situations have been discovered that will cause MySQL replication problems. This mainly occurs when certain functions or functions are used when modifying data. For example, sleep() cannot be used in some versions. Copied correctly.

2.rowlevel mode

The log will record the modified form of each row of data, and then modify the same data on the slave side. Advantages: The bin-log does not need to record the context-related information of the executed SQL statement. It only needs to record which record was modified and what the modification was. Therefore, the content of the row level log will clearly record the details of each row of data modification. And there will be no problem that stored procedures, functions, and trigger calls and triggers cannot be copied correctly under certain circumstances. Disadvantages: At row level, when all executed statements are recorded in the log, they will be recorded as modifications recorded in each row, which may generate a large amount of log content. For example, there is such an update statement: update product set owner_member_id= 'd' where owner_member_id='a', after execution, what is recorded in the log is not the event corresponding to this update statement (mysql records the bin-log log in the form of events), but every event updated by this statement. The changes of one record are recorded as many events in which many records are updated. Naturally, the amount of bin-log logs will be large.

3.Mixed mode

is actually a combination of the first two modes. In mixed mode, mysql will distinguish the log form to be recorded according to each specific SQL statement executed. That is to choose between statement and row. The statement level in the new version is still the same as before, only the executed statements are recorded. The row level mode has been optimized in the new version of mysql. Not all modifications will be recorded in row level. For example, when table structure changes are encountered, they will be recorded in statement mode. If the sql statement is indeed update or For statements such as delete that modify data, all row changes will still be recorded.

2. What format should we choose when using binlog?

Through the above introduction, we know that binlog_format is STATEMENT, which can save IO and speed up synchronization in some scenarios, but for InnoDB, The transaction engine, when the READ-COMMITTED, READ-UNCOMMITTED isolation level or the parameter innodb_locks_unsafe_for_binlog is ON, prohibits writing under binlog_format=statement. At the same time, binlog_format=mixed is the default writing mode for statement format for non-transaction engines and other isolation levels. Only row format will be recorded.

> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

> create table t(c1 int) engine=innodb;

> set binlog_format=statement;

> insert into t values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

> set binlog_format='mixed';

> show binlog events in 'mysql-bin.000004'\G
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 287
 Event_type: Gtid
  Server_id: 3258621899
End_log_pos: 335
       Info: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 335
 Event_type: Query
  Server_id: 3258621899
End_log_pos: 407
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 407
 Event_type: Table_map
  Server_id: 3258621899
End_log_pos: 452
       Info: table_id: 124 (test.t)
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 452
 Event_type: Write_rows_v1
  Server_id: 3258621899
End_log_pos: 498
       Info: table_id: 124 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql-bin.000002
        Pos: 498
 Event_type: Xid
  Server_id: 3258621899
End_log_pos: 529
       Info: COMMIT /* xid=18422 */复制代码

Why can’t the statement format binlog be used under READ-COMMITTED (RC) and READ-UNCOMMITTED? This is because when a statement is executed in a transaction, it can see data submitted or being written by other transactions. After the transaction is committed, the binlog is written and then played back from the slave library. The data you will see will not correspond to the data written to the main library. For example: There is a table:

+------+------+
| a    | b    |
+------+------+
|   10 |    2 |
|   20 |    1 |
+------+------+复制代码

We do the following operations:

1. session1 updates in the transaction, UPDATE t1 SET a=11 where b=2; satisfies the conditions There is a record in row (10,2), which has not been submitted.
2. session2 also performs an update operation, updates row (20,1) to (20,2) and submits it.
3. Then the previous session1 submits the update to row (10,2).

If the binlog uses Statement format recording, during slave playback, the update in session2 will be played back first because it was submitted first, and line (20,1) will be updated to (20,2). Then play back the statement of session1 UPDATE t1 SET a=11 where b=2;The statement will update the two lines (10,2) and (20,2) to (11,2). This results in the main library behavior (11, 2), (20,2), and the slave side is (11,2), (11, 2).

三、问题分析

上面是通过一个具体的例子说明。本质原因是RC事务隔离级别并不满足事务串行化执行要求，没有解决不可重复和幻象读。

对于Repetable-Read和Serializable隔离级别就没关系，Statement格式记录。这是因为对于RR和Serializable，会保证可重复读，在执行更新时候除了锁定对应行还会在可能插入满足条件行的时候加GAP Lock。上述case更新时，session1更新b =2的行时，会把所有行和范围都锁住，这样session2在更新的时候就需要等待。从隔离级别的角度看Serializable满足事务的串行化，因此binlog串行记录事务statement格式是可以的。同时InnoDB的RR隔离级别实际已经解决了不可重复读和幻象读，满足了ANSI SQL标准的事务隔离性要求。

READ-COMMITTED、READ-UNCOMMITTED的binlog_format限制可以说对于所有事务引擎都适用。

四、拓展内容

对于InnoDB RR和Serializable隔离级别下就一定能保证binlog记录Statement格式么？也不一定。在Innodb中存在参数innodb_locks_unsafe_for_binlog控制GAP Lock，该参数默认为OFF：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.01 sec)复制代码

即RR级别及以上除了行锁还会加GAP Lock。但如果该参数设置为ON，对于当前读就不会加GAP Lock，即在RR隔离级别下需要加Next-key lock的当前读蜕化为READ-COMMITTED。所以如果此参数设置为ON时即便使用的事务隔离级别为Repetable-Read也不能保证从库数据的正确性。

五、总结

对于线上业务，如果使用InnoDB等事务引擎，除非保证RR及以上隔离级别的写入，一定不要设置为binlog_format为STATEMENT，否则业务就无法写入了。而对于binlog_format为Mixed模式，RR隔离级别以下这些事务引擎也一定写入的是ROW event。

更多相关免费学习推荐：mysql教程(视频)

The above is the detailed content of Selection of binlog format when using binlog in MySQL. For more information, please follow other related articles on the PHP Chinese website!