使用mysqlsh通过socket连接关闭时MySQL Router反复报错的排查与解决

环境信息 #

• 操作系统：CentOS 7
• MySQL 版本：8.0.32
• MySQL Router 版本：8.0.32
• MySQL Shell 版本：8.0.32

报错日志 #

2023-02-09 16:00:01 routing INFO [7f258c18c700] [routing:bootstrap_rw] incrementing error counter for host of /opt/mysqlrouter/mysql.sock (now 0)
2023-02-09 16:00:01 routing ERROR [7f258c18c700] classic::loop() processor failed: Bad message (generic:74)
2023-02-09 16:02:01 routing INFO [7f258b169700] [routing:bootstrap_rw] /opt/mysqlrouter/mysql.sock closed connection before finishing handshake
2023-02-09 16:02:01 routing INFO [7f258b169700] [routing:bootstrap_rw] incrementing error counter for host of /opt/mysqlrouter/mysql.sock (now 0)
2023-02-09 16:02:01 routing ERROR [7f258b169700] classic::loop() processor failed: Bad message (generic:74)
2023-02-09 16:04:01 routing INFO [7f258a167700] [routing:bootstrap_rw] /opt/mysqlrouter/mysql.sock closed connection before finishing handshake
2023-02-09 16:04:01 routing INFO [7f258a167700] [routing:bootstrap_rw] incrementing error counter for host of /opt/mysqlrouter/mysql.sock (now 0)
2023-02-09 16:04:01 routing ERROR [7f258a167700] classic::loop() processor failed: Bad message (generic:74)
2023-02-09 16:06:01 routing INFO [7f2589144700] [routing:bootstrap_rw] /opt/mysqlrouter/mysql.sock closed connection before finishing handshake
2023-02-09 16:06:01 routing INFO [7f2589144700] [routing:bootstrap_rw] incrementing error counter for host of /opt/mysqlrouter/mysql.sock (now 0)
2023-02-09 16:06:01 routing ERROR [7f2589144700] classic::loop() processor failed: Bad message (generic:74)
2023-02-09 16:08:02 routing INFO [7f258c18c700] [routing:bootstrap_rw] /opt/mysqlrouter/mysql.sock closed connection before finishing handshake
2023-02-09 16:08:02 routing INFO [7f258c18c700] [routing:bootstrap_rw] incrementing error counter for host of /opt/mysqlrouter/mysql.sock (now 0)
2023-02-09 16:08:02 routing ERROR [7f258c18c700] classic::loop() processor failed: Bad message (generic:74)

报错原因 #

1. Router 健康检查触发异常：MySQL Router 默认每60秒对后端实例（包括socket连接的实例）执行健康检查。当mysqlsh通过socket快速关闭连接时，Router的健康检查握手流程未完成，导致连接提前终止，触发协议错误。
2. Socket连接协议不完整：mysqlsh关闭socket连接时未完成MySQL协议的完整握手流程，Router收到不完整的协议数据，被判定为无效消息，抛出"Bad message"错误。

解决方法 #

• 调整健康检查参数：
  修改MySQL Router配置文件（如mysqlrouter.conf），延长健康检查间隔或禁用socket实例的健康检查：

  [routing:bootstrap_rw]
  health_check_interval = 300  # 调整为5分钟，按需修改
  # 生产环境不建议禁用，若需临时测试可添加：health_check = false
  

  修改后重启Router：systemctl restart mysqlrouter

• 规范连接关闭流程：
  使用mysqlsh连接后，执行\q或exit命令正常退出，避免强制关闭连接导致协议异常。

• 升级组件版本：
  该协议兼容性问题在MySQL 8.0.33及后续版本中已修复，建议将MySQL Server、Router、Shell升级至最新稳定版。

• 关闭Router的Socket监听：
  若无需通过Router的socket连接，可修改配置仅监听TCP端口：

  [routing:bootstrap_rw]
  bind_address = 0.0.0.0:3306  # 仅保留TCP端口监听
  

内容的提问来源于stack exchange，提问作者sateesh