MySQL复制跳过错误--slave_skip_errors、sql_slave_skip_counter、slave_exec_mode

MySQL主从复制中的跳过错误处理参数--slave_skip_errors、sql_slave_skip_counter、slave_exec_mode

跳过复制错误——slave_skip_errors、slave_exec_mode

跳过复制错误——sql_slave_skip_counter

1 简介

    mysql在主从复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

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

2 官方参考

    slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors。

     默认情况下该参数值是off，我们可以列出具体的error code，也可以选择all，mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）

    一些error code代表的错误如下：

    1007：数据库已存在，创建数据库失败

    1008：数据库不存在，删除数据库失败

    1050：数据表已存在，创建数据表失败

    1051：数据表不存在，删除数据表失败

    1054：字段不存在，或程序文件跟数据库有冲突

    1060：字段重复，导致无法插入

    1061：重复键名

    1068：定义了多个主键

    1094：位置线程ID

    1146：数据表缺失，请恢复数据库

    1053：复制过程中主服务器宕机

    1062：主键冲突 Duplicate entry '%s' for key %d

 my.cnf中的写法：

slave_skip_errors=1062,1053  

slave_skip_errors=all  

slave_skip_errors=ddl_exist_errors  

  

  作为mysql启动参数的写法：

--slave-skip-errors=1062,1053  

--slave-skip-errors=all  

--slave-skip-errors=ddl_exist_errors  

    

从数据库中查看该参数的值：

mysql> show variables like 'slave_skip%';  

+-------------------+-------+  

| Variable_name     | Value |  

+-------------------+-------+  

| slave_skip_errors | 1007  |  

+-------------------+-------+  

3 举例分析

    3.1 测试说明

    配置好mysql主从同步，然后在从上写入数据，造成主从不一致。

    3.2 准备测试表结构

    在主机上创建表：

create table replication (c1 int not null primary key, c2 varchar(10));  

    3.3 准备测试数据

    在主机上插入基础数据

mysql> insert into replication values (1, 'test1');  

mysql> insert into replication values (2, 'test2');  

    此时，主机从机replication表里面都有两条记录

    3.4 开始测试

    从机插入一条记录

mysql> insert into replication values (3, 'test3');  

    然后在主机上执行相同的操作

mysql> insert into replication values (3, 'test3');  

    在从机上查看复制状态

mysql> show slave status \G  

*************************** 1. row ***************************  

               Slave_IO_State: Waiting for master to send event  

                  Master_Host: 192.168.1.222  

                  Master_User: repl  

                  Master_Port: 3306  

                Connect_Retry: 60  

              Master_Log_File: mysql-bin.000003  

          Read_Master_Log_Pos: 16700  

               Relay_Log_File: mysql-relay-bin.000003  

                Relay_Log_Pos: 16595  

        Relay_Master_Log_File: mysql-bin.000003  

             Slave_IO_Running: Yes  

            Slave_SQL_Running: No  

              Replicate_Do_DB:   

          Replicate_Ignore_DB:   

           Replicate_Do_Table:   

       Replicate_Ignore_Table: mysql.ibbackup_binlog_marker  

      Replicate_Wild_Do_Table:   

  Replicate_Wild_Ignore_Table: mysql.backup_%  

                   Last_Errno: 1062  

                   Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into replication values (3, 'test3')'  

                 Skip_Counter: 0  

          Exec_Master_Log_Pos: 16425  

              Relay_Log_Space: 17544  

    可以看到：sql线程已经停止工作 Slave_SQL_Running: No

                        错误号为：Last_Errno: 1062

                        错误信息为：Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into replication values (3, 'test3')'

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

[mysqld]  

slave_skip_errors=1062  

    具体测试方法同上，大家可自己验证。

4 从backup恢复时从机复制出错的一些解释

    mysql企业版备份工具meb提供在线热备功能，如果在备份过程中执行ddl操作，从机需要从主机的备份恢复时可能会异常，从而导致从机同步数据失败。原因是从机恢复时需要先从备份文件恢复（包含备份过程中执行的ddl语句），

同步时不是从全备后的最后一个位置同步，而是从ddl的上个位置同步，如果再次执行该ddl语句在从机上不会造成冲突，

则同步继续，如果会造成冲突，同步终止。解决此冲突的办法是在my.cnf文件中加入一行

[mysqld]  

slave_skip_errors=ddl_exist_errors  

5 注意事项

    5.1 该参数为全局静态参数，不能动态调整，可在my.cnf中加入该参数列表后重启mysql服务器生效。

    5.2 必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

****************************************************************************************

sql_slave_skip_counter 介绍：

摘自MySQL官方的解释（ 强烈建议阅读英文原文 。中文版，是笔者自己的理解，只能说仁者见仁）

SET GLOBAL sql_slave_skip_counter Syntax：
        SET GLOBAL sql_slave_skip_counter = N
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.
        跳过N个events。注意：以event为单位，而不是以事务为单位，只有在由单条语句组成的事务时，两者才等价。
        如：一个事务由多个EVENT组成，BEGIN；INSERT；UPDATE；DELETE；COMMOIT； 这种情况下，两者绝不相等
This statement is valid only when the slave threads are not running. Otherwise, it produces an error.

When using this statement, it is important to understand that the binary log is actually organized as a sequence of groups known as event groups. Each event group consists of a sequence of events.

For transactional tables, an event group corresponds to a transaction.
        对于事务表，一个event group对应一个事务
or nontransactional tables, an event group corresponds to a single SQL statement. 
        对于非事务表，一个event group对应一条SQL
When you use SET GLOBAL sql_slave_skip_counter to skip events and the result is in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group
        当你跳过event的时候，如果N的值，处于event group之中，那么slave会继续跳过event，直至跳过这个event group，从下一个event group开始

对于事务表使用 sql_slave_skip_counter 的情况：

1、跳过1032复制错误（update/delete error）

跳过由单条SQL组成的事务：

在Slave主机上人为的删除两条数据：

DELETE FROM `edusoho_e`.`t1` WHERE `id` = '9'; 
DELETE FROM `edusoho_e`.`t1` WHERE `id` = '11';