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

The following is about the description and usefulness of the slave_exec_mode parameters of MySQL. The secret of the text is to be close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading the slave_exec_mode parameter description and usefulness of MySQL.

Slave_exec_mode=IDEMPOTENT is a very useful parameter in the MySQL replication environment: as long as set global slave_exec_mode=IDEMPOTENT is running on the slave, the sql thread of the slave runs in the waiting mode, which allows the standby to keep copying when the insert primary key, unique key conflict, and update and delete values do not find an error. (effective immediately, even the slave SQL thread does not have to restart) However, rough skip error methods such as sql_slave_skip_counter=N and slave-skip-errors = N may break the consistency between master and standby.

Note: the watch should have a primary key when using the ghost mode.

Ghost mode is not omnipotent, except that it cannot operate on DDL, nor is it equal to errors caused by different field lengths (for example, a field in the host computer is char (20) and the standby machine is char (10). Another limitation is that the table has a primary key to set effective settings for insert, etc.: because the behavior of insert is to determine whether the standby has a duplicate value through the primary key, and if the table does not have a primary key Even if the standby machine is set up, it may repeat more data than the host computer.

For the slave_exec_mode parameter, it can skip 1062 and 1032 errors without affecting normal data execution in the same transaction. If it is a transaction consisting of multiple SQL, you can skip the problematic event.

In addition, replication in GTID mode is not supported by sql_slave_skip_counter, and replication in this mode can be tested by itself.

The problem often encountered in master-slave replication is the duplication of 1062 primary keys.

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

How to deal with error 1032 or 1062:

So the ways to handle 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 the configuration file (restart is required)

Both methods can bring replication back to normal, but will make master-slave data inconsistent (used with caution), and if there are multiple statements in a transaction, other statements will be lost.

And the second method also needs to restart the database, so the slave_exec_mode parameter comes in handy. Set this parameter on the slave 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

Slave_exec_mode and slave_skip_errors play the same role, but slave_skip_errors does not support dynamic modification, and mysql must be restarted to take effect, so it is recommended to use slave_exec_mode.

For the slave_exec_mode parameter, it can skip 1062 and 1032 errors without affecting normal data execution in the same transaction. If it is a transaction consisting of multiple SQL, you can skip the problematic event.

Insert into x values (1), (4), (5); # in which the slave database has the record of id=4

BEGIN

INSERT INTO x SELECT 4

DELETE FROM x WHERE id = 2; # Slave does not have this record

INSERT INTO x SELECT 5

COMMIT

#! / bin/bash

# Description: automatically skip master-slave copy errors (error codes 1023, 1062). This script is executed 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 $2}')

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\ nPort: 3306\ nService: MySQL master-slave replication error occurred, the system has automatically skipped the error, please follow up.\ noccurrence time: `date +"% F% T "`"\

| | mail-s' Master / Slave replication error warning 'xxxxxx@xxx.com |

Fi

Is there anything you don't understand about the description and usefulness of the slave_exec_mode parameters of MySQL above? Or if you want to know more about it, you can continue to follow our industry information section.

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.