mysql主从同步报错，提示Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most

╰半橙微兮° 2022-03-28 03:42 335阅读 0赞

## mysql主从不同步，提示Last\_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ##

昨天zabbix突然发送告警信息，告警说是mysql的slave停掉了，登录到数据库从库，进去看mysql的进程是正常的，然后去看下mysql的error日志  
![在这里插入图片描述][20190109111145268.png]

1，进入数据库查看主从状态 show slave status\\G;  
根据提示查询到的错误信息  
Last\_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’ at master log mysql-bin.000063, end\_log\_pos 171661170. See error log and/or performance\_schema.replication\_applier\_status\_by\_worker table for more details about this failure or others, if any.

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70]

2，根据上图的提示，查询到的异常数据出现在opp\_strack表中  
select \* from performance\_schema.replication\_applier\_status\_by\_worker\\G；  
确定事务发生在表 opp\_strack 上，定位到表,再去排查是哪一条记录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 1]

3，去主库的对应的binlog日志中去查找数据库操作记录  
mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /zdata/mysql\_data/mysql-bin.000063 | grep -A 20 “171661170” --color

–/zdata/mysql\_data/mysql-bin.000063是在主从状态中查找的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 2]  
binlog日志中查询到opp\_strack表的更新操作，然后对比主从库opp\_strack表中字段“1683”的数据，我这边得到的数据是不一致的，发现从库opp\_strack中被插入进来三条数据，冲掉了主库的id值，然后将从库中的三条数据删除掉

4，主从数据恢复一致后需要在slave上跳过报错的事务  
在从库中执行  
Stop slave;  
Set @@SESSION.GTID\_NEXT=’ d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’  
Begin;  
Commit;  
Set @@SESSION.GTID\_NEXT = AUTOMATIC;  
Start slave;  
再次查看从库状态，恢复正常即解决  
Show slave status\\G  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 3]

[20190109111145268.png]: /images/20220328/7503679c9f7e47cca805b0bff332dd76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70]: /images/20220328/5a630725ca1343db8a91715a7588faa6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 1]: /images/20220328/eb2a0778552649cb826132dc148a4488.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 2]: /images/20220328/41cfe8ae7c9c41fb9cae4a3de20d7d07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21lbW9yeTYzNjQ_size_16_color_FFFFFF_t_70 3]: /images/20220328/c6056f8f61c24abe8985e7ce655a84e8.png