复制中断 mysql_mysql5.7线上环境从库复制中断（update）

最新推荐文章于 2022-06-22 11:20:54 发布

yymWater

最新推荐文章于 2022-06-22 11:20:54 发布

阅读量369

点赞数

文章标签：复制中断 mysql

本文链接：https://blog.csdn.net/weixin_29603387/article/details/113252616

版权

在2018年4月27日，线上环境中遇到MySQL5.7从库复制中断的问题，错误日志显示因主键冲突导致Slave SQL执行Write_rows事件失败。错误代码为1062，且在尝试重新启动复制时，警告可能造成数据不一致状态。在后续处理中，调整了复制设置并初始化了新的复制工作线程，但之后又遇到了Update_rows事件执行失败，错误代码1032，提示无法在BI_BlackUser表中找到记录。建议检查数据一致性，并考虑使用更安全的复制方式。

摘要由CSDN通过智能技术生成

[root@gfs1 free]# tail -n 100 /home/mysql3307/log/mysqld.log

2018-04-27T09:00:02.219598Z 17 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '8a0446c4-cce4-11e5-865d-002564fbe7a0:540311459' at master log mysql-bin.000717, end_log_pos 602665891; Could not execute Write_rows event on table hp_cougar_transfer.BI_AppointmentOrder; Duplicate entry '89677392' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 602665891, Error_code: 1062

2018-04-27T09:00:02.219734Z 15 [Warning] Slave SQL for channel'': Slave worker thread has failed to apply an event. As a consequence, the coordinator thread is stopping execution. Error_code: 0

2018-04-27T09:00:02.219773Z 15 [Warning] Slave SQL for channel'': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

2018-04-27T09:00:19.368059Z 14 [ERROR] Error reading packet from server for channel'': Lost connection to MySQL server during query (server_errno=2013)

2018-04-27T09:00:19.368102Z 14 [Note] Slave I/O thread killed while reading event for channel''2018-04-27T09:00:19.368114Z 14 [Note] Slave I/O thread exiting for channel'', read up to log'mysql-bin.000717', position 923581274

2018-04-27T09:00:28.997607Z 13 [Note]'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='20.1.31.44', master_port= 3306, master_log_file='mysql-bin.000717', master_log_pos= 923581274, master_bind=''. New state master_host='20.1.31.44', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.

2018-04-27T09:00:33.576115Z 18 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the'START SLAVE Syntax'in the MySQL Manual for more information.

2018-04-27T09:00:33.579294Z 19 [Note] Slave SQL thread for channel''initialized, starting replication in log'FIRST'at position 0, relay log'/home/mysql3307/relay/relay-bin.000001'position: 4

2018-04-27T09:00:33.583110Z 18 [Note] Slave I/O thread for channel'': connected to master'repl@20.1.31.44:3306',replication started in log'FIRST'at position 4

2018-04-27T09:00:37.154304Z 21 [ERROR] Slave SQL for channel'': Worker 1 failed executing transaction'8a0446c4-cce4-11e5-865d-002564fbe7a0:540311459'at master log , end_log_pos 602665891; Could not execute Write_rows event on table hp_cougar_transfer.BI_AppointmentOrder; Duplicate entry'89677392'for key'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 602665891, Error_code: 1062

2018-04-27T09:00:37.155089Z 19 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

2018-04-27T09:00:37.155143Z 19 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

2018-04-27T09:06:26.445883Z 13 [Note] @@GLOBAL.GTID_PURGED was changed from '' to '8a0446c4-cce4-11e5-865d-002564fbe7a0:1-540640764'.2018-04-27T09:06:26.445922Z 13 [Note] @@GLOBAL.GTID_EXECUTED was changed from '' to '8a0446c4-cce4-11e5-865d-002564fbe7a0:1-540640764'.2018-04-27T09:06:30.593608Z 22 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000717' at position 602663272, relay log '/home/mysql3307/relay/relay-bin.000002' position: 414

2018-04-27T09:08:21.784025Z 23 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '8a0446c4-cce4-11e5-865d-002564fbe7a0:540640886' at master log mysql-bin.000717, end_log_pos 925167524; Could not execute Update_rows event on table hp_cougar_transfer.BI_BlackUser; Can't find record in'BI_BlackUser', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000717, end_log_pos 925167524, Error_code: 1032

2018-04-27T09:08:21.792219Z 22 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756