作者：俊達(dá)
我們經(jīng)常會(huì)遇到一些數(shù)據(jù)庫(kù)連接失敗或訪問(wèn)報(bào)錯(cuò)的問(wèn)題。收集并分析具體的報(bào)錯(cuò)信息，可以幫助我們迅速定位問(wèn)題。
1、客戶端到服務(wù)端的網(wǎng)絡(luò)是否暢通，服務(wù)端端口是否能連通。
使用ping、telnet等工具探測(cè)服務(wù)端的端口是否能訪問(wèn)。
[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
如果端口不通，要先排除網(wǎng)絡(luò)問(wèn)題，如是否有防火墻限制，服務(wù)端是否正常。
2、如果端口能訪問(wèn)，需要看客戶端的報(bào)錯(cuò)信息。
客戶端或者編程接口連接數(shù)據(jù)庫(kù)失敗，通常會(huì)有相應(yīng)的錯(cuò)誤消息。通過(guò)錯(cuò)誤消息可以判斷問(wèn)題原因，常見(jiàn)的連接失敗、查詢報(bào)錯(cuò)的原因如下：
- 賬號(hào)密碼錯(cuò)誤
- 數(shù)據(jù)庫(kù)連接數(shù)占滿
- 數(shù)據(jù)庫(kù)賬號(hào)被鎖定
- 超出max-connect-error限制
- 客戶端連接池滿了
- 連接會(huì)話被KILL
- 會(huì)話超時(shí)
- 對(duì)應(yīng)的客戶端報(bào)錯(cuò)信息
[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查看錯(cuò)誤碼的含義
如果服務(wù)端返回的報(bào)錯(cuò)信息中有錯(cuò)誤碼，如下面日志中的(113)，可以使用perror查看對(duì)應(yīng)的錯(cuò)誤信息
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服務(wù)端日志文件
mysql服務(wù)端的日志文件中包含重要信息。如果連接失敗，或者執(zhí)行sql有異常，可以到日志文件中查看是否有相關(guān)信息。日志文件的路徑由參數(shù)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分析包
對(duì)于一些更隱蔽的問(wèn)題，如果使用上面幾種方法都無(wú)法定位問(wèn)題原因，可能需要使用tcpdump工具抓包進(jìn)行分析。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-811747.html
通過(guò)以上系列的排查和解決步驟，及時(shí)采取有效的措施，能夠使得數(shù)據(jù)庫(kù)連接問(wèn)題得以迅速解決，進(jìn)一步確保系統(tǒng)的正常運(yùn)行。更多技術(shù)信息歡迎查看云掣官網(wǎng)https://yunche.pro/?t=yrgw文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-811747.html
到了這里，關(guān)于MySQL運(yùn)維實(shí)戰(zhàn)(3.2) 常見(jiàn)數(shù)據(jù)庫(kù)連接失敗問(wèn)題排查的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
