MySQL备库复制SQL线程中断问题及解决方法

MySQL5.6之前的备库复制有两个工作线程:IO线程和SQL线程。SQL线程异常导致复制被中断。

许多DBA会遭受备库复制线程中断的报警,具体说来常见的错误有如下几类:

  • 1032错误 – HA_ERR_KEY_NOT_FOUND
  • 1062错误 – HA_ERR_FOUND_DUPP_KEY (HA_ERR_FOUND_DUPP_KEY 或 HA_ERR_FOUND_DUPP_UNIQUE)

问题背景

主备数据一不致,往往是由于MySQL的Bug导致,其中MySQL复制相关的Bug从每次Release Notes都可以看到相关的身影,就我们最近两周线上遇到的列举如下:

当然,最大的一个未知的因素是主库宕机,甚至为保证服务可用而做的主备切换,有多少数据不致,我们心里没有谱。只有依赖后续的定时扫描来做事后检测。虽然我们目前做的MHA从一定程度上回减少数据不一致的数量,但也无法做到数据严格一致。这个问题是MySQL社区的一大难题,在些不展开讨论。

另外,有发生异常时,DBA的不严格操作也可能会加深这种数据不一致的影响,例如,遇到错误时,跳过若干错误,直到复制能正常工作。

1
root@(none) 09:43:26>set global sql_slave_skip_counter=1;

解决思路

对于我们线上最常见的两种类型进行智能化地处理,即 HA_ERR_KEY_NOT_FOUND 或 HA_ERR_FOUND_DUPP_KEY的错误,利用ROW模式复制的特点,在执行事件遇到错误时,进行如下转化:

  • HA_ERR_KEY_NOT_FOUND:
    • 对于update事件 UPDATE_ROWS_EVENT 直接插入前镜像再做更新
    • 对于delete事件 DELETE_ROWS_EVENT 直接插入前镜像再做删除。
  • HA_ERR_FOUND_DUPP_KEY:
    • 对于update事件 UPDATE_ROWS_EVENT先删除后镜像再更新
    • 对于insert事件 WRITE_ROWS_EVENT 直接以覆盖形式插入(即先删再插或转化成更新)。

实现效果

对slave_exec_mode增加 SMART 模式(STRICT | IDEMPOTENT | SMART)

1
2
3
4
5
6
7
8
9
root@(none) 01:39:34>set global slave_exec_mode=SMART; 
Query OK, 0 rows affected (0.00 sec)
root@(none) 01:39:39>select @@slave_exec_mode;
+-------------------+
| @@slave_exec_mode |
+-------------------+
| SMART |
+-------------------+
1 row in set (0.00 sec)

增加对smart处理结果的统计:

root@(none) 01:46:51>show status like 'smart%'; 
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Smart_handle_dup_pk | 0     |
| Smart_handle_failed | 0     |
| Smart_handle_no_pk  | 15    |
+---------------------+-------+
3 rows in set (0.00 sec)

运行情况

从线上运行情况下,此patch解决了绝大部分复制中断异常,大大减少DBA半夜三更被警告吵醒手动修复异常复制的频率。总体而言,是一个非常成功的补丁。

但仍然存一此尚未完善的场景,例如唯一键约束或外键约束情况下,更新失败还是无法处理。在线上某个应用上,SMART还是不能处理unique key更新失败问题。

Percona已经将此patch标记为contribution, 希望他们能将剩下的问题解决掉,这样更大程度上解放DBA对某些业务线的MySQL复制中断的处理精力和时间。

patch参考:

原文地址:http://www.taobaodba.com/html/922_add_smart_slave_execute_mode.html