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

This article mainly describes how to use binlog in MySQL binlog format selection, the article is very detailed, has a certain reference value, interested friends must read!

1. three modes of binlog 1. statement level mode

Every sql that modifies data is recorded in master's bin-log. When slave copies sql, the sql process resolves to the same sql that was executed on the master side and executes it again. Advantages: The advantages of statement level, first of all, is to solve the shortcomings of row level, do not need to record the changes of each row of data, reduce the bin-log volume, save io, improve performance. Because it only needs to record the details of the statement executed on master, and the context in which the statement was executed. Disadvantages: Since it is a recorded execution statement, in order for these statements to be executed correctly on the slave side, he must also record some relevant information about each statement at the time of execution, that is, context information, to ensure that all statements are executed on the slave side. The same result can be obtained when executing on the master side. In addition, because mysql is now developing rapidly, many new features have been added, which makes mysql replication encounter no small challenge. The more complex the content is involved in natural replication, the easier it is for bugs to appear. At the statement level, there are many cases that have been discovered that cause mysql replication problems, mainly when certain functions or functions are used when modifying data, such as sleep(), which cannot be copied correctly in some versions.

2. rowlevel mode

Log will be recorded in the form of each line of data is modified, and then in the slave side to modify the same data Advantages: bin-log can not record the execution of sql statement context-related information, only need to record which record has been modified, modified into what kind. So the row level log clearly records the details of each line of data modification. And there are no stored procedures, or functions, and trigger calls and triggers that cannot be copied correctly in certain cases. Disadvantages: Row level, all the executed statements when recorded in the log, will be recorded in each line of records, which may produce a large number of log content, such as an update statement: update product set owner_member_id='d' where owner_member_id='a', after execution, the log records events that are not corresponding to this update statement (mysql records bin-log logs as events), but changes to each record updated by this statement, thus recording many events with many records updated. Naturally, bin-log logs can be large.

3. Mixed mode

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

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 a transaction engine such as InnoDB, when READ-COMMITTED, READ-UNCOMMITTED isolation levels or the parameter innodb_locks_unsafe_for_binlog is ON, binlog_format=statement is prohibited. At the same time, binlog_format=mixed, which writes statement format by default for non-transaction engines and other isolation levels, will only record 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: 3258621899End_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: 3258621899End_log_pos: 407 Info: BEGIN*************************** 5. row *************************** Log_name: mysql-bin.000002 Pos: 407 Event_type: Table_map Server_id: 3258621899End_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: 3258621899End_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: 3258621899End_log_pos: 529 Info: COMMIT /* xid=18422 */copy code

Why can't I use binlog in READ-COMMITTED(RC) or READ-UNCOMMITTED? This is because statements executing in transactions can see data committed or being written by other transactions. After the transaction is committed, binlog is written, and then playback from the slave library will see that the data does not correspond to the data written to the master library. For example, there are tables:

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

We do the following:

Session1 updates in the transaction, UPDATE t1 SET a=11 where b=2; a record with row (10,2) that meets the condition is not committed. Session2 also updates line (20,1) to (20,2) and commits. Then the previous sesssion1 commits an update to row (10,2).

If the binlog is recorded in Statement format, during slave playback, the update in session2 will be played back first because it was committed first, updating row (20,1) to (20,2). Subsequent playback of the statement UPDATE t1 SET a=11 where b=2; of session1 will update the (10,2) and (20,2) lines (11,2). This results in the main library behavior (11, 2), (20,2), with slave ends (11,2), (11, 2).

III. Problem analysis

The above is illustrated by a specific example. The essential reason is that the RC transaction isolation level does not meet the transaction serialization execution requirements and does not address non-repeatable and phantom reads.

For Repeatable-Read and Serializable isolation levels it doesn't matter, Statement format records. This is because for RR and Serializable, repeatable reads are guaranteed, and GAP Lock is added when updating the corresponding row in addition to locking the corresponding row. When updating the above case, session1 will lock all rows and ranges when updating the row b =2, so session2 needs to wait when updating. Serializable satisfies the serialization of transactions from the perspective of isolation level, so binlog serial record transaction statement format is OK. At the same time, the RR isolation level of InnoDB has actually solved the problem of non-repeatable reads and phantom reads, meeting the transaction isolation requirements of ANSI SQL standard.

READ-COMMITTED, READ-UNCOMMITTED binlog_format restrictions can be said to apply to all transaction engines.

IV. Expand content

For InnoDB RR and Serializable isolation levels, can binlog record Statement format be guaranteed? Not necessarily. There is a parameter innodb_locks_unsafe_for_binlog in Innodb to control GAP Lock, which defaults to 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) Copy Code

RR level and above in addition to the row lock will also add GAP 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 needs to be added with Next-key lock at RR isolation level will degenerate to READ-COMMITTED. Therefore, if this parameter is set to ON, even if the transaction isolation level used is Repeatable-Read, the correctness of the slave data cannot be guaranteed.

V. Summary

For online services, if transaction engines such as InnoDB are used, unless RR and above isolation levels are guaranteed to be written, binlog_format must not be set to STATEMENT, otherwise the service cannot be written. For binlog_format Mixed mode, these transaction engines below RR isolation level must also write ROW event.

The above is how to use binlog in MySQL binlog format selection of all the content, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.