MySQL Router連接數(shù)據(jù)庫失敗原因-1 _mysql

MySQL 是一個輕量級的連接轉發(fā)代理，用來將應用程序的連接轉發(fā)到MySQL數(shù)據(jù)庫。官方建議MySQL 與應用部署在相同服務器上，當?shù)讓覯ySQL高可用集群（通常是指MGR）的角色或服務器發(fā)生變化時，MySQL 能自動識別變化情況，并進行連接控制，無需應用修改配置，實現(xiàn)應用對數(shù)據(jù)庫的透明訪問。
當前使用MySQL 的用戶越來越多，但是關于MySQL 的問題分析較少。本文總結了常見的MySQL 連接失敗案例，供大家參考。
原因一：連接失敗次數(shù)（連續(xù)的）達到上限
1、問題現(xiàn)象
MySQL客戶端或應用代碼報錯出現(xiàn)關鍵字：Too many
[kingly@rhel7]$ mysql -uapp -pxxx -h127.0.0.1 -P23306mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1129 (HY000): Too many connection errors from 127.0.0.1:25084
2、問題原因
應用到MySQL 的連接失敗次數(shù)（連續(xù)的）達到上限（默認100）。
這里的錯誤連接不是指密碼輸入錯誤，而是指三次握手失敗。應用到數(shù)據(jù)庫的連接會先連接MySQL ，而MySQL 是一個http服務vb連接數(shù)據(jù)庫實例下載，所以在連接時，需先進行三次握手。三次握手失敗的原因，可能是網(wǎng)絡原因，也可能是應用本身連接異常。
【MySQL Router連接數(shù)據(jù)庫失敗原因-1】這里的連接失敗次數(shù)是指連續(xù)失敗次數(shù)，如果失敗在未達到上限時連接成功了，則錯誤次數(shù)自動清零。
對于連接失敗的報錯，MySQL 日志中會提示“”，表示三次握手未正常完成；同時，會記錄連接失敗的統(tǒng)計情況“1for 127.0.0.1 (max 100)” ，其中 1 是當前已連續(xù)錯誤的次數(shù)vb連接數(shù)據(jù)庫實例下載，max 100 表示最大的錯誤次數(shù)為100 。當達到上限時，會提示連接已被阻止（host 127.0.0.1）。以下是日志示例：

文章插圖

2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:47520 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 1 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:50818 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 2 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:52844 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 3 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:54380 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 4 connection errors for 127.0.0.1 (max 100)......2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 95 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:29112 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 96 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:34896 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 97 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:44970 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 98 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:63526 closed connection before finishing handshake2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 99 connection errors for 127.0.0.1 (max 100)2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:24348 closed connection before finishing handshake2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.12022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:23440 closed connection before finishing handshake2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.12022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:30882 closed connection before finishing handshake2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.1

3、解決方法
此方法會清零MySQL 當前的錯誤連接計數(shù)情況，能快速解決當前服務器上應用連不上數(shù)據(jù)庫的問題。
查看.conf配置文件中，是否有設置最大連接數(shù)，如果沒有配置，默認是100 。
修改配置文件.conf，在[:]模塊下添加=XXXX，XXXX為需要調整的最大錯誤連接數(shù) 。

[kingly@rhel7]$vi /home/mw/mysqlrouter/mycluster_23306/mysqlrouter.conf[routing:mycluster_rw]max_connect_errors=10000bind_address=0.0.0.0bind_port=23306destinations=metadata-cache://mycluster/?role=PRIMARYrouting_strategy=first-availableprotocol=classic

重啟MySQL。
此種方法適合網(wǎng)絡穩(wěn)定性差、或忽略這種連接異常多的場景，避免短時連接失敗影響新連接到數(shù)據(jù)庫。
4、驗證結果
測試連接情況，通過MySQL客戶端嘗試連接數(shù)據(jù)庫，看能否正常連接數(shù)據(jù)庫。
[kingly@rhel7]$ mysql -uapp -pxxx -h127.0.0.1 -P23306
本文到此結束，希望對大家有所幫助。