1. slave_exec_mode 参数作用

slave_exec_mode 可以在主从复制中遇到 duplicate-key 和 no-key-found 错误时,自动覆盖或者略过binlog里面这个row_event,避免报错停止复制。

这个参数原本是解决像 NDB Cluster 多节点写入冲突的情况,也可以在普通主从、双主、环形复制等情况下解决冲突,保持幂等性。幂等性怎么定义,感兴趣的可以阅读The differences between IDEMPOTENT and AUTO-REPAIR mode)。

set global slave_exec_mode=IDEMPOTENT (可以动态修改)使从库运行在 幂等模式,对1062,1032等不同的错误类型,有不同的处理:

  1. write_row event 遇到主键冲突或唯一索引冲突,这一行被覆写(delete + insert)。 delete时候不是full value match,仅需要主键或唯一索引找到记录则删除
  2. delete_row event 遇到记录不存在,忽略这一行
  3. update_row event 修改唯一索引导致的冲突,忽略这一行

注意:

  • idempotent 模式都是对有疑问的进行replace或ignore,不影响其它row。
  • idempotent 模式要求表上必须要有主键
  • binlog必须是 FULL RBR 模式

2. slave-skip-errors

这个参数不能在线修改,只能加到配置文件里面或者启动的时候带上--slave-skip-errors=1032,1062。除非你真的理解它skip掉了什么,否则不建议使用。

讲一个我所遇到的坑。在我们的一个分库项目中,需要把一个database里面的数据拆成32份,于是做了个主从,把从库里面不需要的那份删除,但复制过来肯定会报 HA_ERR_KEY_NOT_FOUND 错误,于是这也是所期望的,就设置了--slave-skip-errors=1032

但接下来就出现 1062:HA_ERR_FOUND_DUPP_KEY 错误!从库只会删数据,不会写入和更新,怎么会出现重复数据?读者不妨试想一下为什么。

这里做个说明:

① insert into t values (1, 'a'), (2, 'b'), (3, 'c');

② begin;
③ delete from t where id=1;
④ delete from t where id in (1, 2, 3);
⑤ insert into t where (3, 'c'), (4, 'd'), (5, 'e');
⑥ update t set ... id=1;
⑦ commit;
  • 事务包括显式事务和隐式事务(transaction),语句①落在binlog里面也会有begin和end
  • 一个事物可以包含多个语句(statement)
  • 一个语句可以影响多行(row),但属于一个event
  • 一个语句在binlog里面有多个event (row log event, table map event, xid event…)
  • event在binlog里面以 event group 组合起来
    事务引擎如 InnoDB,event group 就一个事务;非事务引擎如 MyISAM,event group就是一条语句

slave-skip-errors 参数作用的是 statement,上面的slave_exec_mode作用的是row 比如上面那段sql在RBR复制到从库时发现④的 id=2 不存在:

  • slave_exec_mode: ④里面的 id=2 略过,id=1,3 正常删除,事务里其它sql(row event)都正常重放

  • slave-skip-errors=1032: 从库也会一直从 begin 执行到 end ,但④里面的 id=3 会跳过(跳过的是这个statement,而 id=1 会依然删除,不是原子操作),事务里其它sql正常重放。

    这就导致了我上面那个问题,id=3应该是被删除的但被跳过,下面在插入 id=3 的记录就 1062 了。如果再把 1062 也加入到 skip-errors,那么数据肯定会出现的丢失,是不可取的。

相关验证可以看后文。

3. sql_slave_skip_counter

MySQL主从复制出现异常的时候,如不及时处理,延迟的时间会越来越长,所以有时候哪怕允许极少量的数据不一致,也要让数据继续同步,往往会用到 sql_slave_skip_counter 参数来跳过异常事件。 用法:

mysql> show slave status\G -- 1062可以看到是哪条记录重复

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
  • sql_slave_skip_counter=1
    跳过一个 event group。前面讲到对InnoDB而言,就是跳过一个事务。 如果当前 binlog postion 落在 event group 中间,那么就一直跳到这个事务末尾。
  • sql_slave_skip_counter=N (N>1)
    跳过 N 个 event。对不同的binlog版本会加入不同的event类型。
    slave_error_binlog_events
    比如上图在 pos 199 出现error,如果设置 set global sql_slave_skip_counter=3,那么就会以此跳过 199,264,332,每跳过一个 Skip_Counter 减去1,减到 Skip_Counter=1 的时候,如果pos还在事务中间,那么那么就一直跳到该事务末尾。 (同样,在事务中出现异常之前的修改,不会回滚)

4. GTID复制异常处理

