How to avoid data loop conflicts when MySQL is configured with dual masters

I wonder if you have ever thought about this question? If dual-active is configured, how to avoid data loop conflicts? This is one of the core design ideas of the active-active data design.

If the main database triggers a SQL statement:

insert into test_data(name) values('aa');

Then Master1 generates binlog, pushes data changes to Master2, generates relay log on Master2, and then hands it over to SQL thread for change replay. The reverse process is similar. The whole process can be described as follows.

If Master2 consumes the data from relay, then binlog will be generated (log_slave_updates is enabled by default). The binlog generated at this time will continue to be pushed to Master1 for consumption, and then pushed back and forth. A set of insert statements will be endless. Obviously, this design is unreasonable, and MySQL will certainly not do this.

So the key part of the question is: Did Master2 push the previous binlog to Master1?

a) If it is pushed, how does Master1 filter it to avoid an infinite loop?

b) If there is no push, how does Master2 filter

If we want to understand this process, we need to simulate the test and check the binlog status during the data flow. You can refer to this process.

1) Master1's binlog

2) Master2's relay log

3) Master's binlog

A master-slave environment was quickly deployed, and then by adding change master to, a dual-master environment for testing was quickly built.

In order to see as complete binlog event information as possible, we turn on the parameter binlog_rows_query_log_events

Trigger statement on Master1:

insert into test_data(name) values('gg');

The obtained binlog events are as follows, and the relevant SQL statements can be clearly seen.

On the Master2 side, we check the binlog status. When binlog_rows_query_log_events is enabled, we can see that there are obviously fewer events: Rows_query.

What needs to be considered at this point is whether the offset has changed in this process. From the binlog generated by Master1 to the Master's relay log, if parsed through mysqlbinlog, the offsets obtained are exactly the same. After Master2 consumes, relevant binlog information is generated.

The key to the problem lies here. In Maser2, the source of the data is marked by Server_id, so it is called the end point of the entire data flow here, which means that when the data is copied, it is filtered according to server_id, and each Master end will only transmit its own related binlog information.

If you look at it from this perspective, one of the reasons why MySQL is so important about server_id in replication is based on this.

If we look at offset-based asynchronous replication from another perspective, we can actually get similar information.

This is the binlog detail after Master1 triggers the insert statement.

This is the binlog details after Master2 receives real-time data.

In fact, there is still a problem here. That is, in offset mode, if a data change operation is lost in Master2, there is no way to backtrack.

The GTID mode can uniquely identify global transactions, so even if the operation is applied repeatedly, even if it is a DDL statement, the number of rows affected by the operation is 0.

We reapply an operation that has already been executed to see whether MySQL will automatically discard such operations.

mysql> SET @@SESSION.GTID_NEXT= '6fb744dd-05dd-11ea-ada7-52540043a8b5:6';

Query OK, 0 rows affected (0.00 sec)

mysql> use `test`; create table test_data (id int primary key auto_increment,name varchar(30));

Database changed

Query OK, 0 rows affected (0.00 sec)

Checking show binlog events it is found that this process does not generate additional binlogs.

So based on this, we have basically clarified a design idea for the data loop solution, which is how to enable MySQL to identify the transaction data that has been applied. I think GTID is an answer, and distributed ID is not needed. This is the internal processing mechanism of MySQL, and it is a method that MySQL can recognize.

The above is the details of how to avoid data loop conflicts when MySQL is configured with dual masters. For more information on how to avoid data loop conflicts in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!