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)
4、使用tcpdump分析包
对于一些更隐蔽的问题，如果使用上面几种方法都无法定位问题原因，可能需要使用tcpdump工具抓包进行分析。
