服务器数据库连接超时怎么办

# 1) 端口通不通（注意：这是 TCP 层，与账号密码无关）
nc -vz 10.0.0.10 3306
timeout 3 bash -c 'cat < /dev/null > /dev/tcp/10.0.0.10/3306' && echo OK || echo FAIL

# 2) 应用服务器上抓最近的连接错误量
ss -antp | grep 3306 | awk '{print $1}' | sort | uniq -c

# 3) 数据库侧看总体连接态势
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_%'; SHOW GLOBAL VARIABLES LIKE 'max_connections';"

-- 连接数全局态势
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 占用率粗算：超过 80% 应扩容连接或治理连接泄漏
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') AS connected,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') AS max_conn,
  ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected')
      / (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') * 100, 2) AS pct;

-- 按状态统计：Sleep 多 = 应用端未释放；Query 多 = SQL 慢
SELECT command, time, state, count(*) AS cnt
FROM information_schema.processlist
GROUP BY command, time, state ORDER BY cnt DESC LIMIT 20;

-- 揪出睡眠最久的空闲连接
SELECT id, user, host, db, command, time, state, LEFT(info,120) AS sql_text
FROM information_schema.processlist
WHERE command='Sleep' ORDER BY time DESC LIMIT 10;

-- 批量杀掉超过 300 秒的空闲连接（先 SELECT 确认，再拼 KILL）
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist
WHERE command='Sleep' AND time > 300 AND user NOT IN ('system user','event_scheduler');

-- 慢日志开关与阈值（MySQL 8.x）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';

-- 用 sys schema 直接看最耗时语句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 实时看当前正在跑且超过 2 秒的查询
SELECT id, user, host, db, time, state, LEFT(info,200) AS q
FROM information_schema.processlist
WHERE command <> 'Sleep' AND time > 2 ORDER BY time DESC;

-- MySQL 8.0：谁在等谁
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
FROM sys.innodb_lock_waits LIMIT 20;

-- 元数据锁（MDL）阻塞：常见于 ALTER TABLE 卡住全表业务
SELECT * FROM performance_schema.metadata_locks WHERE OWNER_THREAD_ID IS NOT NULL LIMIT 20;

-- InnoDB 事务视图
SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_rows_locked, LEFT(trx_query,120) AS q
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 20;

# 服务端确认监听地址：127.0.0.1 表示只允许本机，外部当然连不上
ss -lntp | grep -E '3306|5432'
my_print_defaults --mysqld | grep -E 'bind-address|skip-name-resolve|max_connections'

# 防火墙 / 安全组
firewall-cmd --list-all
iptables -L -n -v | grep 3306
# 云安全组需在控制台放行：来源 IP/安全组 + 协议 TCP + 端口 3306/5432

# /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
skip-name-resolve
max_connections        = 500
wait_timeout           = 600
interactive_timeout    = 600
connect_timeout        = 10
max_connect_errors     = 1000
innodb_lock_wait_timeout = 30

# 绑定监听地址：0.0.0.0 表示所有网卡；仅本机访问时应保持 127.0.0.1
[mysqld]
bind-address = 0.0.0.0

# Spring Boot + HikariCP（application.yml）
spring:
  datasource:
    hikari:
      maximum-pool-size: 30
      minimum-idle: 5
      connection-timeout: 3000        # 等待获取连接上限，超时报 SQLTransientConnectionException
      validation-timeout: 3000
      idle-timeout: 300000            # 5 分钟空闲回收，必须 < 数据库 wait_timeout
      max-lifetime: 1500000           # 25 分钟强制重建，小于设备/数据库侧超时
      keepalive-time: 120000
      leak-detection-threshold: 60000 # 连接泄漏检测，生产排障利器
      connection-test-query: SELECT 1

# Druid 连接池关键参数（druid.properties）
druid.maxActive=30
druid.minIdle=5
druid.maxWait=3000
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.validationQuery=SELECT 1
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.removeAbandoned=true
druid.removeAbandonedTimeout=180
druid.logAbandoned=true