主从开启了GTID(select @@gtid_mode),就不能再用 sql_slave_skip_counter 来跳过错误,需要注册一个空gtid event来代替原本执行报错的event。比如:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.153.173.149
                  Master_User: replicator
                  Master_Port: 3027
                Connect_Retry: 60
              Master_Log_File: mysql-bin.014670
          Read_Master_Log_Pos: 181716556
               Relay_Log_File: slave-relay.028871
                Relay_Log_Pos: 166693104
        Relay_Master_Log_File: mysql-bin.014670
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.014670, end_log_pos 166693925
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 166692941
              Relay_Log_Space: 688
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.014670, end_log_pos 166693925 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1088575531
                  Master_UUID: 108f89d5-d74f-11e7-942f-7cd30ac4755e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 108f89d5-d74f-11e7-942f-7cd30ac4755e:8077-122925776
            Executed_Gtid_Set: 108f89d5-d74f-11e7-942f-7cd30ac4755e:1-122925773,
8b101f33-f327-11e7-89c3-7cd30ac333bc:1-1425,
fba62795-d74e-11e7-942e-7cd30ac4e7fc:1-630905526
                Auto_Position: 0
1 row in set (0.00 sec)

跳过处理:

mysql> stop slave;
mysql> set gtid_next='108f89d5-d74f-11e7-942f-7cd30ac4755e:122925774';
mysql> begin; commit;  -- empty trx
mysql> set gtid_next='AUTOMATIC';  -- auto position
mysql> start slave;

上面 gtid_next 的值 108f89d5-d74f-11e7-942f-7cd30ac4755e:122925774 是个会话级变量。

  • uuid是 Retrieved_Gtid_Set 的uuid,一般是 Master_UUID 的值,但如果是级联复制(master -> slavel1 -> slave2),那么要找到出错事务最原先在哪执行的
  • trx_id(或叫position)是 master 上正常执行的最大id + 1,即Executed_Gtid_Set里面master uuid执行过的最大值 122925773 + 1

5. pt-slave-restart

pt-slave-restart 可以快速方便的恢复主从复制错误,并且支持普通 file:postion 和 GTID 模式。

修复的原理就是运行上面的 sql_slave_skip_countergtid_next,只是它可以自动的帮DBA识别错误码,或者匹配error_msg,stop/start slave,并且默认情况下它是一直运行 检测+修复。

pt-slave-restart --user=dbuser --password=xxxx --socket=/var/lib/mysql/mysql.sock --error-numbers=1032,1677,1051

几点说明一下:

  • --sleep
    pt-slave-restart 循环检查 show slave status 的间隔时间。如果发现有异常,下次sleep time将减半,因为它假设当前有异常,那么下一个event很有可能也异常。

  • --master_uuid
    级联复制下指定了 master_uuid 才能知道事件原始来自于哪里,好让pt-slave-restart知道在哪个 max_trx_id 上面 + 1。

  • 在gtid模式下,pt-slave-restart 不能用在多线程复制下(即 slave_parallel_workers>0),因为它不知道这个GTID错误是从库哪个sql线程产生的。

  • 以上所有处理错误的方法,在跳过后,都需要进行数据一致性修复(pt-table-sync),或者重做从库。

6. 手动处理复制错误并修复

这种处理思路是写程序实现,遇到1032错误,在主库Binlog里面解析出before image,在从库插入,再stop/start slave;遇到1062错误,在从库删除这条数据(可以根据主库binlog after image取数据,也可以根据duplicate key中提示的重复记录),再stop/start/slave。

不需要skip操作,也不需要后续修复数据(只是不会因为有跳过event而产生不一致),如果从主库拿binl log或者从库拿relay log有困难,也可使用 pymysql-replication 来伪装成从库拿到出错的 binlog postion 的内容,解析再用。

当然为保险起见,已经出现不一致的还是要 pt-table-checksum 跑一下。

7. 附: 测试 slave_skip_errors, slave_exec_mode

