服务器数据库连接失败排查

# 客户端侧：TCP 端口是否可达
nc -zv -w 3 10.0.0.20 3306
telnet 10.0.0.20 3306

# 服务端侧：mysqld 是否在跑、监听在哪个地址
sudo systemctl status mysql
sudo ss -lntup 'sport = :3306'
# 关键看 Local Address：0.0.0.0:3306(全部) / 127.0.0.1:3306(仅本地) / :::3306

# 查看 MySQL 实际监听配置
mysql -uroot -p -e "SHOW VARIABLES LIKE 'bind_address'; SHOW VARIABLES LIKE 'skip_networking'; SHOW VARIABLES LIKE 'port';"

# 本地 socket 路径
mysql -uroot -p -e "SHOW VARIABLES LIKE 'socket';"
mysql --protocol=SOCKET -S /run/mysqld/mysqld.sock -uroot -p

# /etc/mysql/mysql.conf.d/mysqld.cnf  （RHEL 系为 /etc/my.cnf.d/mysql-server.cnf）
[mysqld]
bind-address = 0.0.0.0        # 允许远程；仅本地应用则保持 127.0.0.1 更安全
port         = 3306
# skip-networking 必须为注释状态，否则只能走 socket

sudo systemctl restart mysql
sudo ss -lntup 'sport = :3306'

-- 查看账号的 host 与认证插件
SELECT user, host, plugin, account_locked FROM mysql.user WHERE user='appuser';

-- 测试用：创建允许指定网段连接的账号
CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'Str0ng-Passw0rd';
GRANT SELECT,INSERT,UPDATE,DELETE ON shopdb.* TO 'appuser'@'10.0.0.%';
FLUSH PRIVILEGES;

-- 兼容老客户端：改用 mysql_native_password
ALTER USER 'appuser'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'Str0ng-Passw0rd';
FLUSH PRIVILEGES;

# 在应用服务器上用命令行复现，最能说明问题
mysql -h 10.0.0.20 -P 3306 -u appuser -p -e "SELECT 1"

# 检查账号是否被锁定 / 密码是否过期
mysql -uroot -p -e "SELECT user,host,account_locked,password_expired FROM mysql.user;"

-- 1130：补授权，host 用网段通配
CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'Str0ng-Passw0rd';
GRANT ALL PRIVILEGES ON shopdb.* TO 'appuser'@'10.0.0.%';
FLUSH PRIVILEGES;

-- 1129：解除拉黑
FLUSH HOSTS;
-- 或调大阈值
SET GLOBAL max_connect_errors = 10000;

# 永久生效
[mysqld]
max_connect_errors = 10000

-- 当前连接数与上限
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 谁在占连接、睡了多久（Sleep 很久的说明应用没释放）
SELECT id, user, host, db, command, time, state, LEFT(info,60) AS q
FROM information_schema.PROCESSLIST
ORDER BY time DESC LIMIT 20;

-- 紧急临时扩容（重启失效）
SET GLOBAL max_connections = 1000;

# 永久配置
[mysqld]
max_connections      = 1000
wait_timeout         = 60      # 非交互连接空闲 60 秒断开
interactive_timeout  = 120

# application.properties
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.leak-detection-threshold=60000

-- 1049：列出真实库名，注意 Linux 上库名默认区分大小写
SHOW DATABASES;
SELECT @@lower_case_table_names;   # 0=区分大小写（Linux 默认）

# 服务端强制 TLS
[mysqld]
require_secure_transport = ON

# 客户端必须显式启用 SSL
mysql -h 10.0.0.20 -u appuser -p --ssl-mode=REQUIRED -e "STATUS" | grep -i ssl

# JDBC 连接串启用 TLS
spring.datasource.url=jdbc:mysql://10.0.0.20:3306/shopdb?useSSL=true&requireSSL=true&serverTimezone=Asia/Shanghai

# 步骤 1：网络层——端口可达吗？
nc -zv -w 3  

# 步骤 2：服务层——数据库活着吗？
mysqladmin -h  -u root -p ping
systemctl status mysql

# 步骤 3：凭据层——命令行用同样的账号密码能连吗？
mysql -h  -u  -p -e "SELECT CURRENT_USER(), @@hostname"

# 步骤 4：资源层——连接数、磁盘、内存是否打满
mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
df -h /var/lib/mysql

# 步骤 5：日志层——看服务端错误日志，它通常直接写了拒绝原因
sudo tail -n 100 /var/log/mysql/error.log
sudo journalctl -u mysql -n 100 --no-pager