針對mysql的連接參數和狀態值，本文做些介紹和對比 一、MYSQL連接參數變數 1、常用連接數限制參數 show variables like '%connect%'; 2、超時參數 mysql -e "show variables like '%timeout%'" 二、MySQL連接狀態變數 ...
針對mysql的連接參數和狀態值，本文做些介紹和對比
一、MYSQL連接參數變數
1、常用連接數限制參數
show variables like '%connect%';
| max_connect_errors | 999999999 | ##允許單用戶連接錯誤最大值，超過後在不刷新狀態的情況下，禁止該用戶新連接 | max_connections | 6000 | ##實例最大連接數限制 | max_user_connections | 0 | ##但用戶連接最大限制，預設0表示無限制，遵守總連接數小於等於max_connections | connect_timeout | 10 | ##用戶連接超時限制，超過10秒，如果依舊無法連接到mysql，則終止連接
2、超時參數
mysql -e "show variables like '%timeout%'"
+-----------------------------+----------+ | connect_timeout | 10 | ##連接超時，10秒 | delayed_insert_timeout | 300 | ##延遲插入超時時間，300秒 | have_statement_timeout | YES | ## | innodb_flush_log_at_timeout | 1 | ##刷新redo log buffer超時時間，1秒 | innodb_lock_wait_timeout | 120 | ##事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗，120秒 | innodb_rollback_on_timeout | ON | | interactive_timeout | 28800 | ##mysql客戶端交互連接超時時間，預設8小時，用於控制sleep超時 | lock_wait_timeout | 31536000 | ##主要針對DDL產生的metadata locks超時時間 | net_read_timeout | 60 | ##網路讀取數據超時時間，60秒 | net_write_timeout | 60 | ##為網路寫入數據超時間60秒 | rpl_stop_slave_timeout | 31536000 | ##停止從庫服務超時時間 | slave_net_timeout | 60 | ##slave網路超時時間 | thread_pool_idle_timeout | 60 | | wait_timeout | 28800 | ##jdbc/odbc連接超時時間，預設8小時，用於控制sleep超時 +-----------------------------+----------+
二、MySQL連接狀態變數
1、常用鏈接狀態變數
show global status like '%connect%';
+-----------------------------------------------+----------+ | Connection_errors_max_connections | 1906160 | ##用戶最大錯誤連接數 | Connections | 87341259 | ##歷史用戶連接累積值 | Max_used_connections | 10242 | ##歷史最大連接用戶數,processlist看到的連接數 | Threads_connected | 298 | ##當前用戶連接數，當前processlist數
show global status like '%Threads_running%';
| Threads_running | 2 | ##當前用戶連接處於running狀態的數目，非sleep
show global status like '%abort%';
+------------------+----------+ | Aborted_clients | 85050948 | ##非法終止客戶端連接的狀態統計，例如通過kill終止連接 | Aborted_connects | 1905528 | ##非法連接操作狀態統計，例如用戶密碼錯誤
三、常見問題分析
1、最大連接數超出限制
動態調整最大連接參數變數，然後通過連接嘗試
mysql> set global max_connections=2; [root@localhost ~]# mysql ERROR 1040 (HY000): Too many connections
##此時連接超出的嘗試，不會經過許可權驗證，因此不會記錄Aborted_connects變數值
如遇到此問題即表示用戶連接超出了最大連接限制，需要增大連接數
mysql> set global max_connections=350; ##動態調整後，需要將/etc/my.cnf配置參數中的max_connections=350也要做相應調整，這個參數可以根據狀態量值Max_used_connections 評估
2、用戶連接錯誤數超出限制
動態調整最大連接錯誤數變數限制，然後通過嘗試失敗登錄，主機對應的普通用戶會被阻塞，超級用戶不限
max_connect_errors官方解釋如下：
If more than this many successive connection requests from a host are interrupted without a successful connection,
the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache.
To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command.
If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted,
the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.
The default is 100.
手動調整參數測試（telnet 10.1.0.1 3306 五次斷鏈，用戶會被鎖）
mysql> set global max_connect_errors=5;
通過測試發現用戶直接錯誤密碼連接，不會導致用戶被鎖，主要通過performance_schema下的host_cache表查看相關阻塞信息
mysql> select IP,HOST,HOST_VALIDATED,SUM_CONNECT_ERRORS ,COUNT_HANDSHAKE_ERRORS,COUNT_HOST_BLOCKED_ERRORS from host_cache; +----------------+-------+----------------+--------------------+------------------------+---------------------------+ | IP | HOST | HOST_VALIDATED | SUM_CONNECT_ERRORS | COUNT_HANDSHAKE_ERRORS | COUNT_HOST_BLOCKED_ERRORS | +----------------+-------+----------------+--------------------+------------------------+---------------------------+ | 10.1.0.1 | NULL | NO | 0 | 5 | 0 | | 10.1.0.100 | node1 | YES | 0 | 0 | 0 | +----------------+-------+----------------+--------------------+------------------------+---------------------------+
哪些連接會導致用戶被鎖？導致必須通過flush hosts釋放host_cache，具體如下：
| COUNT_NAMEINFO_TRANSIENT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_NAMEINFO_PERMANENT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_FORMAT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_ADDRINFO_TRANSIENT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_ADDRINFO_PERMANENT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_FCRDNS_ERRORS | bigint(20) | NO | | NULL | | | COUNT_HOST_ACL_ERRORS | bigint(20) | NO | | NULL | | | COUNT_NO_AUTH_PLUGIN_ERRORS | bigint(20) | NO | | NULL | | | COUNT_AUTH_PLUGIN_ERRORS | bigint(20) | NO | | NULL | | | COUNT_HANDSHAKE_ERRORS | bigint(20) | NO | | NULL | | | COUNT_PROXY_USER_ERRORS | bigint(20) | NO | | NULL | | | COUNT_PROXY_USER_ACL_ERRORS | bigint(20) | NO | | NULL | | | COUNT_AUTHENTICATION_ERRORS | bigint(20) | NO | | NULL | | | COUNT_SSL_ERRORS | bigint(20) | NO | | NULL | | | COUNT_MAX_USER_CONNECTIONS_ERRORS | bigint(20) | NO | | NULL | | | COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS | bigint(20) | NO | | NULL | | | COUNT_DEFAULT_DATABASE_ERRORS | bigint(20) | NO | | NULL | | | COUNT_INIT_CONNECT_ERRORS | bigint(20) | NO | | NULL | | | COUNT_LOCAL_ERRORS | bigint(20) | NO | | NULL | | | COUNT_UNKNOWN_ERRORS | bigint(20) | NO | | NULL | |
解決方法就是：flush hosts或mysqladmin flush-hosts
mysql> select ip,host,HOST_VALIDATED,SUM_CONNECT_ERRORS ,COUNT_HANDSHAKE_ERRORS,COUNT_HOST_BLOCKED_ERRORS from host_cache; +----------------+-------+----------------+--------------------+------------------------+---------------------------+ | ip | host | HOST_VALIDATED | SUM_CONNECT_ERRORS | COUNT_HANDSHAKE_ERRORS | COUNT_HOST_BLOCKED_ERRORS | +----------------+-------+----------------+--------------------+------------------------+---------------------------+ | 10.1.0.1 | NULL | NO | 0 | 5 | 0 | | 10.1.0.100 | node1 | YES | 0 | 0 | 0 | +----------------+-------+----------------+--------------------+------------------------+---------------------------+ 2 rows in set (0.00 sec) mysql> flush hosts; Query OK, 0 rows affected (0.00 sec) mysql> select ip,host,HOST_VALIDATED,SUM_CONNECT_ERRORS ,COUNT_HANDSHAKE_ERRORS,COUNT_HOST_BLOCKED_ERRORS from host_cache; Empty set (0.00 sec)
3、aborted連接值非常高
abort連接狀態值主要有兩個，分別是 Aborted_clients和Aborted_connects，其中兩個變數值含義如下：
Aborted_clients：The number of connections that were aborted because the client died without closing the connection properly.
Aborted_connects：The number of failed attempts to connect to the MySQL server
下麵的狀態變數主要是用戶最大連接超過後，有多少嘗試連接的錯誤統計：
Connection_errors_max_connections：The number of connections refused because the server max_connections limit was reached
Aborted_clients和Aborted_connects參數對比，兩個狀態值在什麼情況下會變化？
Aborted_clients主要是因為客戶端非正常關閉，次值就會不斷增加，具體場景如下：
(1)手動殺死連接,kill的方式 (2)mysql客戶端超時interactive_timeout，結束sleep (3)程式超時wait_timeout,異常結束sleep
(4)The client program did not callmysql_close()before exiting.
(5)The client program ended abruptly in the middle of a data transfer.
Aborted_connects主要是連接Mysql失敗的次數，具體場景如下：
(1)用戶名密碼錯誤 (2)登陸許可權不足
(3)連接超時，connect_timeout
(4)max_allowed_packetvariable value is too small or queries require more memory than you have allocated for mysqld
針對aborted連接非常高，首先判斷是aborted_clients還是aborted_connects，然後具體情況具體分析，如果是aborted_connects可以通過tcpdump和error log日誌判斷具體連接失敗、導致abort的主機、用戶等信息。
tcpdump -s 1500 -w tcp.out port 3306
tcpdump參數選項如下： (1)tcp: ip icmp arp rarp 和 tcp、udp、icmp這些選項等都要放到第一個參數的位置，用來過濾數據報的類型 (2)-i eth1 : 只抓經過介面eth1的包 (3)-t : 不顯示時間戳 (4)-s 0 : 抓取數據包時預設抓取長度為68位元組。加上-S 0 後可以抓到完整的數據包 (5)-c 100 : 只抓取100個數據包 (6)dst port ! 22 : 不抓取目標埠是22的數據包 (7)src net 192.168.1.0/24 : 數據包的源網路地址為192.168.1.0/24 (8)-w ./target.cap : 保存成cap文件，方便用ethereal(即wireshark)分析
4、連接數不斷增加
mysql連接數有時候會不斷增加，這種情況一般由以下幾種原因
(1)長連接，processlist不斷遞增 (2)連接池初始化設置過大，程式不關閉連接，只增加連接
解決方案：調整sleep合理超時時間；檢查程式代碼，確定是否關閉資料庫連接操作；檢查連接池配置，調整合適的初始化連接值和最大連接值