CREATE TABLE `t_repl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

insert into t_repl_test values(1,'a',10), (2,'b',20), (3,'c',30), (4,'d',40),(5,'e',50);

# 初始化测试数据
# master:
delete from t_repl_test where id=2;

# slave:
delete from t_repl_test where id=3; insert into t_repl_test values(2,'b',20);

每次测试前,数据都初始化成下面的:

7.1 delete 测试

1. slave_skip_errors=1032,1062
slave:

mysql> select @@slave_skip_errors, @@slave_exec_mode;
+---------------------+-------------------+
| @@slave_skip_errors | @@slave_exec_mode |
+---------------------+-------------------+
| 1032,1062           | STRICT            |
+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> delete from t_repl_test where id in (1,3,4); Query OK, 3 rows affected (0.00 sec)

mysql> delete from t_repl_test where id in (5); Query OK, 1 row affected (0.01 sec)

mysql> commit; Query OK, 0 rows affected (0.00 sec)

在从库,1和5被删除,4被跳过了,skip_error=1032作用在statement上,并且已经部分成功了的statement 不会回滚。

2. slave_exec_mode=IDEMPOTENT
复原。不是设置skip, 设置idempotent, slave:

mysql> select @@slave_skip_errors, @@slave_exec_mode;
+---------------------+-------------------+
| @@slave_skip_errors | @@slave_exec_mode |
+---------------------+-------------------+
| OFF                 | IDEMPOTENT        |
+---------------------+-------------------+

mysql> select * from t_repl_test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | b    |   20 |
+----+------+------+

这次1, 4, 5都被删除,也就是4是一个 statement 里面某一个row_event,没有受到 id=3 error 1032的影响。

注意
如果slave同时设置 slave_skip_errors 和 slave_exec_mode,那么优先生效的是 slave_skip_errors。

7.2 insert

1. slave_skip_errors=1032,1062 slave_exec_mode=STRICT

mysql> insert into t_repl_test values(8,‘h’,80); Query OK, 1 row affected (0.01 sec)

mysql> commit;

mysql> select * from t_repl_test; +—-+——+——+ | id | name | age | +—-+——+——+ | 1 | a | 10 | | 2 | b | 20 | | 4 | d | 40 | | 5 | e | 50 | | 6 | f | 60 | | 8 | h | 80 | +—-+——+——+

6成功,2和7失败,8成功。与delete作用范围一致。

2. slave_skip_errors=OFF slave_exec_mode=IDEMPOTENT
slave:

mysql> select @@slave_skip_errors, @@slave_exec_mode;
+---------------------+-------------------+
| @@slave_skip_errors | @@slave_exec_mode |
+---------------------+-------------------+
| OFF                 | IDEMPOTENT        |
+---------------------+-------------------+

mysql> select * from t_repl_test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   10 |
|  2 | bb   |  200 |
|  4 | d    |   40 |
|  5 | e    |   50 |
|  6 | f    |   60 |
|  7 | g    |   70 |
|  8 | h    |   80 |
+----+------+------+

6, 7, 8 都插入成功,id=2的id=2被更新。所以从库在 idempotent 模式下遇到1062,是replace操作。

3. slave_skip_errors=OFF slave_exec_mode=IDEMPOTENT unique_key
再来看一个好玩的(id是主键,name是唯一索引): 从库应用relay log遇到 Duplicate entry 错误有不同处理动作。

mysql> insert into t_repl_test values(9,‘b’,200); Query OK, 1 row affected (0.00 sec)

mysql> update t_repl_test set name=‘e’ where id=4; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t_repl_test; +—-+——+——+ | id | name | age | +—-+——+——+ | 1 | a | 10 | | 3 | c | 30 | | 4 | e | 40 | | 9 | b | 200 | +—-+——+——+ 4 rows in set (0.00 sec)

mysql> select * from t_repl_test; +—-+——+——+ | id | name | age | +—-+——+——+ | 1 | a | 10 | | 4 | d | 40 | | 5 | e | 50 | | 9 | b | 200 | +—-+——+——+ 4 rows in set (0.00 sec)

第一条 insert 值在从库上 name=b 已经存在,违反唯一约束,所以被 replace 掉了。
第二条 update 值在从库上 name=e 已经存在,违反唯一约束,在从库 被忽略 了。看从从库的imdepotent错误日志:

2018-02-02 14:50:35 24325 [Warning] Slave SQL: Could not execute Update_rows event on table d_ec_crmlog.t_repl_test; 
Duplicate entry 'e' for key 'uk_name', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
the event's master log mysql-bin.000015, end_log_pos 27072, Error_code: 1062

为什么会有这个行为,可以从源码里面找到答案:

Write_rows_log_event::do_before_row_operations()
  if ((slave_exec_mode == SLAVE_EXEC_MODE_IDEMPOTENT) ||
      (m_table->s->db_type()->db_type == DB_TYPE_NDBCLUSTER))
  {
    /*
      We are using REPLACE semantics and not INSERT IGNORE semantics
      when writing rows, that is: new rows replace old rows.  We need to
      inform the storage engine that it should use this behaviour.
    */
    
    /* Tell the storage engine that we are using REPLACE semantics. */
    thd->lex->duplicates= DUP_REPLACE;
    
    /*
      Pretend we're executing a REPLACE command: this is needed for
      InnoDB and NDB Cluster since they are not (properly) checking the
      lex->duplicates flag.
    */
    thd->lex->sql_command= SQLCOM_REPLACE;
    /* 
       Do not raise the error flag in case of hitting to an unique attribute
    */
    m_table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);

    ...
  }

本文链接地址:http://xgknight.com/2018/03/11/mysql-replication-error-and-idempotent/