MySQL运维实战(3.2) 常见数据库连接失败问题排查

作者：俊达
我们经常会遇到一些数据库连接失败或访问报错的问题。收集并分析具体的报错信息，可以帮助我们迅速定位问题。
在这里插入图片描述

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)