首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >运维实践|采集MySQL数据出现many connection errors

运维实践|采集MySQL数据出现many connection errors

原创
作者头像
Aion
发布2023-11-29 12:25:54
2750
发布2023-11-29 12:25:54
举报

问题出现

最近在做OGG结构化数据采集工作,在数据采集过程中,数据库总是出现连接错误,导致阻塞。并提示以下错误:

Host is blocked because of many connection errors;unblock with 'mysqladmin flush-hosts'

这里手动的解决方法是去数据库执行脚本: FLUSH HOSTS; 。执行完成刷新缓存后,我已经将数据库错误连接数调大了,但是还是没一会就出错。

问题分析

当前环境

测试机器: macOSKylin V10 SP1

MySQL版本: MySQL 8.0.31 CEMySQL 8.0.29 CE

分析

在网上搜索到一段话:

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客户端与数据库建立连接需要发起三次握手协议,正常情况下,这个时间非常短,但是一旦网络异常,网络超时等因素出现,就会导致这个握手协议无法完成,MySQL有个参数、 connect_timeout ,它是MySQL服务端进程mysqld等待连接建立完成的时间,单位为秒。如果超过connect_timeout时间范围内,仍然无法完成协议握手话,MySQL客户端会收到异常,异常消息类似于: Lost connection to MySQL server at ‘XXX’, system error: errno,该变量默认是10秒。

看到这里,在网上搜索了下并结合提示信息,也有很多解决方案,例如在服务器中创建一个调度任务,定时刷新缓存错误数据,那就开始试试。

临时解决方案

1 检查调度事件任务是否开启

执行脚本命令查看调度是否开启

mysql> SHOW VARIABLES LIKE '%event%';

2 开启调度事件任务

上面说明我这服务器已经开启了事件任务,如果没有开启,业务需担心,则执行以下命令(OFF或者0或者DISABLED),可以使用下面的命令临时处理下:

SET GLOBAL event_scheduler = 1;

上面开启事件任务是临时的,如果服务器重新启动或者MySQL服务重新启动,没有开启的事件任务会被还原,此时需要在配置文件配置启动服务时启动调度事件。在 my.cnf 中的 [mysqld] 部分添加如下内容,然后重启mysql服务

event_scheduler=ON

3 创建一张日志表

创建一张名称为 it_flush_hosts_log 的表,此步骤如果不需要可以省略,用来记录调度器执行的日志信息。

DROP TABLE IF EXISTS it_flush_hosts_log;

CREATE TABLE it_flush_hosts_log(
	id INT(10) NOT NULL AUTO_INCREMENT COMMENT '序号',
	proc_name VARCHAR(64)  NULL DEFAULT 'proce_flush_hosts' COMMENT '函数存储过程',
	event_name VARCHAR(64) NULL DEFAULT 'event_flush_hosts' COMMENT '事件调度器',
	create_by VARCHAR(64) NULL DEFAULT NULL COMMENT '日志记录人',
	create_time datetime NULL DEFAULT NULL COMMENT '日志记录时间',
	PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT '清理缓存日志表';

4 创建函数存储过程

此时需要创建一个函数,名称为 flush_hosts_proce 存储过程的方式来执行程序,也就是我们常说的执行器。

DROP PROCEDURE IF EXISTS proce_flush_hosts;

CREATE PROCEDURE proce_flush_hosts()
BEGIN

	FLUSH HOSTS;

  INSERT INTO it_flush_hosts_log VALUE (null, 'proce_flush_hosts', 'event_flush_hosts', 'root', NOW());

END

5 创建事件定时器

创建一个名称为 event_flush_hosts 事件定时器(不启动)。这个事件会从现在开始,每隔1个月执行一次,并调用一个名称为 flush_hosts_proce 的存储过程。

DROP EVENT IF EXISTS event_flush_hosts;

CREATE EVENT event_flush_hosts
	ON SCHEDULE EVERY 1 MONTH
	ON COMPLETION PRESERVE DISABLE
COMMENT '每个月1号清理主机缓存'
DO 
	CALL flush_hosts_proce();

6 开启事件调度任务

执行下面的命令,开启调度定时器。

ALTER EVENT event_flush_hosts ON COMPLETION PRESERVE ENABLE;

注意⚠️:此步骤可以同上面的步骤5合并,直接使用 ENABLE 开启也是可以的。

7 检查核实是否创建

核实创建的是否准确,可以执行下面的命令,也可以使用GUI图形界面化工具查看。

SHOW PROCEDURE STATUS WHERE NAME LIKE 'proce_flush_hosts';

SHOW EVENTS WHERE NAME LIKE 'event_flush_hosts';

总结

有问题不可怕,有问题不解决才可怕。对于中年人来说,学习新技能或知识并不晚。事实上,许多人都是在中年甚至老年时才找到自己真正热爱的事情并开始学习。我感觉学习给予我最大的快乐就是预防以后的老年痴呆症。


我正在参与2023腾讯技术创作特训营第三期有奖征文,组队打卡瓜分大奖!

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题出现
  • 问题分析
  • 临时解决方案
    • 1 检查调度事件任务是否开启
      • 2 开启调度事件任务
        • 3 创建一张日志表
          • 4 创建函数存储过程
            • 5 创建事件定时器
              • 6 开启事件调度任务
                • 7 检查核实是否创建
                • 总结
                相关产品与服务
                云数据库 MySQL
                腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
                领券
                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档