高级DBA带你处理MySQL客户端程序频繁访问MYSQL数据库并错误链接不释放导致连接数爆满事故实战

时间:2024-03-31 21:38:34

高级DBA带你处理MySQL客户端程序频繁访问MYSQL数据库并错误链接不释放导致连接数爆满事故实战

一、生产事故描述

Mysql生产数据库最大连接数爆满,其余客户端也同样拿不到数据库连接,生产异常,数据传输失败!
报错如下:

在这里插入图片描述

已经排查出某一个数据库用户错误连接非常多,并且不释放!

用这个用户登录程序错误!
在这里插入图片描述

在这里插入图片描述
用户过多错误连接已经被锁定!!

二、问题解决方法

步骤一 排查出引起连接错误过多的账号,关掉起客户端的程序

关掉程序,避免再频繁攻击主数据库!

步骤二 删掉那些错误的连接,避免占着数据库资源!让其他用户拿不到连接!!

select concat('kill ',ID,';')  
from information_schema.PROCESSLIST 
where Command='Connect' and 
STATE='Waiting in connection_control plugin';  #批量删掉错误连接
select ID from information_schema.PROCESSLIST 
where Command='Connect' and 
STATE='Waiting in connection_control plugin';  # ID 为查询的线程ID

我们可以用工具批量删除这些错误的连接,释放数据库,也可以执行上面的语句批量删!
在这里插入图片描述

步骤三 这个要MYSQL客户端去解决频繁访问数据库的调度

这个是要客户端程序去解决的,就是这个数据库调度跟释放的逻辑!

步骤四 重新验证问题是否解决

ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;  #用户解锁
SHOW  VARIABLES LIKE 'max_connect_errors'; #查询系统当前的配置值

加大运行错误连接的个数!

set global max_connect_errors = 500;
重新打开之前的客户端程序再通过数据库监控,查看之前的问题是否已经解决!

三、总结

MySQL Waiting in connection_control plugin 的含义解释

MySQL中出现"Waiting in connection_control plugin"表示客户端连接请求被Connection-Control Plugins插件拦截,连接处于等待用户身份验证的状态。出现该问题的原因可能是用户名或密码错误,导致默认MySQL连接数被耗尽。可以尝试修改Connection-Control Plugins插件的配置参数来解决该问题,例如将connection_control.failed_login_threshold参数设置为一个非常大的值,从而达到类似禁用插件的效果。

事故应该是,MYSQL客户端程序配置错了用户名密码,然后程序无限制的频繁反复重连数据库!
然后找发现还会生成状态为“Waiting in connection_control plugin”的等待连接,
经查询对应的Host,原来是自己的一个很旧的服务在一直跑着,但后来mysql密码更改了,造成这个程序一下在重新尝试连接Mysql,,,杀掉它,问题解决。。。

其他可能原因,比如密码失效,密码过期了,但是程序一直起着!或者超出数据库打开文件数的值!
原因1.
数据库打开文件数超过linux默认打开文件
数量1024 ulimit -a
查看openfiles数量
原因2.
用户名或密码错误,不停连接,默认mysql连接数被耗尽.
根因:链接异常(用户名密码错误)导致connectioncontrol 插件进行拦截,拦截用户身份验证处于 “waiting in connection_control plugin” 的链接延迟状态。

解决方案:

修改connection_control 插件配置参数 - 可选

SET GLOBAL connection_control.failed_login_threshold = 1000000; #这个不建议

将 connection_control.failed_login_threshold 参数设置为一个非常大的值,这将使插件几乎不再对登录尝试进行限制,从而达到类似禁用的效果。请注意,这种方法并非官方推荐的禁用插件的方式,因此在生产环境中使用时需要谨慎考虑可能的影响
也有可能是Mysql密码自动过期了,此时微服务也会报错。
提示密码过期: SQLException: Your password has expired. To log in you must change it using a client that supports e
由于密码过期导致连接失败,所以也会频繁重连,此时ip可能会被Mysql block 。 此时数据库负载可能也会突然升高。
java.sql.SQLException: null, message from server: “Host ‘172.18.224.48’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”)

解决方案

解决密码过期问题 - 重置密码即可
或者临时取消过期机制: ALTER USER ‘repl’@‘%’ PASSWORD EXPIRE NEVER;
解决黑名单问题 - mysql> FLUSH HOSTS;

笔者简介
国内某一线知名软件公司企业认证在职员工:任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验!现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA,软件架构师。直接参与设计*亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起,挽回数据丢失所造成的灾难损失不计其数!并为某*大数据系统的技术方案(国家知识产权局颁布)专利权的第一专利发明人!

在这里插入图片描述

在这里插入图片描述