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

文章目录

• 问题出现
• 问题分析
  • 当前环境
  • 问题分析
• 解决方案
• • 1 检查调度事件任务是否开启
  • 2 开启调度事件任务
  • 3 创建一张日志表
  • 4 创建函数存储过程
  • 5 创建事件定时器
  • 6 开启事件调度任务
  • 7 检查核实是否创建
• 总结

问题出现

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

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

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

在这里插入图片描述

问题分析

当前环境

测试机器： macOS ， Kylin V10 SP1 MySQL版本： MySQL 8.0.31 CE， MySQL 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';

在这里插入图片描述

在这里插入图片描述

总结

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