首页 > 代码库 > MySQL 跳过同步错误方法

MySQL 跳过同步错误方法

最近MySQL 遇到了同步问题,现整理一下常遇到的错误的解决方法,备用。

 

 

方法一:手动设置动态参数 sql_slave_skip_counter

 

我常用的脚本:

stop slave sql_thread;set global sql_slave_skip_counter=1;start slave sql_thread;

这个要 根据具体的错误来判定,一般用于主键冲突或者更新失败错误,进行手动跳过。

 

 


方法二:静态服务器设置,需要重启MySQL
[mysqld]
slave_skip_errors=1032,1064
重启MySQL之后,会自动加载配置文件,同步自动跳过更新,与主键冲突错误。
参数说明:
Normally, replication stops when an error occurs on the slave. 
This gives you the opportunity to resolve the inconsistency in the data manually.
This variable tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.

 

方法三:动态设置跳过错误
slave_exec_mode
这个比较狠
set global slave_exec_mode =strict;
严格执行策略。大多数情况下遇到错误,同步就会终止。等待错误解决。

set global slave_exec_mode =idempotent;
这个设置,可以允许同步跳过 
duplicate-key and no-key-found错误

参数说明:
Controls whether IDEMPOTENT or STRICT mode is used in replication conflict resolution and error checking. 
IDEMPOTENT mode causes suppression of some errors, including duplicate-key and no-key-found errors. Beginning with MySQL 5.1.23-ndb-6.2.14 and MySQL 5.1.24, this mode should be employed in multi-master replication, circular replication, and some other special replication scenarios. 
STRICT mode is the default, and is suitable for most other cases

MySQL 跳过同步错误方法