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日志
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.
2,根据上图的提示,查询到的异常数据出现在opp_strack表中
select * from performance_schema.replication_applier_status_by_worker\G;
确定事务发生在表 opp_strack 上,定位到表,再去排查是哪一条记录
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是在主从状态中查找的
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