MySQL-跳过主从复制环境中错误

简述

mysql在主从复制过程中，由于各种的原因，从库可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，将停止复制进程，不再进行同步，等到人工干预来处理。

slave-skip-errors

slave-skip-errors的作用就是用来定义复制过程中从库可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

--slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]

全局静态参数，不可动态修改

参数值

OFF: 默认值
[list of error codes]：
all：忽略所有错误消息并继续运行。无法保证数据的完整性
ddl_exist_errors：
- 等价于错误代码列表 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

常见错误号

1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1050：数据表已存在，创建数据表失败
1051：数据表不存在，删除数据表失败
1054：字段不存在，或程序文件跟数据库有冲突
1060：字段重复，导致无法插入
1061：重复键名
1068：定义了多个主键
1094：位置线程ID
1146：数据表缺失，请恢复数据库
1053：复制过程中主服务器宕机
1062：主键冲突 Duplicate entry '%s' for key %d

示例

--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skip-errors=ddl_exist_errors

查看当前参数值

show variables like 'slave_skip_errors';

sql_slave_skip_counter

sql_slave_skip_counter参数用于复制过程中从库可以自动跳过N个events。该参数不会立即生效，它从下一个START REPLICA开始生效。

从MySQL 8.0.26开始，sql_slave_skip_counter 已经被 sql_replica_skip_counter 参数取代，已弃用sql_slave_skip_counter。

START REPLICA

START REPLICA [thread_types] [until_option] [connection_options] [channel_option]

thread_types:
    [thread_type [, thread_type] ... ]

thread_type:
    IO_THREAD | SQL_THREAD

until_option:
    UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
          |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
          |   SOURCE_LOG_FILE = 'log_name', SOURCE_LOG_POS = log_pos
          |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
          |   SQL_AFTER_MTS_GAPS  }

connection_options:
    [USER='user_name'] [PASSWORD='user_pass'] [DEFAULT_AUTH='plugin_name'] [PLUGIN_DIR='plugin_dir']


channel_option:
    FOR CHANNEL channel

gtid_set:
    uuid_set [, uuid_set] ...
    | ''

uuid_set:
    uuid:interval[:interval]...

uuid:
    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh

h:
    [0-9,A-F]

interval:
    n[-n]

    (n >= 1)

MySQL 8.0.22 开始，start slave已经被start replica取代，此版本已弃用start slave。在8.0.22以前版本使用start slave

取值范围

sql_slave_skip_counter 参数的取值范围：[0, 4294967295]

注意：

sql_slave_skip_counter 参数与基于 GTID 的复制不兼容，并且在设置 gtid_mode=ON 时不得将其设置为非零值

示例

-- 在Slave跳过一个 Update_rows event 复制
set global sql_slave_skip_counter=1;
start slave sql_thread;

show slave statusG

slave_exec_mode

slave_exec_mode 控制复制线程如何解决复制期间的冲突和错误。设置此变量会立即对所有复制通道生效，包括正在运行的通道。

从MySQL 8.0.26开始，slave_exec_mode 已经被 replica_exec_mode 参数取代

参数值

IDEMPOTENT：忽略 duplicate-key errors 和 key-not-found errors 错误。即：可以让从库避免1032（从库上不存在的键）和1062（重复键，需要存在主键或则唯一键）的错误
- 主要用于多主复制和NDB CLUSTER的环境
STRICT: 严格模式，不会跳过任何错误。（MySQL Server 8.0 默认值）

示例

使用slave_skip_errors跳过复制错误

创建测试数据

create table tb1 (id int not null primary key, name varchar(10));  

insert into tb1 values (1, 'test1');id
insert into tb1 values (2, 'test2');
commit;

模拟故障

从库插入一条记录

insert into tb1 values (3, 'test3');
commit;

主库执行相同的操作

insert into tb1 values (3, 'test3');
commit;

查看从库复制状态

show slave status G

处理

方案1：从库配置参数启动

/usr/local/mysql/bin/mysqld_safe --user=mysql --basedir=/usr/local/mysql --datadir=/data --slave-skip-errors=1062 &

方案2：修改my.cnf文件

在my.cnf中加入如下选项，则可跳过此错误，数据同步继续进行

[mysqld]  
slave_skip_errors=1062
# DDL 冲突
# slave_skip_errors=ddl_exist_errors

注意事项

处理不当，很可能造成主从数据库的数据不一致

使用sql_slave_skip_counter参数跳过复制错误

模拟故障

slave库删除一条记录

DELETE FROM `tb1` WHERE `id` = 2;
commit;

主库刚好也执行删除同一条记录的事务

BEGIN;
DELETE FROM tb1 WHERE id = 2;
insert into tb1 values (5, 'test5');
COMMIT;

检查slave状态

show slave statusG

处理

在Master上查看对应的position操作

show binlog events in 'mysql-bin.000002' from 6840;

slave上处理跳过一个事务

set global sql_slave_skip_counter=1;
start slave sql_thread;

show slave statusG

在使用sql_slave_skip_counter跳过由多条SQL（event）组成的事务时，虽然主从复制恢复了，但是数据仍处于不一致状态，要抓紧时间补齐数据或重做Slave

可以配合slave_exec_mode参数一起使用，就可以达到只跳过由多个SQL语句组成单个事务中有问题的event。