MySQL连接异常场景模拟与排查的实战指南

MySQL连接异常场景模拟与排查实战指南

一、常见连接异常场景模拟

1. 基础连接失败场景

场景1：服务未启动

# 模拟MySQL服务停止
sudo systemctl stop mysql

# 连接测试（将立即失败）
mysql -h 127.0.0.1 -P 3306 -u root -p
# 错误：ERROR 2003 (HY000): Can't connect to MySQL server

场景2：错误认证信息

-- 创建错误密码用户用于测试
CREATE USER 'test_user'@'%' IDENTIFIED BY 'wrong_password';

-- 使用错误密码连接
mysql -u test_user -p wrong_password
-- 错误：ERROR 1045 (28000): Access denied

场景3：网络/防火墙问题

# 模拟端口不通
iptables -A INPUT -p tcp --dport 3306 -j DROP

# 使用telnet测试连通性
telnet 127.0.0.1 3306
# 连接超时或拒绝

2. 资源限制场景

场景4：连接数耗尽

-- 查看当前最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 通常默认151

-- 模拟大量连接（使用脚本）
#!/bin/bash
for i in {1..200}; do
    mysql -u root -p密码 -e "SELECT SLEEP(300)" &
done

-- 新连接将收到：
-- ERROR 1040 (HY000): Too many connections

场景5：内存/线程资源不足

-- 修改配置模拟资源限制（my.cnf）
[mysqld]
max_connections = 10
thread_cache_size = 0

-- 重启后快速创建多个连接

3. 配置错误场景

场景6：绑定地址限制

-- 错误配置：只监听本地socket
[mysqld]
bind-address = 127.0.0.1
skip-networking = ON

-- 远程连接尝试
mysql -h 服务器IP -u root -p
-- 错误：连接超时

场景7：时区/字符集不匹配

-- 服务端配置特殊时区
SET GLOBAL time_zone = '+10:00';

-- 客户端使用不同时区连接
-- 可能导致时间相关查询异常

二、系统化排查流程

第1步：基础连通性检查

# 1. 检查服务状态
systemctl status mysql
ps aux | grep mysqld

# 2. 检查端口监听
netstat -tlnp | grep 3306
ss -tlnp | grep :3306

# 3. 测试网络连通性
telnet <mysql_host> 3306
nc -zv <mysql_host> 3306

第2步：MySQL服务端检查

-- 1. 登录MySQL（如能本地登录）
mysql -u root -p

-- 2. 检查用户权限
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';

-- 3. 检查连接数状态
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- 4. 检查错误日志
SHOW VARIABLES LIKE 'log_error';
# 查看文件内容：tail -f /var/log/mysql/error.log

第3步：客户端连接参数验证

# 使用详细连接参数测试
mysql --verbose \
  -h hostname \
  -P 3306 \
  -u username \
  -p \
  --connect-timeout=10 \
  --protocol=TCP

三、高级诊断技巧

1. 使用性能模式监控

-- 开启连接监控
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_waits%';

-- 查看连接等待事件
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE 'wait/io/socket%';

2. 慢连接日志分析

-- 开启慢查询日志（包含连接时间）
SET GLOBAL slow_query_log = ON;
SET GLOBAL log_slow_extra = ON;
SET GLOBAL long_query_time = 1; -- 1秒以上视为慢连接

-- 查看日志
SHOW VARIABLES LIKE 'slow_query_log_file';

3. 网络层抓包分析

# 使用tcpdump捕获MySQL连接包
tcpdump -i any port 3306 -w mysql_connect.pcap

# 使用Wireshark分析
# 过滤器：mysql || tcp.port == 3306

四、常见错误代码及解决方案

错误代码	原因	解决方案
ERROR 2003	无法连接到服务器	1. 检查服务状态 2. 检查防火墙 3. 验证网络路由
ERROR 1045	访问被拒绝	1. 检查用户名/密码 2. 验证主机权限 3. 重置密码
ERROR 1040	连接数过多	1. 增加max_connections 2. 杀死空闲连接 3. 使用连接池
ERROR 2013	查询期间连接丢失	1. 增加wait_timeout 2. 检查网络稳定性 3. 启用连接保活
ERROR 1129	主机被阻塞	1. 检查max_connect_errors 2. 执行FLUSH HOSTS

五、预防与最佳实践

1. 连接池配置示例（Java）

// HikariCP配置
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://host:3306/db");
config.setUsername("user");
config.setPassword("pass");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000); // 30秒
config.setIdleTimeout(600000); // 10分钟
config.addDataSourceProperty("socketTimeout", "30000");

2. 监控告警配置

# 监控脚本示例
#!/bin/bash
CONNECTIONS=$(mysql -u监控用户 -p密码 -e "SHOW STATUS LIKE 'Threads_connected'" -sN | awk '{print $2}')
MAX_CONN=$(mysql -u监控用户 -p密码 -e "SHOW VARIABLES LIKE 'max_connections'" -sN | awk '{print $2}')

# 连接数超过80%告警
THRESHOLD=$(echo "$MAX_CONN * 0.8" | bc | cut -d. -f1)
if [ $CONNECTIONS -gt $THRESHOLD ]; then
    echo "警告：MySQL连接数过高 ($CONNECTIONS/$MAX_CONN)" | mail -s "MySQL告警" admin@example.com
fi

3. 自动修复脚本示例

#!/bin/bash
# 自动清理空闲连接
mysql -u root -p密码 << EOF
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE COMMAND = 'Sleep' 
AND TIME > 600 INTO OUTFILE '/tmp/kill_idle.sql';

SOURCE /tmp/kill_idle.sql;
EOF