How to choose the format when using binlog in MySQL

1. Three modes of binlog

1.Statement level mode

Every SQL statement that modifies data will be recorded in the master's bin-log. When the slave is replicating, the SQL process will parse it into the same SQL executed by the original master and execute it again.

Advantages: The advantages at the statement level first solve the disadvantages at the row level. There is no need to record the changes of each row of data, which 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 records the execution statements, in order to make these statements execute correctly on the slave side, it must also record some relevant information of each statement when it is executed, that is, context information, to ensure that all statements can get the same results when they are executed on the slave side as when they are executed on the master side. In addition, due to the rapid development of MySQL, many new features have been added, which has brought considerable challenges to MySQL replication. Naturally, the more complex the content involved in replication, the more likely bugs will occur. At the statement level, many situations have been found to cause MySQL replication problems. These problems mainly occur when certain functions or features are used when modifying data. For example, sleep() cannot be replicated correctly in some versions.

2. Rowlevel mode

The log will record each row of data that has been modified, and then modify the same data on the slave side.

Advantages: Bin-log does not need to record the context-related information of the executed SQL statement. It only needs to record which record has been modified and how it has been modified. Therefore, the content of the row level log will clearly record the details of each row of data modification. In addition, there will be no problem that the stored procedure, function, or trigger call and trigger cannot be correctly replicated in certain situations.

Disadvantages: At the row level, when all executed statements are recorded in the log, they will be recorded as modifications to each row. This may generate a large amount of log content. For example, there is an update statement like this: update product set owner_member_id='d' where owner_member_id='a'. After execution, the log does not record the event corresponding to this update statement (MySQL records bin-log logs in the form of events), but the changes of each record updated by this statement. In this way, many events of many records being updated are recorded. Naturally, the amount of bin-log logs will be very large.

3. Mixed mode

In fact, it is a combination of the first two modes. In mixed mode, MySQL will distinguish the log format for each specific SQL statement executed, that is, 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 new version of MySQL has optimized the row level mode. Not all modifications are recorded at the row level. For example, when the table structure changes, it will be recorded in statement mode. If the SQL statement is indeed an update or delete statement that modifies data, all row changes will still be recorded.

2. What format should we choose when using binlog?

From the above introduction, we know that binlog_format=STATEMENT can save IO and speed up synchronization in some scenarios. However, for transaction engines such as InnoDB, when the isolation levels are READ-COMMITTED and READ-UNCOMMITTED or the parameter innodb_locks_unsafe_for_binlog is ON, writing under binlog_format=statement is prohibited. At the same time, binlog_format=mixed, which is the default writing statement format for non-transaction engines and other isolation levels, will only record the row format.

> 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 statement format binlog be used in READ-COMMITTED (RC) and READ-UNCOMMITTED modes? This is because when a statement is executed within a transaction, it can see data committed or being written by other transactions. After the transaction is committed, the binlog is written. If you then replay it on the slave, the data you see will not correspond to when it was written on the master.

For example:

There is table:

+------+------+
| a | b |
+------+------+
| 10 | 2 |
| 20 | 1 |
+------+------+

We do the following:

1. Session1 performs an update in the transaction, UPDATE t1 SET a=11 where b=2; a record in row (10,2) meets the condition, but it is not committed.
2. Session2 also performs an update operation, updates row (20,1) to (20,2) and commits the update.
3. Then the previous session1 commits the update to row (10,2).

If the binlog is recorded in Statement format, when the slave replays the data, the update in session2 is replayed first because it was submitted first, and the row (20,1) is updated to (20,2). Then replay the statement UPDATE t1 SET a=11 where b=2; in session 1, and the two rows (10,2) and (20,2) will be updated to (11,2). This results in the master database behavior being (11, 2), (20,2) and the slave end being (11,2), (11, 2).

3. Problem Analysis

The above is explained through a specific example. The fundamental reason is that the RC transaction isolation level does not meet the transaction serialization execution requirements and does not solve the non-repeatable and phantom reads.

For Repetable-Read and Serializable isolation levels, it does not matter, the Statement format is recorded. This is because for RR and Serializable, repeatable read is guaranteed. When performing an update, in addition to locking the corresponding row, a GAP Lock will be added when a row that meets the conditions may be inserted. In the above case, when session1 updates the row with b = 2, all rows and ranges will be locked, so session2 will need to wait when updating. From the perspective of isolation level, Serializable satisfies the serialization of transactions, so the binlog serial record transaction statement format is acceptable. At the same time, InnoDB's RR isolation level has actually solved non-repeatable reads and phantom reads, and meets the transaction isolation requirements of the ANSI SQL standard.

The READ-COMMITTED and READ-UNCOMMITTED binlog_format restrictions apply to all transaction engines.

4. Expand content

Can the binlog record statement format be guaranteed under InnoDB RR and Serializable isolation levels? Not necessarily. Innodb, there is a parameter innodb_locks_unsafe_for_binlog to control GAP Lock. The default setting is 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)

That is, RR level and above will have GAP Lock in addition to row lock. However, if this parameter is set to ON, GAP Lock will not be added to the current read, that is, the current read that requires Next-key lock at the RR isolation level degenerates to READ-COMMITTED. Therefore, if this parameter is set to ON, the correctness of the slave data cannot be guaranteed even if the transaction isolation level used is Repetable-Read.

V. Conclusion

For online business, if you use a transaction engine such as InnoDB, do not set binlog_format to STATEMENT unless you want to ensure writing at the RR isolation level or above. Otherwise, the business will not be able to be written. For binlog_format in Mixed mode, these transaction engines below the RR isolation level must also write ROW events.

This is the end of this article about how to choose the format when using binlog in MySQL. For more information about how to choose the format when using binlog in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!