mysql主从同步报错解决

查看mysql error.log日志如下

2021-08-19T07:19:04.118011+08:00 14933683 [ERROR] Slave SQL for channel '': Could not execute Update_rows event on table xxxdb.xxxtable; Can't find record in 'xxxtable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000118, end_log_pos 615106549, Error_code: 1032
2021-08-19T07:19:04.118060+08:00 14933683 [Warning] Slave: Can't find record in 'xxxtable' Error_code: 1032
2021-08-19T07:19:04.118233+08:00 14933683 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000118' position 615075712.

可初步分析主从同步失败是因为从库在update xxxdb库xxxtable时，从库xxxtable某条记录不存在，导致update失败
执行如下语句查看具体的sql，其中mysql-bin.000118和615106549取于错误日志中的‘mysql-bin.000118, end_log_pos 615106549’

mysqlbinlog —no-defaults —base64-output=decode-rows -v /data/mysql/binlog/mysql-bin.000118|grep -A 200 615106549

输出如下,如果涉及到的sql比较多，200行不能完全显示，可自行调整

此次导致主从复制出问题就是因为截图中的某个sql在从库不存在，主库执行了update，到从库是就报错了，将从库对应记录手动插入即可