MySQL主从复制中常见的3个错误及填坑方案
主从复制错误一直是MySQL DBA一直填不完的坑,如鲠在喉,也有人说mysql主从复制不稳定云云,其实MySQL复制比我们想象中要坚强得多,而绝大部分DBA却认为只要跳过错误继续复制就好啦,接下来不发生错误就好了,其实跳过错误就会有数据不一致的风险,数据不一致可能还会越来越严重,而我就复制错误中反复出现的1045、1032和1062错误引起的数据库主从不一致的的现象进行深入分析及给出一套完善的解决方案。
(1) 【ERROR】1452:无法在外键的表插入参考主键没有的数据
(2) 【ERROR】1032:删除或更新数据,从库找不到记录
(3) 【ERROR】1062:从库插入数据,发生唯一性冲突
二、原因分析
【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:
普通主从复制环境
从库:
主库:
查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert或者update的对应的主键值。
查询item_discovery的外键约束c_item_discovery_1参考的表items对应主键值的数据行。
从库:
在items表插入主库查询出来的数据。
基于GTID复制环境
与普通主从复制环境处理方式相同。
【ERROR】1032:
发生1032可能是delete或者update时从库没有对应数据行,可以分两种情况处理:
(1)如果是Could not execute Delete_rows,则可以直接跳过错误
普通主从复制环境
从库:
基于GTID复制环境
从库:
找出复制出错时的executed_Gtid_Set,若出现多个,则选择跟Master_uuid相同的那一条。
(2)如果是Could not execute Update_rows,则需要在二进制日志找出出错位置的SQL,再找出该表在主库的对应的数据行,然后直接在从库插入这条数据,开启SQL线程恢复。
普通主从复制环境
从库:
主库:
查看主库在出错的相应位置的执行语句,可通过SQL得出当时update的对应的主键值。
查询item_discovery的对应主键值的数据行。
从库:
在items表插入主库查询出来的数据。
基于GTID复制环境
与普通主从复制环境处理方式相同。
【ERROR】1062:
普通主从复制环境
从库:
主库:
查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert的对应的主键值。
查询trends_uint表对应主键值的数据行。
从库:
在trends_uint表删除主库查询出来的数据。
基于GTID复制环境
与普通主从复制环境处理方式相同。
2.彻底解决方案
使用pt-table-checksum和pt-table-sync彻底修复数据不一致。
注意:使用pt工具包首先要安装pt工具包和安装perl模块。
(1) 从库停止复制
(2) 在主库创建校验信息表
(3) 在主库用pt-table-checksum校验主从数据一致性
在从库执行以下语句,查看Last_Error,发现数据不一致的表:
然后返回操作系统执行以下命令:
该命令可以查看该表是否发生数据不一致情况,若有,则使用pt-table-sync修复。
(4) 在主库用pt-table-sync打印出修复不一致数据的SQL(如果有外键约束,修复数据应先从外键参考的字段所属表开始修复),后将修复语句在从库执行。
四、优化建议
在复制由于1045、1032、1062的原因中断后,应使用三.1的临时解决方案,恢复复制后再在业务低谷使用pt-check-sum检查数据一致性。
检查完后可以在从库执行这条语句查看有无数据不一致表:
针对核心表,可以定制自动数据校验脚本,每周进行数据校验,但必须要在业务低谷进行校验哦!
本文来自云栖社区合作伙伴"DBAplus",原文发布时间:2016-05-11
MYSQL 索引成本计算,为什么MYSQL预判后选择了错误索引? IO 成本: 即从磁盘把数据加载到内存的成本,默认情况下,读取数据页的 IO 成本是 1,MySQL 是以页的形式读取数据的,即当用到某个数据时,并不会只读取这个数据,而会把这个数据相邻的数据也一起读到内存中,这就是有名的程序局部性原理,所以 MySQL 每次会读取一整页,一页的成本就是 1。所以 IO 的成本主要和页的大小有关
mysql操作中 出现You can‘t specify target table for update in FROM clause错误的解决方法 这个错误实际上也不能称之为咱们sql语句写的不行,实际上是我们在一些细节上没有遵循mysql的语法规范。 问题所在:我们一个sql语句中先select这个表,然后再update这个表的内容。 错误示范:
nivicat复制mysql数据库[Err] [Dtf] 1273 - Unknown collation: utf8mb4_0900_ai_ci 错误 问题的原因是两个数据库待转移的表的字符格式不能被被转移数据库识别。例如mysql8的utf8mb4_0900_ai_ci格式再mysql5中就不支持。就需要更改数据库字符编码和各个varchar等字段的编码方式
相关文章
- MySql中文乱码问题(3)
- mysql binlog 参数_MySQL Binlog常用参数
- Neor Profile SQL is the solution for developers wanting to profile the MySQL queries. This software allows the tracking of all queries to MySQL server from your application or website.
- VS2013与MySql建立连接;您的项目引用了最新实体框架;但是,找不到数据链接所需的与版本兼容的实体框架数据库 EF6使用Mysql的技巧
- 让 MySQL 支持 emoji 存储
- 【MySQL高级】Mysql并发参数调整及常用SQL技巧
- 【MySQL高级】MySql中常用工具及Mysql 日志
- mysql timestamp类型字段的CURRENT_TIMESTAMP与ON UPDATE CURRENT_TIMESTAMP属性
- MySQL 八股文中的联合索引最左匹配原则结论正确? 值得去亲手验证一下,纠正全网错误结论
- 阿里云重启之后mysql.sock丢了
- 从小型网站到超大规模网站的MySQL参考架构
- MySQL 中 You can't specify target table '表名' for update in FROM clause错误解决办法
- [MySQL] mysql地理位置服务geometry字段类型
- MySQL zabbix
- 【MySQL进阶-03】深入理解mysql的索引分类,覆盖索引,覆盖索引失效,回表,MRR
- 【MySQL进阶-09】深入理解mysql执行的底层机制
- mysql 慢查询日志,灾难日志恢复,错误日志
- MySQL修改字符集步骤(字段插入中文提示错误时解决办法)
- MySql视图
- 转 mysql的恢复过程非常慢
- 转 MySQL shell脚本执行错误 $'r':command not found
- 查看mysql事务的隔离级别
- mysql 索引类型
- mysql存在则更新,mysql插入 on duplicate key update
- 【C#】使用MySql.Data.dll连接MySQL数据库