阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL连接参数和状态值详解

185次阅读
没有评论

共计 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;
[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 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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计6892字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中