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是不支持的,该模式下的复制可以自行测试。
上一篇: 批处理程序删除自身 bat批处理