Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of slave_exec_mode parameters in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Accidentally see the parameter slave_exec_mode, from the manual instructions to see that the parameter and MySQL replication related, can be dynamically modified variables, the default is STRICT mode (strict mode), optional values are IDEMPOTENT mode (idempotent mode). Setting to IDEMPOTENT mode allows the slave library to avoid errors of 1032 (keys that do not exist on the slave library) and 1062 (duplicate keys, which require the presence of a primary key or unique key), which is valid only in ROW EVENT's binlog mode and not in STATEMENT EVENT's binlog mode. IDEMPOTENT mode is mainly used in the case of multi-master replication and NDB CLUSTER, but is not recommended in other cases. Judging from the above introduction, this parameter skips the specified error from the library, and here comes the problem:

1: compared with sql_slave_skip_counter, what are the advantages?

2: compared with slave-skip-errors = N, what are the advantages?

With these two questions, this paper carries on the related test and explanation.

Environment:

MySQL version: Percona MySQL 5.7

Copy mode: ROW, GTID is not enabled

Test:

① 1062 error: Could not execute... Event on table db.x; Duplicate entry 'xx' for key' PRIMARY', Error_code: 1062

The test table structure on the master-slave:

CREATE TABLE `x` (`id` int (11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

The table record on the master and slave:

M:

Select * from

S:

Select * from x-1 | 2 | 3 | +-+ 3 rows in set (0.00 sec)

The table records on the master and slave are already inconsistent, and the master lacks the record of id=1.

At this point, slave_exec_mode from the top is the default STRICT mode:

Show variables like 'slave_exec_mode';+-+-+ | Variable_name | Value | +-+-+ | slave_exec_mode | STRICT | +-+-+ 1 row in set (0.00 sec)

The binlog mode on M is:

Show variables like 'binlog_format'; +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec)

Execute on M:

Insert into x values (1), (4), (5); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

Because the id=1 record already exists from above, a 1062 error is reported from the replication at this time:

Last_SQL_Errno: 1062Last_SQL_Error: Could not execute Write_rows event on table dba_test.x; Duplicate entry'1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-3306.000006, end_log_pos 7124

When this error occurs, the consensus is to execute: sql_slave_skip_counter=N.

1. N in set global sql_slave_skip_counter=N refers to skipping N event2. It is best to remember that when N is set to 1, the effect skips the next transaction. 3. After skipping the Nth event, if the location happens to fall within a transaction, the whole transaction will be skipped. 4. An insert/update/delete does not necessarily correspond to only one event, which is determined by the engine and log format.

The unit of sql_slave_skip_counter is "event". Many people think that the unit of this parameter is "transaction". In fact, it is wrong because a transaction contains multiple event, and skipping N may still be in the same transaction. For the 1062 error above, setting N to 1: 4 has the same effect, skipping a transaction. Because the executed SQL generates four event:

Show binlog events in 'mysql-bin-3306.000006' from 6950 +-+ | Log_name | Pos | Event_type | Server_ Id | End_log_pos | Info | +-+ | Mysql-bin-3306.000006 | 6950 | Query | 7026 | BEGIN | mysql-bin-3306.000006 | 7026 | Table_map | 7074 | table_id: 707 (dba_test.x) | mysql-bin-3306.000006 | 7074 | Write_rows | table_id: 707 flags: STMT_END_F | mysql-bin-3306.000006 | 7124 | Xid | 169,7155 | COMMIT / * xid=74803 * / | | +-+-- + 4 rows in set (0.00 sec) |

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 of these methods can restore replication to normal, but will make the master-slave data inconsistent (used with caution) and cause the slave database to lose the records of id=4 and 5. And the second method also needs to restart the database, so the slave_exec_mode parameters introduced in this article come in handy. Set this parameter on the slave library:

Set global slave_exec_mode='IDEMPOTENT';Query OK, 0 rows affected (0.00 sec) stop slave;Query OK, 0 rows affected (0.00 sec) start slave;Query OK, 0 rows affected (0.00 sec)

Also executed on the Lord:

Insert into x values (1), (4), (5)

You can be pleasantly surprised to find that the master-slave data is synchronized and there is no replication exception:

M:select * from x; +-+ | id | +-+ | 1 | | 2 | 3 | 4 | | 5 | +-+ 5 rows in set (0.00 sec) S:select * from x +-+ | id | +-+ | 1 | | 2 | | 3 | | 4 | | 5 | +-+ 5 rows in set (0.01sec)

As you can see from the above test, when the parameter is set to slave_exec_mode='IDEMPOTENT', an incorrect event can be skipped.

② 1032 error: Could not execute... Event on table db.x; Can't find record in'xmom, Error_code: 1032

The reason for this error is that replication in ROW mode has strict requirements on data consistency.

The test table structure on the master-slave:

CREATE TABLE `x` (`id` int (11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

The table record on the master and slave:

M:

Select * from x; +-+ | id | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec)

S:

Select * from x + id | +-+ | 1 | | 3 | +-+ 2 rows in set (0.00 sec)

The table records on the master and slave are already inconsistent, and the records of id=2 are missing from the master. At this point, slave_exec_mode from the top is the default STRICT mode:

Show variables like 'slave_exec_mode';+-+-+ | Variable_name | Value | +-+-+ | slave_exec_mode | STRICT | +-+-+ 1 row in set (0.00 sec)

The binlog mode on M is:

Show variables like 'binlog_format'; +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec)

Execute on M:

BEGIN;INSERT INTO x SELECT 4 _ delete FROM x WHERE id = 2 _ insert INTO x SELECT 5 _ commit

Because there is no record of id=2 from above, a 1032 error is reported for replication from:

Last_SQL_Errno: 1032Last_SQL_Error: Could not execute Delete_rows event on table dba_test.x; Can't find record in'xrooms, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin-3306.000006, end_log_pos 12102

Similarly, the two methods described in the above tests can make replication normal, but the data will also be lost. Lost the record of id=4 and 5, continue to set this parameter on the slave library:

Set global slave_exec_mode='IDEMPOTENT';Query OK, 0 rows affected (0.00 sec) stop slave;Query OK, 0 rows affected (0.00 sec) start slave;Query OK, 0 rows affected (0.00 sec)

Do the same on M:

BEGIN;INSERT INTO x SELECT 4 _ delete FROM x WHERE id = 2 _ insert INTO x SELECT 5 _ commit

You can also be pleasantly surprised to find that the master-slave data is synchronized and there is no replication exception.

Note: slave_exec_mode='IDEMPOTENT' cannot idempotent DDL, nor can it idempotent errors caused by different field lengths, such as changing the id field type int to bigint from the database table in the example. And can only be used in the mode where binlog_format is ROW, and can only be idempotent for 1032 and 1062.

Summary:

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

This parameter looks good, but the manual states that it is not recommended to turn it on in a normal replication environment. For storage engines other than NDB, IDEMPOTENT mode should be used only when it is determined that duplicate key errors and non-key errors can be safely ignored. This parameter is specifically designed for NBD Cluster. In NBD Cluster mode, this parameter can only be set to IDEMPOTENT mode. Therefore, it should be decided according to your application scenario. Under normal circumstances, the master and slave are consistent, and any error should be reported, but when special processing is done, it can be turned on temporarily.

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 above is all the contents of the article "sample Analysis of slave_exec_mode parameters in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.