MySQL中slave_exec_mode参数详解

MySQL中slave_exec_mode参数详解,第1张

MySQL中slave_exec_mode参数详解

今天无意当中看到参数slave_exec_mode,从手册里的说明看出该参数和MySQL复制相关,是可以动态修改的变量,默认是STRICT模式(严格模式),可选值有IDEMPOTENT模式(幂等模式)。设置成IDEMPOTENT模式可以让从库避免1032(从库上不存在的键)和1062(重复键,需要存在主键或则唯一键)的错误,该模式只有在ROW EVENT的binlog模式下生效,在STATEMENT EVENT的binlog模式下无效。IDEMPOTENT模式主要用于多主复制和NDB CLUSTER的情况下,其他情况不建议使用。从上面的介绍来看,这个参数的让从库跳过指定的错误,那问题来了:

1:和 sql_slave_skip_counter 比,有什么好处?

2:和 slave-skip-errors = N比,有什么好处?

带着这2个问题,本文来进行相关的测试和说明。 

环境:

MySQL版本:Percona MySQL 5.7

复制模式:ROW,没有开启GTID

测试:

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

主从上的测试表结构:

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

主从上的表记录:

M:

select * from x;
+----+
| id |
+----+
| 2 |
| 3 |
+----+
2 rows in set (0.01 sec)

S:

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

主从上的表记录本来就不一致了,主上缺少了id=1的记录。

此时从上的slave_exec_mode为默认的STRICT模式:

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

M上的binlog模式为:

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

在M上执行:

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

因为从上已经存在了id=1的记录,此时从的复制就报了1062的错误:

Last_SQL_Errno: 1062
Last_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

出现这个错误时,大家的一致做法就是执行:sql_slave_skip_counter=N。

1、set global sql_slave_skip_counter=N中的N是指跳过N个event
2、最好记的是N被设置为1时,效果跳过下一个事务。
3、跳过第N个event后,位置若刚好落在一个事务内部,则会跳过这整个事务
4、一个insert/update/delete不一定只对应一个event,由引擎和日志格式决定

sql_slave_skip_counter的单位是“event”,很多人认为该参数的单位是“事务”,其实是错误的,因为一个事务里包含了多个event,跳过N个可能还是在同一个事务当中。对于上面出现1062的错误,把N设置成1~4效果是一样的,都是跳过一个事务。因为执行的SQL生成了4个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   |    169 |    7026 | BEGIN              |
| mysql-bin-3306.000006 | 7026 | Table_map |    169 |    7074 | table_id: 707 (dba_test.x)   |
| mysql-bin-3306.000006 | 7074 | Write_rows |    169 |    7124 | 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)

所以处理该错误的方法有:

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:在配置文件里指定slave-skip-errors=1062(需要重启)

这2种方法都能让复制恢复正常,但是会让主从数据不一致(谨慎使用),让从库丢失了id=4和5的记录。并且第2种方法还需要重启数据库,这时本文介绍的slave_exec_mode参数就派上用场了。在从库上设置该参数:

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)

同样在主上执行:

insert into x values(1),(4),(5);

可以惊喜的发现主从数据是同步的,没有出现复制异常:

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.01 sec)

上面的测试可以看到,参数设置成slave_exec_mode='IDEMPOTENT' 后,可以跳过出一个错误的event。

② 1032错误:Could not execute ... event on table db.x; Can't find record in 'x', Error_code: 1032;

这个错误的出现是因为ROW模式下的复制,对数据的一致性有了很严的要求

主从上的测试表结构:

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

主从上的表记录:

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)

主从上的表记录本来就不一致了,从上缺少了id=2的记录。此时从上的slave_exec_mode为默认的STRICT模式:

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

M上的binlog模式为:

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

在M上执行:

BEGIN;
INSERT INTO x SELECT 4;
DELETE FROM x WHERE id = 2;
INSERT INTO x SELECT 5;
COMMIT;

因为从上不存在了id=2的记录,此时从的复制就报了1032的错误:

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

同样的,在上面测试中说明的2种方法可以让复制正常,但是数据也一样会丢失。丢失了id=4和5的记录,继续在从库上设置该参数:

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)

在M上执行同样的 *** 作:

BEGIN;
INSERT INTO x SELECT 4;
DELETE FROM x WHERE id = 2;
INSERT INTO x SELECT 5;
COMMIT;

也可以惊喜的发现主从数据是同步的,没有出现复制异常。

注意:slave_exec_mode='IDEMPOTENT'不能对DDL *** 作幂等,并且也不能对字段长度不同导致的错误进行幂等,如把例子中的从库表的id字段类型int改成bigint。并且只能在binlog_format为ROW的模式下使用,而且只能对1032和1062进行幂等模式。

总结:

对于上面的测试总结,针对slave_exec_mode参数,它可以跳过1062和1032的错误,并且不影响同一个事务中正常的数据执行。如果是多个SQL组成的事务,则可以跳过有问题的event。

看着这个参数很不错,但手册上说明不建议在普通的复制环境中开启。对于NDB以外的存储引擎,只有在确定可以安全地忽略重复键错误和没有键的错误时,才应使用IDEMPOTENT模式。这参数是专门针对NBD Cluster进行设计的,NBD Cluster模式下,该参数只能设置成IDEMPOTENT模式。所以要根据自己的应用场景来决定,正常情况下,主从是一致的,有任何错误发生都要报错,不过在做特殊处理时,可以临时开启。

另外在GTID模式下的复制,sql_slave_skip_counter是不支持的,该模式下的复制可以自行测试。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/887536.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-05-14
下一篇 2022-05-14

发表评论

登录后才能评论

评论列表(0条)

保存