印刻数据库

外观

MySQL 连接控制 ​

连接控制是 MySQL 数据库运维的重要组成部分，它决定了客户端如何连接到数据库服务器，以及服务器如何管理这些连接。合理的连接控制配置可以有效防止连接风暴、资源耗尽和恶意攻击，保障数据库服务器的稳定运行。本文将详细介绍 MySQL 连接控制的配置、管理和最佳实践，包括不同版本的连接特性差异。

连接控制基础 ​

连接控制的重要性 ​

在生产环境中，连接管理不当可能导致严重的问题：

  • 连接风暴：大量并发连接请求可能导致服务器资源耗尽
  • 资源竞争：过多的连接会导致 CPU、内存、磁盘 I/O 等资源竞争
  • 恶意攻击：攻击者可能通过大量连接请求进行 DoS 攻击
  • 连接泄露：应用程序连接未正确关闭可能导致连接泄露
  • 性能下降：过多的连接会导致上下文切换频繁，降低系统性能

连接控制的核心要素 ​

一个完善的连接控制策略应包含以下核心要素：

  • 最大连接数限制：限制数据库服务器允许的最大并发连接数
  • 连接超时设置：设置连接的超时时间，自动关闭空闲连接
  • 连接队列管理：管理连接请求队列，防止连接风暴
  • 失败登录限制：限制失败登录尝试次数，防止暴力破解
  • 连接来源限制：限制允许连接的客户端 IP 地址或主机名
  • 连接资源控制：控制每个连接使用的资源，如内存、CPU 等

不同版本的连接特性 ​

MySQL 5.6 连接特性 ​

MySQL 5.6 的连接管理功能相对基础：

  • 最大连接数：默认值为 151，最大可设置为 100000
  • 连接超时：支持 wait_timeout 和 interactive_timeout 参数
  • 连接队列：支持 back_log 参数，控制连接请求队列大小
  • 失败登录限制：不支持内置的失败登录限制，需通过外部工具实现
  • 连接来源限制：支持通过防火墙或绑定地址限制
  • 连接资源控制：缺乏细粒度的连接资源控制

MySQL 5.7 连接特性 ​

MySQL 5.7 对连接管理进行了显著增强：

  • 最大连接数：默认值为 151，最大可设置为 100000
  • 连接超时：支持 wait_timeout 和 interactive_timeout 参数
  • 连接队列：支持 back_log 参数，控制连接请求队列大小
  • 失败登录限制：
    • 引入 connection_control 插件，可限制失败登录尝试次数
    • 支持失败登录延迟，防止暴力破解
  • 连接来源限制：支持通过防火墙或绑定地址限制
  • 连接资源控制：
    • 支持 performance_schema 监控连接资源使用
    • 支持通过 max_connections 限制总连接数

MySQL 8.0 连接特性 ​

MySQL 8.0 对连接管理进行了全面增强：

  • 最大连接数：默认值为 151，最大可设置为 100000
  • 连接超时：
    • 支持 wait_timeout 和 interactive_timeout 参数
    • 增强了连接超时的管理机制
  • 连接队列：
    • 支持 back_log 参数
    • 增强了连接队列的管理
  • 失败登录限制：
    • 增强了 connection_control 插件功能
    • 支持更灵活的失败登录限制配置
  • 连接来源限制：
    • 支持通过 firewall 插件实现细粒度的连接来源控制
    • 支持 IP 地址、主机名、用户等多种维度的限制
  • 连接资源控制：
    • 支持通过资源组（Resource Groups）控制连接资源
    • 增强了 performance_schema 的连接监控能力
    • 支持连接线程的优先级管理

连接控制配置 ​

最大连接数配置 ​

查看当前连接数 ​

sql
-- 查看当前连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 查看连接详细信息
SHOW PROCESSLIST;
-- 或
SELECT * FROM information_schema.processlist;

配置最大连接数 ​

sql
-- 临时修改最大连接数
SET GLOBAL max_connections = 1000;

-- 永久修改最大连接数（在my.cnf中配置）
[mysqld]
max_connections = 1000

最大连接数设置建议 ​

  • 生产环境建议：根据服务器资源和业务需求设置，建议为 CPU 核心数的 100-200 倍
  • 考虑因素：
    • 服务器内存大小：每个连接大约占用 2-10MB 内存
    • CPU 核心数：过多的连接会导致上下文切换频繁
    • 磁盘 I/O 能力：过多的连接会增加磁盘 I/O 压力
    • 业务并发需求：根据业务的最大并发连接需求设置

连接超时配置 ​

查看连接超时设置 ​

sql
-- 查看非交互式连接超时
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

-- 查看交互式连接超时
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

-- 查看当前连接的超时设置
SHOW SESSION VARIABLES LIKE 'wait_timeout';
SHOW SESSION VARIABLES LIKE 'interactive_timeout';

配置连接超时 ​

sql
-- 临时修改连接超时
SET GLOBAL wait_timeout = 180;
SET GLOBAL interactive_timeout = 600;

