今天无意当中看到参数 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。关于该参数的说明可以看 MySQL 小误区:关于 set global 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 模式下的复制,对数据的一致性有了很严的要求,具体的可以看 MySQL Binlog 【ROW】和【STATEMENT】选择
主从上的测试表结构:
- 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://www.cnblogs.com/zhoujinyi/p/8035413.html