MySQL5.6 crash-safe replication一个坑

.版本

1)操作系统

cat /etc/issue
CentOS release 6.9 (Final)
Kernel \r on an \m

cat /proc/version
Linux version 2.6.32-696.el6.x86_64 (mockbuild@c1bm.rdu2.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) ) #1 SMP Tue Mar 21 19:29:05 UTC 2017

2)mysql数据库版本

mysql --version

mysql  Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using  EditLine wrapper

 

 

2. 问题描述

2.1 问题发现

    在测试crash-safe 功能时发现了点问题，如果我指定了 relay_log_info_repository = TABLE,这时主库执行DDL语句后，从库的 relay_log_info_repository 表中的 Master_log_pos 字段并不会立即被更新(这时ddl操作已经被应用到从库，并且从库show slave status\G; 查看执行的位置是正确的)，而DML语句不会有该问题(sync_relay_log_info 为默认值 10000)。

    如果在主库执行了一个ddl，并且该ddl被从库应用后，紧接着(从库执行该ddl后)从库异常宕机。那么从库再启动后可能会把之前已经执行过的ddl再次执行一遍，导致复制异常 会遇到如下错误：
Error 'Duplicate column name 'name_1'' on query. Default database: 'test_shao'. Query: 'alter table test_4 add column name_1 varchar(20)'

2.2 问题原因

  我们知道，MySQL 5.6 针对复制功能提供了新特性: slave支持crash-safe. 该功能可以解决之前版本中系统异常断电可能导致的SQL thread 信息不准确的问题。##关于crash-safe 详细介绍请参见 MySQL5.6新特性之crash-safe slaves

    实现 从库 crash-safe需要在从库配置文件中添加如下两个参数，并重启实例

relay_log_info_repository = TABLE
relay_log_recovery = ON

    从库实现 crash-safe 的大概原理就是把 sql_thread 的执行位置记录到 innodb 表中(relay_log_info_repository = TABLE),并且把 sql_thread 线程执行事务和更新 mysql.slave_relay_log_info 放在一个事物中，从而避免实际已执行的binlog位点和写入relay log info 的位点信息不一致的情况发生。那的怎么保证 io_thread 线程位点的正确性,通过添加 relay_log_recovery = ON 参数，在从库实例重启时会自动把 io_thread 线程位点初始化成 sql_thread位点。

##对于dml 我们可以做到把 dml操作 和 mysql.slave_relay_log_info表的更新放到一个事务中来保证位点正确性，那么对于 ddl 操作呢？

2.3 问题处理
如果我指定了 relay_log_info_repository = TABLE,这时主库执行DDL语句后，从库的 relay_log_info_repository 表中的 Master_log_pos 字段并不会立即被更新(这时ddl操作已经被应用到从库，并且从库show slave status\G; 查看执行的位置是正确的)，而DML语句不会有该问题。这时有如下几种情况会让从库的 relay_log_info_repository表信息更新到最新值
1)如果此时主库再执行一个dml操作，我们会发现从库 relay_log_info_repository 中记录的位置被更新为最新值。

2)重启复制线程 stop slave; start slave;

3)设置 从库 sync_relay_log_info = 1

##官方文档中说到 如果 relay_log_info_repository = TABLE, 对于支持事务的存储引擎来说，会忽略 sync_relay_log_info 该参数，但是实测 发现对于 ddl 如果 sync_relay_log_info 大于 1，则 mysql.slave_relay_log_info 表中 主库binlog 位置不会及时刷新。设置为1 时会及时刷新。

4)如果你确定ddl语句已经在从库执行，那你可以 set global sql_slave_skip_counter=1; 跳过该错误，修复主库复制
 

##综上所述，如果你 设置了 relay_log_info_repository = TABLE，建议同时设置 sync_relay_log_info = 1