-- 永久修改连接超时（在my.cnf中配置）
[mysqld]
wait_timeout = 180
interactive_timeout = 600

连接超时设置建议 ​

  • 非交互式连接（应用程序连接）：30-300 秒
  • 交互式连接（DBA 连接）：600-1800 秒
  • 长连接应用：根据应用特性设置，建议不超过 8 小时
  • 短连接应用：设置较短的超时时间，如 60 秒

连接队列配置 ​

查看连接队列设置 ​

sql
-- 查看连接队列大小
SHOW GLOBAL VARIABLES LIKE 'back_log';

-- 查看连接队列使用情况
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

配置连接队列 ​

sql
-- 临时修改连接队列大小
SET GLOBAL back_log = 500;

-- 永久修改连接队列大小（在my.cnf中配置）
[mysqld]
back_log = 500

连接队列设置建议 ​

  • 生产环境建议：根据服务器的处理能力设置，建议为 100-500
  • 考虑因素：
    • 服务器的网络带宽
    • 服务器的 CPU 处理能力
    • 预期的连接峰值

失败登录限制配置（MySQL 5.7+） ​

安装和启用 connection_control 插件 ​

sql
-- MySQL 5.7 安装插件
INSTALL PLUGIN connection_control SONAME 'connection_control.so';
INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'connection_control.so';

-- MySQL 8.0 安装插件
INSTALL PLUGIN connection_control SONAME 'connection_control.so';
INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'connection_control.so';

配置失败登录限制 ​

sql
-- 查看插件状态
SHOW PLUGINS LIKE 'connection_control%';

-- 查看当前配置
SHOW GLOBAL VARIABLES LIKE 'connection_control%';

-- 临时修改配置
SET GLOBAL connection_control_max_connection_delay = 30000; -- 最大延迟30秒
SET GLOBAL connection_control_min_connection_delay = 1000; -- 最小延迟1秒
SET GLOBAL connection_control_failed_connections_threshold = 3; -- 3次失败尝试后开始延迟

-- 永久修改配置（在my.cnf中配置）
[mysqld]
connection_control_max_connection_delay = 30000
connection_control_min_connection_delay = 1000
connection_control_failed_connections_threshold = 3

连接来源限制配置 ​

通过绑定地址限制 ​

sql
-- 配置 MySQL 仅监听特定 IP 地址
[mysqld]
bind-address = 127.0.0.1 -- 仅监听本地地址
-- 或
bind-address = 0.0.0.0 -- 监听所有地址
-- 或
bind-address = 192.168.1.100 -- 仅监听特定 IP 地址

通过防火墙限制 ​

bash
# Linux 防火墙配置（iptables）
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

# Linux 防火墙配置（firewalld）
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept'
firewall-cmd --reload

# Windows 防火墙配置
# 通过 Windows 防火墙高级设置配置入站规则

MySQL 8.0 Firewall 插件 ​

sql
-- 安装 Firewall 插件
INSTALL PLUGIN mysql_firewall SONAME 'mysql_firewall.so';

-- 创建防火墙规则
CALL mysql.sp_set_firewall_group_mode('PROTECTING');

-- 添加允许的客户端 IP
CALL mysql.sp_firewall_group_add_ip('PROTECTING', '192.168.1.%');

连接管理最佳实践 ​

生产环境连接控制建议 ​

连接池使用 ​

  • 应用层连接池：建议应用程序使用连接池管理数据库连接
  • 连接池配置：
    • 初始连接数：根据应用负载设置，建议为 5-20
    • 最大连接数：根据数据库最大连接数设置，建议不超过数据库最大连接数的 80%
    • 连接超时：建议与数据库 wait_timeout 保持一致
    • 验证连接：启用连接有效性验证

连接监控与告警 ​

  • 监控指标：

    • 当前连接数（Threads_connected）
    • 最大连接数（max_connections）
    • 连接使用率（Threads_connected / max_connections）
    • 失败连接尝试次数（Aborted_connects）
    • 连接队列长度（Connection_errors_max_connections）

  • 告警设置：

    • 连接使用率超过 80% 时告警
    • 失败连接尝试次数激增时告警
    • 连接数突然下降时告警

连接风暴处理 ​

  • 配置连接队列：设置合理的 back_log 参数
  • 启用连接限制：使用 connection_control 插件限制失败登录
  • 使用防火墙：配置防火墙规则限制连接速率
  • 启用限流机制：在应用层或负载均衡层实现限流

连接泄露检测与处理 ​

sql
-- 检测长时间运行的连接
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE time > 3600 -- 运行时间超过1小时
ORDER BY time DESC;

-- 检测空闲连接
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE command = 'Sleep' AND time > 3600 -- 空闲时间超过1小时
ORDER BY time DESC;

-- 终止异常连接
KILL <connection_id>;

连接优化建议 ​

使用长连接 ​

  • 长连接可以减少连接建立和断开的开销
  • 适合持续运行的应用程序
  • 注意设置合理的连接超时时间，防止连接泄露

