replication的错误

最新推荐文章于 2024-04-18 19:01:54 发布

epee

最新推荐文章于 2024-04-18 19:01:54 发布

阅读量364

点赞数

本文讲述了在MySQL数据库复制过程中遇到的问题及解决办法。由于master和slave服务器上的max_allowed_packet设置不一致，导致复制失败。通过调整配置并手动设置复制起点解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

昨天replication又出问题了。master上更改了max_allowed_packet，但slave上的忘记改了，结果导致master上过大的SQLslave不能取到。之后虽然在slave上也更改了max_allowed_packet，但start slave仍然不能成功。错误日志中的记录是：


080409 18:37:31 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000251', position 607694155
080409 18:51:23 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000251' at position 605990219, relay log './mysql-relay-bin.001025' position: 89483173
080409 18:51:23 [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.000251' position 605990219
080409 18:51:23 [Note] Slave I/O thread: connected to master 'replicate@192.168.1.169:3306',  replication started in log 'mysql-bin.000251' at position 607694155
080409 18:51:24 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
080409 18:51:24 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when reading data from binary log

注意红色的部分，605990219开始是一个长达18M的SQL，slave的sql thread执行到这一个sql出错了，不过不知道原因。而io thread读到了日志中的607694155。我们使用change master to应该是给sql thread设开始位置，所以用change master to把master_log_pos设为605990219，再运行start slave就可以了。