共计 6892 个字符，预计需要花费 18 分钟才能阅读完成。
针对 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; | |
| [] | |
| ERROR 1040 (HY000): Too many connections |
如遇到此问题即表示用户连接超出了最大连接限制，需要增大连接数
| 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 call mysql_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_packet variable 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 合理超时时间；检查程序代码，确定是否关闭数据库连接操作；检查连接池配置，调整合适的初始化连接值和最大连接值。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150267.htm