合理设置连接参数 ​

sql
-- 优化连接参数
[mysqld]
max_connections = 1000
wait_timeout = 180
interactive_timeout = 600
back_log = 500
skip_name_resolve = 1 -- 禁用 DNS 解析，提高连接速度

禁用 DNS 解析 ​

sql
-- 禁用 DNS 解析，提高连接速度
[mysqld]
skip_name_resolve = 1

使用 Unix Socket 连接（本地连接） ​

  • 本地连接建议使用 Unix Socket，比 TCP 连接更快
  • 配置 socket 文件路径
sql
[mysqld]
socket = /var/lib/mysql/mysql.sock

连接相关问题排查 ​

连接数已满 ​

错误信息：

ERROR 1040 (HY000): Too many connections

排查步骤：

  • 查看当前连接数：SHOW GLOBAL STATUS LIKE 'Threads_connected';
  • 查看连接详情：SHOW PROCESSLIST;
  • 分析连接来源：识别连接来源和用途
  • 处理异常连接：终止长时间运行或空闲的连接
  • 调整最大连接数：根据实际需求调整 max_connections 参数
  • 优化连接池配置：调整应用程序连接池配置

连接超时 ​

错误信息：

ERROR 2013 (HY000): Lost connection to MySQL server during query
或
ERROR 2006 (HY000): MySQL server has gone away

排查步骤：

  • 检查连接超时设置：SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
  • 检查网络状况：检查网络连接是否稳定
  • 检查服务器负载：检查服务器 CPU、内存、磁盘 I/O 负载
  • 优化查询：优化长时间运行的查询
  • 调整连接超时：根据实际需求调整 wait_timeout 参数

失败登录尝试过多 ​

错误信息：

ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES)

排查步骤：

  • 检查失败登录尝试次数：SHOW GLOBAL STATUS LIKE 'Aborted_connects';
  • 检查 connection_control 配置：如果启用了 connection_control 插件，检查相关配置
  • 分析日志：查看 MySQL 错误日志，识别失败登录来源
  • 配置失败登录限制：启用 connection_control 插件，限制失败登录尝试
  • 检查密码策略：确保密码策略合理，防止弱密码

连接建立缓慢 ​

排查步骤：

  • 检查 DNS 解析：如果未禁用 DNS 解析，检查 DNS 解析是否正常
  • 检查服务器负载：检查服务器 CPU、内存负载
  • 检查网络状况：检查网络延迟和带宽
  • 禁用 DNS 解析：设置 skip_name_resolve = 1
  • 优化连接参数：调整 back_log 等连接参数

企业级连接管理方案 ​

集中式连接管理 ​

  • 使用中间件：

    • 使用 ProxySQL、MaxScale 等数据库中间件管理连接
    • 实现连接池、读写分离、负载均衡等功能
    • 集中管理连接策略和配置

  • 连接监控平台：

    • 使用 Prometheus + Grafana 监控连接指标
    • 实现连接使用率、连接来源、连接类型等多维度监控
    • 设置连接相关的告警规则

自动化连接管理 ​

  1. 连接自动伸缩：

    • 根据负载自动调整连接池大小
    • 实现连接的动态分配和回收

  2. 异常连接自动处理：

    • 自动检测和终止异常连接
    • 自动清理空闲连接
    • 自动处理连接泄露

  3. 连接策略自动化：

    • 使用 Ansible、Puppet 等配置管理工具管理连接配置
    • 实现连接策略的版本控制和一键部署

连接安全管理 ​

  • SSL/TLS 加密：

    • 启用 SSL/TLS 加密连接
    • 配置客户端证书验证
    • 确保连接传输安全

  • 身份认证增强：

    • 启用双因素认证
    • 集成企业身份管理系统
    • 实现单点登录

总结 ​

连接控制是 MySQL 数据库运维的重要组成部分，合理的连接控制配置可以有效防止连接风暴、资源耗尽和恶意攻击，保障数据库服务器的稳定运行。在生产环境中，DBA 应根据 MySQL 版本选择合适的连接控制策略：

  1. MySQL 5.6：

    • 重点关注最大连接数、连接超时和连接队列配置
    • 建议禁用 DNS 解析，提高连接速度
    • 考虑使用外部工具实现失败登录限制

  2. MySQL 5.7：

    • 充分利用 connection_control 插件，限制失败登录尝试
    • 启用 performance_schema 监控连接资源使用
    • 配置合理的连接超时和最大连接数

  3. MySQL 8.0：

    • 充分利用 connection_control 插件和 firewall 插件
    • 实现细粒度的连接来源控制
    • 利用资源组控制连接资源
    • 增强连接监控和告警

通过合理的连接控制配置和管理，可以有效提高 MySQL 数据库的稳定性和安全性，防止连接相关的问题，保障业务系统的正常运行。DBA 应定期审查和更新连接控制策略，根据业务需求和服务器资源调整配置，确保连接管理的有效性和合理性。

参考资料 ​