MySQL从库重启导致从库大量主键冲突引发的深思

问题描述

由于mysql的从库机器重启，在机器恢复之后mysqld服务恢复正常的时候

重新启动slave，start slave 发现从库发生了大量的主键冲突报错，

为了先保证服务正常，先修改从库配置文件跳过1062主键冲突复制错误，

保证服务正常。

问题分析

–问题发生之后，有点想不通为什么会发生主键冲突，首先从库是没有写入的，所以肯定是从库执行了两遍主库的binlog

–分析过程
–先看一下复制从库的relay相关参数
root@(none) 11:25:58>show global variables like ‘%relay%’
-> ;
±--------------------------±------------------------------------+
| Variable_name | Value |
±--------------------------±------------------------------------+
| max_relay_log_size | 0 |
| relay_log | /data1/mysql/relaylog |
| relay_log_basename | /data1/mysql/relaylog |
| relay_log_index | /data1/mysql/mysqld_relay_bin.index |
| relay_log_info_file | /data1/mysql/relay_log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
±--------------------------±------------------------------------+
11 rows in set (0.00 sec)

/*
针对上述参数简单进行解释一下
max_relay_log_size：标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
relay_log：定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
relay_log_index：同relay_log，定义relay_log的位置和名称；
relay_log_info_file：设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置），也可以配置记录到mysql库中的slave_relay_log_info表中；
relay_log_purge：是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。开启该参数后，记得开启relay-log-purge参数。
relay_log_space_limit：防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用；
sync_relay_log：这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是10000，可动态修改，建议采用默认值。
sync_relay_log_info：这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是10000，可动态修改，建议采用默认值。
*/

–relay_log_recovery
关于这个参数这里单独说一下，可能字面意思会有点难理解
这里大家可以看一下这篇测试文章，可能会有一个很好地理解。
https://www.cnblogs.com/ddzj01/p/11592148.html

从库宕机数据恢复流程图

#补充一个参数,sync_master_info即经过多少个events将进度刷新master.info中

#所以针对以上的东西，我们可以知道问题
#恢复步骤如下
1.重启后relay_log_recovery=0，所以以master.info文件中的位点重新从主库拉binlog进行同步
2.由于sync_master_info参数等于10000（默认值），可能当时同步到9999个events的时候机器挂掉，
但是此时master.info文件此时还没有到达写入文件的条件，即master.info没有变化
举个例子：比如master.info文件为binlog23号文件第一个events的位点（比如是100号位点），按理来说经过1w个events之后
master.info才会变化为23号文件10100位点，但是我们这会儿经过了9999个机器挂掉了，所以此时文件并没有变化还是binlog23号文
件第一个events的100号位点
3.所以经过上面重启后还是读的是23号文件100号位点，但是实际从库可能已经执行到23号文件9999位点，所以在此启动同步，
这9999个events会重新同步，从而造成主键冲突报错

问题解决方案

建议在保证性能的情况下将参数设置为以下方式

当记录主从复制的文件设置为table的时候，根据官方文档的提示

这里我们可以看到当我们将对应的info文件设置为写table的时候，

sync_relay_log_info = 10000的时候，是每个事务之后写表，所以挂掉之后

relay_log_recovery=0 直接读取relay_log_info表覆盖master.info