Mysql master-slave configuration-slave_exec_mode = IDEMPOTENT

Slave_exec_mode = IDEMPOTENT is a useful parameter in the MySQL replication environment: as long as you run set global slave_exec_mode = IDEMPOTENT on the slave machine, the SQL thread on the slave machine runs in the Ming and other modes, this allows the standby machine to enable replication when the insert primary key conflicts with the unique key, and when the update and delete values are not found, the replication will continue to take effect immediately, even the SQL thread of slave does not need to be restarted). However, the method of skipping errors such as SQL _slave_skip_counter = N and slave-skip-errors = N may damage the consistency between the master and slave. However, the description in the official documentation is concise. I have always wondered how slave_exec_mode = IDEMPOTENT maintains consistency when a replication error occurs-for example, whether to simply skip or overwrite a primary key conflict, today, I did an experiment under Percona 5.7 (binlog is in row format), and the experiment process is omitted. The following is a summary:

1. insert scenario
In this case, the insert into statement works the same as replace into on the slave server, but it does not convert insert into replace into for execution. There are two cases:
A. Configure MySQL to autocommit and insert into directly...
Such as insert

Insert into test set c1 = 'a', c2 = 'B ';

In this case, when the insert into statement is executed on the slave machine, if a primary key conflict occurs, it is first converted to delete before insert.

Delete from test where c1 = 'old _ value' and c2 = 'old _ value ';
Insert into test set c1 = 'a', c2 = 'B ';

If a unique key conflict with a non-primary key is encountered, it is converted to update.

Update test set c1 = 'a', c2 = 'B' where c1 = 'old _ value' and c2 = 'old _ value ';
B. When the start transaction is displayed (begin... insert into... commit ;)
Such as SQL

Begin;
......
Insert into test set c1 = 'a', c2 = 'B ';
......
Commit;
At this time, the insert into statement in begin... commit is converted to delete before insert if a primary key conflict or a unique key conflict occurs during execution on the slave machine.

Begin;
......
Delete from test where c1 = 'old _ value' and c2 = 'old _ value ';
Insert into test set c1 = 'a', c2 = 'B ';
......
Commit;
2. update scenario
When the standby machine does not have a record to be updated, this update will not be executed.

3. delete scenario
Same as the update scenario, the standby machine does nothing.

Note: the table must have a primary key when using the mode.
The "Ming" mode is not omnipotent. Apart from the inability to perform DDL operations, the errors caused by different field lengths are not. For example, a host field is char (20) the standby machine is char (10), and there is also a restriction that the table has a primary key to be valid for insert settings such: because insert and other operations are performed by primary keys to determine whether the standby machine has duplicate values and generate overwriting operations. If the table does not have a primary key, the standby machine may have more duplicate data than the host even if it has been set up.