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

[MySQL] [copy] use the slave_exec_mode parameter to handle the background of 1062 and 1032 errors that occur during replication:

​ Today, Brother Zhang asked the group about the 1032 error between master and slave, and after skipping with pt-slave-restart, there was another 1062 error. How to deal with it quickly.

Problem resolution:

​ 1032 error: the binlog passed by the master library contains statements to delete some data, but some or all of the data in the slave library is manually deleted in advance, or does not exist at all.

​ 1062 error: the binlog passed by the master library contains statements to update (or insert) some data, but some of the data already exists in the slave database, or other data occupies the entry of the unique index.

The problem with ​ is that binlog playback is performed as an atomic unit of a transaction. Just as an atom is made up of three quarks, a transaction generally consists of several event. An event is treated as a statement.

​ if a transaction containing the deletion of three rows of data (R1 Personality R2) is passed from the master library, but there are only two corresponding rows in the slave library (R1 Personality R2).

Begin;delete from T1 where row=r3; # assumes that row is listed as a unique index delete from T1 where row=r2;delete from T1 where row=r1;commit

​ then when the first item is executed, the slave will report a 1032 delete a not exist row error. Using Pt-slave-restart-- error-numbers=1032 will skip the whole transaction, resulting in the. Next time, if it comes from the main library,

Begin;insert into T1 (row) values (R1 dint R2) commit

​ will definitely report a 1062 duplicate entry error when performing an insert from the library.

Problem handling: method 1:

​ uses Pt-table-sync for master-slave data synchronization, but in the case of double master conditions or continuous updates of master database-related tables, such data synchronization can lead to fatal data confusion.

Method 2:

​ uses the slave_exec_mode parameter.

​ first take a look at the official manual description:

Parameter name: slave_exec_mode variable range: global dynamic modification: is the default value: NDB cluster default IDEMPOTENT, other mode STRICT valid values: STRICT/IDEMPOTENT setting method: 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 first introduced in NDB mode, and later there are opportunities to exert its talents in both multi-master and circular replication. The main function is that in slave_exec_mode='IDEMPOTENT, slave ignores replication errors of 1062 and 1032 when inserting unique index nodes and deleting unfound corresponding records. however, when the slave library receives an attempt to update a record that does not exist on its own, it will still report an error of 1032.

The ​ problem is simple at this point, and the following steps should be taken:

Stop slave;SET GLOBAL slave_exec_mode = 'IDEMPOTENT'start slave

​ show slave status\ G again should see that the replication SQL thread from the library has returned to normal.

​ but this is an unconventional method after all, after the execution and the master-slave consistency, we should make time for data verification. And it is not recommended to open it directly as the default parameter.

Attached:

After 5.7.0, idempotent can be called as a mysqld startup parameter, that is, mysqld-- defaults-file = my.cnf-- indempotent&, of course, 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 that the server encounters in the current session while

Processing updates. This option may prove useful whenever it is desirable or necessary to replay

One or more binary logs to a MySQL Server which may 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 was introduced in MySQL 5.7.0.

After 5.7.1, a session-level version of rbr_exec_mode with this parameter can be introduced, which takes effect only for the current session, and restricts line replication mode.

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 is useful when

Replaying a row-based binary log on a server that causes conflicts with existing data. Mysqlbinlog

Uses this mode when you set the-idempotent option by writing the following to the output:

SET SESSION RBR_EXEC_MODE=IDEMPOTENT

Pt_slave_restart and skip_slave_errors skip different results.

Skip_slave_errors skips only problematic statements when skipping

And pt_slave_restart skips the whole transaction.

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.