UDN-企业互联网技术人气社区

板块导航

浏览  : 930
回复  : 0

[讨论交流] MySQL主从复制中常见的3个错误及填坑方案

[复制链接]
爱飞的火车的头像 楼主
发表于 2016-5-20 14:45:18 | 显示全部楼层 |阅读模式
  MySQL主从复制中常见的3个错误及填坑方案

    一、问题描述

  主从复制错误一直是MySQL DBA一直填不完的坑,如鲠在喉,也有人说mysql主从复制不稳定云云,其实MySQL复制比我们想象中要坚强得多,而绝大部分DBA却认为只要跳过错误继续复制就好啦,接下来不发生错误就好了,其实跳过错误就会有数据不一致的风险,数据不一致可能还会越来越严重,而我就复制错误中反复出现的1045、1032和1062错误引起的数据库主从不一致的的现象进行深入分析及给出一套完善的解决方案。

  (1) 【ERROR】1452:无法在外键的表插入参考主键没有的数据
5cd5a73657884bcf95d41e5663892252.jpg


  (2) 【ERROR】1032:删除或更新数据,从库找不到记录
7533c6cb74f1430891fba76e1db6f16c.jpg

  (3) 【ERROR】1062:从库插入数据,发生唯一性冲突
190133a9cff84aa19c9c925046a73a2a.jpg

  二、原因分析

  【ERROR】1452:无法在外键的表插入或更新参考主键没有的数据。由于item_discovery.itemid字段(外键)参考了items.itemid字段(主键),当要在item_discovery表插数据时,如果items表的主键没有对应的数据,则无法插入,报1452错误。此时可以检查参考的表的主键是否有主库对应的数据,如果有,则插入参考的表相应的数据,再开启复制恢复SQL线程。

  【ERROR】1032:删除或更新从库的数据,从库找不到记录。此时,主库的数据是比从库新的,可以采取从库添加相同的数据在开启复制恢复SQL线程。

  【ERROR】1062:从库插入数据,发生唯一性冲突。此时从库已经有相同主键的数据,如果再插入相同主键值的数据则会报错。可以查看主库的改行数据与从库的要插入数据是否一致,如一致则跳过错误,恢复SQL线程,如不一致,则以主库为准,将从库的该行记录删除,再开启复制。

  如果当前高可用架构为Master-Master,则以下均在从库的操作都必须set sql_log_bin=0,避免从库执行的语句同步到主库(恢复时以主库的数据为准)。

  三、标准化处理方案

  (旨在落成标准化处理方案)

  1.临时解决方案(业务运行期间不适宜使用数据对比和修复工具)

  【ERROR】1452:
477889ebdb1440828c18487dd0f051cd.jpg

  普通主从复制环境

  从库:
a106bdfb40b2493cb1039af17d30ecba.jpg

  主库:

  查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert或者update的对应的主键值。
540453f6154f465eb9b61e4c5b75f2c4.jpg

  查询item_discovery的外键约束c_item_discovery_1参考的表items对应主键值的数据行。

  从库:

  在items表插入主库查询出来的数据。
e848288cda554b9aa9e644eb4e2947c4.jpg

  基于GTID复制环境

  与普通主从复制环境处理方式相同。

  【ERROR】1032:
cbd788795cb14035918b1846a305c3b4.jpg

  发生1032可能是delete或者update时从库没有对应数据行,可以分两种情况处理:

  (1)如果是Could not execute Delete_rows,则可以直接跳过错误

  普通主从复制环境

  从库:
97f9cbb4a55641df9bb023f287e07193.jpg


  基于GTID复制环境

  从库:

  找出复制出错时的executed_Gtid_Set,若出现多个,则选择跟Master_uuid相同的那一条。
43c97f91331e413b970cec10bcd410f5_th.jpg

  (2)如果是Could not execute Update_rows,则需要在二进制日志找出出错位置的SQL,再找出该表在主库的对应的数据行,然后直接在从库插入这条数据,开启SQL线程恢复。

  普通主从复制环境

  从库:
2980a74b93de41a1ab143fe8bd02550a.jpg

  主库:

  查看主库在出错的相应位置的执行语句,可通过SQL得出当时update的对应的主键值。
fcd48135605b4828b295bc2e475a13b7.jpg

  查询item_discovery的对应主键值的数据行。

  从库:

  在items表插入主库查询出来的数据。
aa26d5ce6e174f2a8f7a727d376ae128.jpg


  基于GTID复制环境

  与普通主从复制环境处理方式相同。

  【ERROR】1062:

  
5ed86e115c5f4093a27415314327682e.jpg

  普通主从复制环境

  从库:
c74321c19bca46149b100e219723411e.jpg

  主库:

  查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert的对应的主键值。
5caf32fc4f9b485e95d4454f565e527c.jpg

  查询trends_uint表对应主键值的数据行。
afd3ec8a9c1c4368b16f5cf64c624067.jpg


  从库:

  在trends_uint表删除主库查询出来的数据。
5e87af9152de4cb3afdd95e93e53066b.jpg

  基于GTID复制环境

  与普通主从复制环境处理方式相同。

  2.彻底解决方案

  使用pt-table-checksum和pt-table-sync彻底修复数据不一致。

  注意:使用pt工具包首先要安装pt工具包和安装perl模块。

  (1) 从库停止复制
446c49b1ae1d44b580c75394d0808689.jpg


  (2) 在主库创建校验信息表
06ecb825af9f4f0b8533559129acbdc8_th.jpg


  (3) 在主库用pt-table-checksum校验主从数据一致性

  在从库执行以下语句,查看Last_Error,发现数据不一致的表:

  然后返回操作系统执行以下命令:
a36b7741c3d04f55a67f3a2e5f21387c.jpg

  该命令可以查看该表是否发生数据不一致情况,若有,则使用pt-table-sync修复。

  (4) 在主库用pt-table-sync打印出修复不一致数据的SQL(如果有外键约束,修复数据应先从外键参考的字段所属表开始修复),后将修复语句在从库执行。
8ffa11030e9b4477be7ba55aa6b3dd98.jpg


  四、优化建议

  在复制由于1045、1032、1062的原因中断后,应使用三.1的临时解决方案,恢复复制后再在业务低谷使用pt-check-sum检查数据一致性。

  检查完后可以在从库执行这条语句查看有无数据不一致表:
9d4cbaca38d046c1aa5e1ccb1a0be91e.jpg

  针对核心表,可以定制自动数据校验脚本,每周进行数据校验,但必须要在业务低谷进行校验哦!

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部