MySQL数据库连接问题诊断分析

152次阅读
没有评论

共计 2566 个字符,预计需要花费 7 分钟才能阅读完成。

MySQL 服务端使用插件的方式认证客户端的用户身份。认证插件和用户相关,不同用户可以使用不同的插件进行密码验证。创建用户时,如果不显式指定,会使用参数 default_authentication_plugin 指定的插件。从 MySQL 8.0 开始,使用 caching_sha2_password 作为默认的认证插件,而 5.7 使用的默认插件是 mysql_native_password。

MySQL 支持使用 TLS 协议建立加密连接,默认开启。使用 MySQL 客户端登录服务器后,执行 \s,查看 SSL 这一行输出,如果显示“Cipher in use …”,则说明当前连接启用了加密。

可以在连接数据库时指定参数,不开启连接加密,此时“SSL”这一行显示“Not in use”。

mysql --ssl-mode=disabled -uroot -p

当数据库连接不上时,分析问题的一般思路如下。

检查数据库监听是否正常开启

在数据库服务器上通过 netstat 或 ss 命令查看数据库端口监听是否正常开启。

ss -nltp | grep 3306
netstat -nltp | grep 3306

检查客户端到服务端之间网络是否连通

可以使用 telnet 等工具检查客户端到服务端口是否连通。

telnet 172.16.121.234 3306

如果端口不通,使用 MySQL 客户端访问数据库时,会有相应的报错信息。注意报错信息最后括号里的数字(操作系统返回错误码,不用操作系统不一样),可使用 MySQL 提供的工具 perror 查看跟错误码关联的错误信息。

perror 111
perror 113

是否认证阶段出问题

常见报错信息:

  • ERROR 2059,Authentication plugin‘caching_sha2_password’cannot be loaded
    • 客户端版本太低,不支持 caching_sha2_password 认证插件
    • 解决方法:使用新版本客户端
  • ERROR 2061,Authentication requires secure connection
    • 使用 caching_sha2_password 认证插件时,用户首次登录时还没有被缓存,服务端需要获取用户的明文密码,如果客户端没有开启连接加密,发送明文密码有安全风险,就会报这个错误
    • 解决方法:开启连接加密,或者在客户端指定 get-server-public-key 选项
    • 不同客户端指定参数的方式可能会有差异,比如使用 JDBC 时需要添加连接属性 allowPublicKeyRetrieval=true
    • MySQL 备库连接主库时,也可能遇到一样的问题,可以在建立复制时,指定 GET_MASTER_PUBLIC_KEY 或 GET_SOURCE_PUBLIC_KEY 选项
    • 使用 MySQL 组复制(MGR)如果遇到这个问题,可以通过设置参数 group_replication_recovery_get_public_key 来解决
  • ERROR 1045: Access denied for user‘username’@‘hostname’
    • 客户端输入密码不正确引起
    • 如果强制要求用户使用加密连接,或者对客户端证书有要求,而客户端没有满足这些条件,那么连接时也会报这个错

数据库连接数限制

参数 max_connections 限制数据库允许创建的总连接数。参数 max_user_connections 限制同一个用户允许创建的最大连接数。如果连接数超过限制,会报下面这 3 个错误:

  • ERROR 1040 (08004): Too many connections
  • ERROR 1203 (42000): User test already has more than‘max_user_connections’active connections
  • ERROR 1226 (42000): User‘test’has exceeded the‘max_user_connections’resource (current value: 2)

操作系统资源限制

如果操作系统资源超出限制,会导致客户端连接失败,MySQL 服务端无法创建新的线程:ERROR 1135: Can’t create a new thread (errno 11)

如果文件句柄数超出限制,在数据库错误日志中还可能会出现这样的报错信息:[ERROR] [MY-010283] [Server] Error in accept: Too many open files

其它错误

ERROR 1129 (HY000): Host ‘172.16.121.237’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’。

  • 客户端从某台机器连接数据库时,连续出错,出错次数超过参数 max_connect_errors 设置后,服务端会禁止这台机器后续连接,这里限制的是客户端 IP。从 performance_schema.host_cache 表里,可看到客户端连接错误次数
  • 解决方法:执行 flush hosts 操作
  • 不是所有连接错误都会引起客户端被禁,比如密码错误并不会导致客户端被禁。host_cache 表 COUNT_HANDSHAKE_ERRORS 达到 max_connect_errors,才会导致客户端被禁。比如连续 telnet mysql 端口会引起这个问题,或者使用了无效 ssl 证书可能会导致这个问题

连接中断问题

情况 1:连接被 Kill

如果有人使用 Kill 命令终止某个会话,那么原先客户端在执行 SQL 时,就会发现连接已经中断。

情况 2:数据库重启

如果数据库发生重启,那么原先所有连接都会断开。可通过查看数据库 Uptime 状态变量来判断数据库最近是否有重启。Uptime 记录了数据库从启动后至今经过的秒数。

show global status where variable_name in ('Uptime');

情况 3:连接空闲时间超时

MySQL 中全局变量 interactive_timeout(交互式连接)和 wait_timeout(非交互式连接)用来控制连接空闲超时,如果一个连接在指定时间内没有发起任何请求,就会被服务器断开。

情况 4:代理(Proxy)超时

如果在数据库之前配置了代理,而代理空闲超时时间比较短。应用程序通过代理访问数据库时,就可能会遇到连接中断问题。

正文完
post-qrcode
 0
三毛
版权声明:本站原创文章,由 三毛 于2024-11-24发表,共计2566字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)