MySQL主从复制idempotent模式以及同步错误处理预案

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 错误!从库只会删数据,不会写入和更新,怎么会出现重复数据?读者不妨试想一下为什么。

这里做个说明:

1
2
3
4
5
6
7
8
① 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 参数来跳过异常事件。
用法:

1
2
3
4
5
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。比如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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)

跳过处理:

1
2
3
4
5
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,并且默认情况下它是一直运行 检测+修复。

1
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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);

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






masterslave

mysql> select from t_repl_test;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 1 | a | 10 |
| 3 | c | 30 |
| 4 | d | 40 |
| 5 | e | 50 |
+—-+——+——+

mysql> select from t_repl_test;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 1 | a | 10 |
| 2 | b | 20 |
| 4 | d | 40 |
| 5 | e | 50 |
+—-+——+——+

7.1 delete 测试

1. slave_skip_errors=1032,1062
slave:

1
2
3
4
5
6
7
mysql> select @@slave_skip_errors, @@slave_exec_mode;
+---------------------+-------------------+
| @@slave_skip_errors | @@slave_exec_mode |
+---------------------+-------------------+
| 1032,1062 | STRICT |
+---------------------+-------------------+
1 row in set (0.00 sec)






master(每轮测试都执行)slave

mysql> begin;
Query OK, 0 rows affected (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)

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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






master(每轮测试都执行)slave

mysql> begin;
mysql> insert into t_repl_test values(6,’f’,60),
(2,’bb’,200),(7,’g’,70);
Query OK, 3 rows affected (0.00 sec)

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 错误有不同处理动作。






masterslave

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

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 |
| 2 | b | 20 |
| 4 | d | 40 |
| 5 | e | 50 |
+—-+——+——+
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错误日志:

1
2
3
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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://seanlook.com/2018/03/11/mysql-replication-error-and-idempotent/