背景
MySQL从库因为机器卡顿原因,重启机器,机器起来后需要重新启动从库
结果开启同步后报错
Last_SQL_Error: Could not execute Delete_rows event on table pay_gateway.tbl_accounting_notifyinfo; Can't find record in 'tbl_accounting_notifyinfo', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.003660, end_log_pos 66840371
原因分析
- 因为db复制情况的信息记录在relay log info、master info文件中(通过参数可以设置为文件,或者表)
公司master-info-repository = TABLE 、relay-log-info-repository = TABLE ,表分别为mysql.slave_master_info和 mysql.slave_relay_log_info
relay log :即读取过来的master 的binlog ,内容与格式与master 的binlog 一致
relay log info :记录SQL Thread 应用的relay log 的位置、文件号等信息
master info :记录IO Thread 读取master 的binlog 的位置、文件号、延迟等信息 - 因此当这三个文件如果不及时落地,则主机crash后会导致数据不一致(即使双一也会有问题,因为要考虑复制信息神马时候保存到表中)
- master info 与relay info 有如下3 个参数控制刷新:
| 参数 | 存储在FILE中 | 存储在table中 |
|---|---|---|
| sync_relay_log | 默认为10000 ,即每10000 次sync_relay_log events会刷新到磁盘(using fdatasync())。为0 则表示不刷新,交由OS 的cache 控制。大于0 则表示N个events写到Relay log后,刷到磁盘.IO线程复制到缓存后写入磁盘 | |
| sync_master_info | 当设置为0 ,则与events无关,由OS处理;如果大于0,则表示在sync_master_info次transactions后刷到磁盘,默认为10000 次刷新到磁盘 | 当设置为0 ,则表不做任何更新,设置为1 ,则每次事件会更新表 # 默认为10000 |
| sync_relay_log_info | 当设置为0 ,交由OS 刷新磁盘;如果大于0,则sync_relay_log_info次transactions后刷到磁盘.默认为10000 次刷新到磁盘 | INNODB 存储(支持事务),则无论为任何值,则都每次event 都会更新表(无论此参数值为多少)。 如果是非事务存储引擎,则为0时不更新表,大于0时则在N次events后更新.默认值都是10000 |
- 使用命令查看IO线程与SQL线程执行情况
mysql> select Master_log_name,Master_log_pos,'IO Thread 读取Master的positon' comments from mysql.slave_master_info
union select Master_log_name,Master_log_pos,'SQL Thread 执行Master的positon' comments from mysql.slave_relay_log_info;
- 导致复制出错的原因
在mysql意外crash的情况下,默认参数配置slave_relay_log_info记录的Master_log_name、Master_log_pos 要大于或等于 mysql.slave_master_info记录的Master_log_name、Master_log_pos
而重启复制后，IO线程拉主库binlog的postion是从slave_master_info记录的点开始的，会导致重复拉取
slave真正执行的点是slave_relay_log_info记录的点,由于重复拉取binlog，relay log会有重复，自然sql执行时会有冲突。
解决问题
- 修改参数配置 sync_relay_log = 1 sync_master_info = 1 sync_relay_log_info = 1,机器断电重启后DB复制正常,问题解决(但是会增加很大的负担)
- 跳过错误 change,或者设置skip_errors等
- 有个参数可以让mysql自动从最新的点开始同步
延伸问题
如果mysql.slave_master_info 的记录的点要比mysql. slave_relay_log_info点大 机器意外crash,重启后依然会有错误
问题分析
- SQL thread启动的位置和slave_relay_log_info 记录的位置一致(478386666) 。 IO thread 启动的位置由于mysql.slave_master_info 表记录被更新所以实际启动的点要大。
当db非正常关闭时,mysql.slave_master_info 表记录由于sync_master_info=10000 的设置导致部分event 信息没有记录到表中。
但是实际已经生成relay log 。那启动的时候IO Thread 是根据表中的记录开始启动IO ,这样就会导致没有记录到表中的relay log 被重复拉取了一次,SQL thread 重复应用
这段relay log 导致复制中断。那么这种问题是执行了重复拉取的一段relay log 导致主键冲突,跳过错误即可,并且数据不会丢失。
解决方法
- 设置sync_master_info=1 让表实时更新记录即可解决问题
- 手动重新change 一次也可避免复制中断
并且手动change 的点应该是按照mysql.slave_relay_log_info 表记录的点
为什么都是sql thread 都是同一个位置启动,为什么需要手动change 呢?
因为重新change 是会清空现有的relay log ,从指定位置重新拉取,而直接启动则是续写之前的relay log 。
解决方案汇总
- DB 复制正常关闭情况直接启动复制即可,如果非正常关闭则需要以mysql.slave_relay_log_info表记录的点重新change,记得一定要重新change,这样
可以清空之前生成的relay log重新拉取。 - 使用GTID复制,无需file、positon ,两张表中不会记录任何信息
- 设置参数为1,实时更新表记录。
sync_relay_log = 1
sync_master_info = 1 sync_relay_log_info = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE - 每次启动check一下slave_master_info和slave_relay_log_info 记录的positon是否一致,如果不一致按照slave_relay_log_info的点重新change后重启复制,切记需要重新change
- 明白这种复制中断的原因,跳过即可。不推荐设置为全1,IO压力加大会导致性能瓶颈,从监控看从20飙到60,机器还是单实例、无业务流量的情景。
- 启动时加上 --relay-log-recovery 参数 ,会在server启动时新创建一个Relay log文件，并让SQL线程指向新的Relay log，让IO线程的位置指向SQL线程执行的master位置然后继续读取Master的binlog
