作者：俊达
我们经常会遇到一些数据库连接失败或访问报错的问题。收集并分析具体的报错信息，可以帮助我们迅速定位问题。
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://www.toymoban.com/news/detail-811747.html
通过以上系列的排查和解决步骤，及时采取有效的措施，能够使得数据库连接问题得以迅速解决，进一步确保系统的正常运行。更多技术信息欢迎查看云掣官网https://yunche.pro/?t=yrgw文章来源地址https://www.toymoban.com/news/detail-811747.html
到了这里，关于MySQL运维实战(3.2) 常见数据库连接失败问题排查的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
