The role of MySQL Slave_exec_mode parameters

Slave_exec_mode=idempotent is a useful parameter in the MySQL replication environment: As long as the set global slave_exec_mode=idempotent is running on the standby, the SQL thread of the standby is running in the Nether mode , you can let the standby in the Insert primary key, unique key conflict, update, delete value is not found when the error occurs, keep on copying and maintain the slave (immediately effective, even the SQL thread is not restarted yo), and similar to sql_slave_skip_counter= Brute-skipping error methods such as n and slave-skip-errors = n can disrupt primary and standby consistency.

Note: The table must have a primary key when using the Nether mode

Hades and other modes are not omnipotent, in addition to the DDL operation of the Nether, the length of the field caused by the error is not a ghost, etc. (such as the Host a field is char (20) and the standby machine is char (10)), there is a restriction that the table has a primary key to insert the ghost and other settings are valid: Because the insert of the nether behavior is through the primary key to determine whether the standby has a duplicate value to produce a overwrite operation, if the table does not have a primary key, the standby machine even if set the ghost and so may be more than the host duplication of data.

For the Slave_exec_mode parameter, it can skip errors of 1062 and 1032, and does not affect normal data execution in the same transaction. If you have multiple SQL-composed transactions, you can skip the problematic event.

In addition, in the Gtid mode of replication, Sql_slave_skip_counter is not supported, the mode of replication can be self-testing.

The main problem encountered in master-slave replication is that 1062 primary key repeats

1032 errors in master-slave replication generally mean that the data to be changed does not exist

Encountered error 1032 or 1062 of the processing mode:

So the methods for handling this error are:

1:skip_slavesql_slave_skip_counter

Stop slave; Query OK, 0 rows Affected (0.00 sec)

Set global sql_slave_skip_counter=[1-4];

Query OK, 0 rows Affected (0.00 sec)

Start slave;

Query OK, 0 rows Affected (0.00 sec)

2: Specify slave-skip-errors=1062 in config file (restart required)

These 2 methods allow replication to return to normal, but will make the master-slave data inconsistent (use sparingly), and if more than one statement in a transaction, the other statements will be lost.

And the 2nd method also needs to restart the database, when the Slave_exec_mode parameter comes in handy. To set this parameter from the library:

The Slave_exec_mode parameter can be used to automatically handle synchronous replication errors:

# Execute on slave

Set global slave_exec_mode= ' idempotent '; Idempotent mode (default is strict strict mode)

Stop slave;

Start slave;

The role of Slave_exec_mode and Slave_skip_errors is the same, but Slave_skip_errors does not support dynamic modification and must restart MySQL to take effect, so Slave_exec_mode is recommended.

For the Slave_exec_mode parameter, it can skip errors of 1062 and 1032, and does not affect normal data execution in the same transaction. If you have multiple SQL-composed transactions, you can skip the problematic event.

INSERT into x values (1), (4), (5); #其中从库有id =4 this record.

BEGIN;

INSERT into X SELECT 4;

DELETE from x WHERE id = 2; #从库没有这条记录

INSERT into X SELECT 5;

COMMIT;

#!/bin/bash

# Description: Auto Skip master-slave copy Error (Error code 1023, 1062), this script executes on slave

#

User= ' Root '

pass= ' Root '

Port= ' 3306 '

ip= ' 172.16.10.12 '

Mysqlcli= '/usr/local/mysql/bin/mysql '

status=$ ($MYSQLCLI-u$user-p$pass-h $IP-e ' show slave status\g ' |sed-n ' 13p ' |awk-f ": ' {print $} ')

if [[$STATUS! = ' YES ']];then

$MYSQLCLI-u$user-p$pass-h $IP-E "SET GLOBAL slave_exec_mode= ' idempotent ';"

$MYSQLCLI-u$user-p$pass-h $IP-E "stop Slave";

$MYSQLCLI-u$user-p$pass-h $IP-E "start slave";

Echo-e "Address: $IP \ n Port: 3306\n service: MySQL master-slave replication error, the system has automatically skipped the error, please follow up processing. \ n Occurrence time: ' Date + '%F%T "'" \

| Mail-s ' master-slave replication error warning ' [email protected]

Fi

Reference: https://www.cnblogs.com/zhoujinyi/p/8035413.html

The role of MySQL Slave_exec_mode parameters