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

时间:2021-12-31 20:44:54

针对mysql的连接参数和状态值,本文做些介绍和对比

一、MYSQL连接参数变量

1、常用连接数限制参数

show variables like '%connect%';

| max_connect_errors |  |      ##允许单用户连接错误最大值,超过后在不刷新状态的情况下,禁止该用户新连接
| max_connections | |           ##实例最大连接数限制
| max_user_connections | |          ##但用户连接最大限制,默认0表示无限制,遵守总连接数小于等于max_connections
| connect_timeout | |         ##用户连接超时限制,超过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_clientsThe 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主要是因为客户端非正常关闭,次值就会不断增加,具体场景如下:

()手动杀死连接,kill的方式
()mysql客户端超时interactive_timeout,结束sleep
()程序超时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失败的次数,具体场景如下:

()用户名密码错误
()登陆权限不足
(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参数选项如下:
()tcp: ip icmp arp rarp 和 tcp、udp、icmp这些选项等都要放到第一个参数的位置,用来过滤数据报的类型
()-i eth1 : 只抓经过接口eth1的包
()-t : 不显示时间戳
()-s : 抓取数据包时默认抓取长度为68字节。加上-S 后可以抓到完整的数据包
()-c : 只抓取100个数据包
()dst port ! : 不抓取目标端口是22的数据包
()src net 192.168.1.0/ : 数据包的源网络地址为192.168.1./
()-w ./target.cap : 保存成cap文件,方便用ethereal(即wireshark)分析

4、连接数不断增加

mysql连接数有时候会不断增加,这种情况一般由以下几种原因

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

解决方案:调整sleep合理超时时间;检查程序代码,确定是否关闭数据库连接操作;检查连接池配置,调整合适的初始化连接值和最大连接值

MYSQL连接相关参数和状态值详解的更多相关文章

  1. MySQL my.cnf参数配置优化详解

    [b]PS:本配置文件针对Dell R710,双至强E5620.16G内存的硬件配置.CentOS -100-300w的站点,主要使用InnoDB存储引擎.其他应用环境请根据实际情况来设置优化.[/b ...

  2. MySQL复制相关参数详解

    MySQL复制相关参数详解 作者:尹正杰  版权声明:原创作品,谢绝转载!否则将追究法律责任. 一.复制相关系统变量 1>.server_id 是必须设置在master和每个slave上的唯一标 ...

  3. 【转】MySQL用户管理及SQL语句详解

    [转]MySQL用户管理及SQL语句详解 1.1 MySQL用户管理 1.1.1 用户的定义 用户名+主机域 mysql> select user,host,password from mysq ...

  4. (转)总结之:CentOS 6.5 MySQL数据库的基础以及深入详解

    总结之:CentOS 6.5 MySQL数据库的基础以及深入详解 原文:http://tanxw.blog.51cto.com/4309543/1395539 前言 早期MySQL AB公司在2009 ...

  5. (转)MySQL性能调优my.cnf详解

    MySQL性能调优my.cnf详解 https://blog.linuxeye.cn/379.html http://blog.csdn.net/orichisonic/article/details ...

  6. MYSQL之数据库初识、安装详解、sql语句基本操作

    目录 MYSQL之数据库初识及安装详解 1.什么是数据库? 1.什么是数据?(data) 2.什么是数据库?(databases,简称DB) 2.为什要用数据库? 3.什么是数据库管理系统?(Data ...

  7. linux dmesg命令参数及用法详解(linux显示开机信息命令)

    linux dmesg命令参数及用法详解(linux显示开机信息命令) http://blog.csdn.net/zhongyhc/article/details/8909905 功能说明:显示开机信 ...

  8. MySql绿色版配置及使用详解

    原文:MySql绿色版配置及使用详解 最近在做项目开发时用到了MySql数据库,在看了一些有关MySql的文章后,很快就上手使用了.在使用的过程中还是出现了一些问题,因为使用的是绿色免安装版的MySq ...

  9. MySQL数据库使用mysqldump导出数据详解

    mysqldump是mysql用于转存储数据库的实用程序.它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等.接下来通过本文给大家介绍MySQL数 ...

随机推荐

  1. 2014_acmicpc_shanghai_google

    I http://acm.hust.edu.cn/vjudge/contest/view.action?cid=84975#problem/I 题意:我方有n个士兵,敌方有m个士兵,每个士兵有攻击力和 ...

  2. iOS刷新第三方MJRefresh的基本使用

    iOS开发中最好用的刷新第三方框架 MJRefresh GitHub : https://github.com/CoderMJLee/MJRefresh UIRefreshControl的介绍 1,U ...

  3. String 内在分配解析

    1.String类概念 (1)String是final的,不可被继承.public final class String.String是的本质是字符数组char[], 并且其值不可改变.private ...

  4. 开源 java CMS - FreeCMS2.1公布

    项目地址:http://www.freeteam.cn/ FreeCMS商业版V2.1更新功能 1.web页面信息採集:通过简单配置就可以抓取目标网页信息,支持增量式採集.keyword替换.定时採集 ...

  5. 虚拟DOM详解

    虚拟DOM简介 Virtual Dom可以看做一棵模拟了DOM树的JavaScript对象树,其主要是通过vnode,实现一个无状态的组件,当组件状态发生更新时,然后触发Virtual Dom数据的变 ...

  6. Run Repository Creation Utility (RCU) for Oracle Identity Management components

    Run Repository Creation Utility (RCU) for Oracle Identity Management components         Installing O ...

  7. java中的引用类型 部分讲解

    所谓的引用类型 类--接口--数组--枚举 [01--Scanner类] Scanner 这个类是用于键盘输入 它的格式为 类型  对象名称 =  new  类型(): 它的操作格式  对象名.nex ...

  8. centos7 mysql5.7.17源码安装

    **安装前准备 操作系统环境:Centos 7.2 1.解决依赖包并下载源码包至/home/soft/目录下 1 2 3 4 5 6 7 [root@node03 ~]# yum -y install ...

  9. python----测试04.18

    # py4测试题 # 1.8 << 2 # 等于? 8转化成二进制:1000 向左移动2位: 0010 0000 转化成十进制:32 # 2.通过内置函数计算5除以2的余数 print(d ...

  10. Bootstrap学习 进度条

    本文将介绍Bootstrap进度条,在本文中你将看到如何使用Bootstrap创建加载,重定向或动作状态的进度条 bootstrap进度条使用CSS3过渡和动画来获得该效果.Internet Expl ...