"MySQL" "Replication" uses Slave_exec_mode to handle the 1062 and 1032 errors that occur during replication

Background:

? Today, say brother in the group asked the master-slave between the 1032 error, after using the Pt-slave-restart jump after another 1062 errors, how to quickly deal with.

Problem Resolution:

? 1032 Error: The Binlog passed by the main library contains statements that delete some data, but some or all of the data in the library has been manually deleted in advance, or it does not exist at all.

? 1062 Error: The Binlog passed by the main library contains statements that update (or insert) some data, but some of the data already exists from the library, or the entrance to the unique index is occupied by other data.

? The problem is that a transaction is replayed as an atomic unit when the Binlog is replayed. Just as the atom consists of three quarks, a transaction typically consists of several event elements. An event is treated as a statement.

? If the main library comes up with a transaction that contains the deletion of three rows of data (R1,R2,R3), there are only two corresponding rows (R1,R2) from the library.

begin;delete from t1 where row=r3; #假设row列为唯一性索引delete from t1 where row=r2;delete from t1 where row=r1;commit;

? Then the 1032 delete a not exist row error is reported from the library when the first rule is executed. Use Pt-slave-restart --error-numbers=1032 will jump over this entire transaction, resulting in the subsequent R1,R2 rows being deleted. Next time, if it's coming from the main library,

begin;insert into t1(row) values(r1,r2)commit;

? The 1062 duplicate entry error will certainly be reported when inserting from the library.

Problem handling: Method One:

? Master-Slave data synchronization is performed using Pt-table-sync, but this data synchronization can lead to more deadly data confusion in the case of a double-master condition or a constant update of the main-library-related tables.

Method Two:

? Use the Slave_exec_mode parameter.

? First look at the official manual description:

parameter name:	Slave_exec_mode
Variable range:	Global
Dynamic modification:	Is
Default value:	NDB cluster default idempotent, other modes strict
Valid values:	Strict/idempotent
How to set it up:	SET GLOBAL slave_exec_mode = ' idempotent '

? Controls how a Slave thread resolves conflicts and errors during replication. Idempotent mode
Causes suppression of Duplicate-key and no-key-found errors; STRICT means no such suppression
takes place.
Idempotent mode is intended for use in multi-master replication, circular replication, and some
Other special replication scenarios for NDB Cluster replication

? This parameter was originally introduced as a NDB mode, and later in multiple and circular replication. The primary effect is that when slave_exec_mode= ' idempotent, slave ignores replication errors that occur when a duplicate unique index node is encountered at the time of insertion and no corresponding record is found at the time of deletion, namely 1062 and 1032. However, when you receive an attempt to update a record that does not exist from the main library, you will still get an error of 1032.

? The problem is simple and should be done in the following steps:

stop slave;SET GLOBAL  slave_exec_mode = ‘IDEMPOTENT‘start slave;

? Again show slave status\G , you should see that the replication SQL thread from the library has returned to normal.

? However, this is a non-conventional means, after the execution and the master-slave agreement, should take time to verify the data. and is not recommended to be opened directly as default parameters.

Report:

5.7.0 can then call idempotent as the Mysqld startup parameter, which is: mysqld --defaults-file =my.cnf --indempotent& of course, it can also be written to my.cnf.

? --idempotent
Tell the MySQL Server to use idempotent mode while processing updates; This causes suppression
of any duplicate-key or key-not-found errors, the server encounters in the current session while
Processing updates. This option may prove useful whenever it's desirable or necessary to replay
One or more binary logs to a MySQL Server which could not contain all of the data to which the logs
Refer.
The scope of effect for this option includes the current Mysqlbinlog client and session only.
The--idempotent option is introduced in MySQL 5.7.0.

5.7.1 can introduce session-level version Rbr_exec_mode for this parameter later, only for the current session, and the row replication mode is restricted.

? Rbr_exec_mode
This variable switches the server between idempotent mode and STRICT mode. Idempotent
Mode causes suppression of Duplicate-key and no-key-found errors. This mode was useful when
Replaying a row-based binary log on a server this causes conflicts with existing data. Mysqlbinlog
uses this mode is set the--idempotent option by writing the following to the output:
SET SESSION rbr_exec_mode=idempotent;

"MySQL" "Replication" uses Slave_exec_mode to handle the 1062 and 1032 errors that occur during replication