MYSQL连接相关参数和状态值详解

| max_connect_errors | 999999999 |　　　　　　##允许单用户连接错误最大值，超过后在不刷新状态的情况下，禁止该用户新连接
| max_connections | 6000 |　　　　　　　　　　　##实例最大连接数限制
| max_user_connections | 0 |　　　　 　　　　　##但用户连接最大限制，默认0表示无限制，遵守总连接数小于等于max_connections
| connect_timeout  | 10     |　　　　　　　　　##用户连接超时限制，超过10秒，如果依旧无法连接到mysql，则终止连接

+-----------------------------+----------+
| 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超时
+-----------------------------+----------+

+-----------------------------------------------+----------+
| Connection_errors_max_connections             | 1906160  |　　　　##用户最大错误连接数
| Connections                                   | 87341259 |　　　　##历史用户连接累积值
| Max_used_connections                          | 10242    |　　　　##历史最大连接用户数,processlist看到的连接数
| Threads_connected                             | 298      |　　　　##当前用户连接数，当前processlist数

| Threads_running | 2     |　　　　　　　　　　　　　　　　　　　　　　　　##当前用户连接处于running状态的数目，非sleep

+------------------+----------+
| Aborted_clients  | 85050948 |　　　　　　　　　　　　　　　　　　　　　　##非法终止客户端连接的状态统计，例如通过kill终止连接
| Aborted_connects | 1905528  |　　　　　　　　　　　　　　　　　　　　　　##非法连接操作状态统计，例如用户密码错误

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 评估

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. 

mysql> set global max_connect_errors=5;

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 |
+----------------+-------+----------------+--------------------+------------------------+---------------------------+

| 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                |       |

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)

(1)手动杀死连接,kill的方式
(2)mysql客户端超时interactive_timeout，结束sleep
(3)程序超时wait_timeout,异常结束sleep
(4)The client program did not call mysql_close() before exiting.
(5)The client program ended abruptly in the middle of a data transfer.

(1)用户名密码错误
(2)登陆权限不足
(3)连接超时，connect_timeout
(4) max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld

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)分析

(1)长连接，processlist不断递增
(2)连接池初始化设置过大，程序不关闭连接，只增加连接