MySQL运维实战(3.2) 常见数据库连接失败问题排查
2024-01-09 16:39:24
作者:俊达
我们经常会遇到一些数据库连接失败或访问报错的问题。收集并分析具体的报错信息,可以帮助我们迅速定位问题。
1、客户端到服务端的网络是否畅通,服务端端口是否能连通。
使用ping、telnet等工具探测服务端的端口是否能访问。
[root@box3 ~]# telnet 172.16.20.51 3306
Trying 172.16.20.51...
Connected to 172.16.20.51.
Escape character is '^]'.
J
5.7.3-m/i
_Y(^#XTCcie|Pmysql_native_password]
!#08S01Got packets out of orderConnection closed by foreign host.
[root@box3 ~]# telnet 172.16.20.52 3306
Trying 172.16.20.52...
telnet: connect to address 172.16.20.52: Connection refused
如果端口不通,要先排除网络问题,如是否有防火墙限制,服务端是否正常。
2、如果端口能访问,需要看客户端的报错信息。
客户端或者编程接口连接数据库失败,通常会有相应的错误消息。通过错误消息可以判断问题原因,常见的连接失败、查询报错的原因如下:
- 账号密码错误
- 数据库连接数占满
- 数据库账号被锁定
- 超出max-connect-error限制
- 客户端连接池满了
- 连接会话被KILL
- 会话超时
- 对应的客户端报错信息
[root@box3 ~]# mysql -udemo -h172.16.20.51 -pdem
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'demo'@'box3' (using password: YES)
[root@box3 ~]# mysql -h 172.16.20.51 -udemo -pdemo
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1203 (42000): User demo already has more than 'max_user_connections' active connections
[root@box3 ~]# mysql -udemo -h172.16.20.51 -pdemo
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
[root@box3 ~]# mysql -udemo -h172.16.20.51 -pdemo
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '172.16.20.53' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
[root@box3 ~]# mysql -udemo -h172.16.20.51 -pdemo
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'demo'@'box3'. Account is locked.
mysql> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Cant connect to MySQL server on '172.16.20.51' (113)
ERROR:
Cant connect to the server
[root@box3 ~]# mysql -h 172.16.20.51 -udemo -pdemo -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Cant connect to MySQL server on '172.16.20.51' (111)
mysql> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
使用perror查看错误码的含义
如果服务端返回的报错信息中有错误码,如下面日志中的(113),可以使用perror查看对应的错误信息
ERROR 2003 (HY000): Cant connect to MySQL server on ‘172.16.20.51’ (113)
[root@box3 ~]# perror 113
OS error code 113: No route to host
[root@box3 ~]# perror 110
OS error code 110: Connection timed out
[root@box3 ~]# perror 11
OS error code 11: Resource temporarily unavailable
[root@box3 ~]# perror 111
OS error code 111: Connection refused
3、查看mysql服务端日志文件
mysql服务端的日志文件中包含重要信息。如果连接失败,或者执行sql有异常,可以到日志文件中查看是否有相关信息。日志文件的路径由参数log_error指定。
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+
2021-04-06T22:55:50.623621Z 8 [Note] Got packets out of order
2021-04-06T23:00:40.347126Z 9 [Note] Access denied for user 'test'@'box3' (using password: YES)
2021-04-06T23:33:42.004939Z 8 [Note] Aborted connection 8 to db: 'unconnected' user: 'demo' host: 'box3' (Got timeout reading communication packets)
4、使用tcpdump分析包
对于一些更隐蔽的问题,如果使用上面几种方法都无法定位问题原因,可能需要使用tcpdump工具抓包进行分析。
通过以上系列的排查和解决步骤,及时采取有效的措施,能够使得数据库连接问题得以迅速解决,进一步确保系统的正常运行。更多技术信息欢迎查看云掣官网https://yunche.pro/?t=yrgw
文章来源:https://blog.csdn.net/weixin_43756308/article/details/135472741
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!