3-3、数据库连接失败4个核心排查点

Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException:
Communications link failure

The last packet sent successfully to the server was 0 ms ago.

HikariPool-1 - Failed to obtain connection from database
Caused by: CommunicationsException: Communications link failure

org.apache.ibatis.exceptions.PersistenceException:
Error querying database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException

Connection refused: connect

# 查看MySQL服务状态
net start | findstr MySQL

# 或在服务列表查看
sc query MySQL80

# 启动MySQL服务
net start MySQL80

# 或者在"服务"管理器里启动
# Win+R → services.msc → 找到MySQL → 右键启动

# 查看MySQL容器是否运行
docker ps | findstr mysql

# 如果没运行，启动它
docker start mysql-container

# 命令行连接
mysql -u root -p

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/my_project?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&allowPublicKeyRetrieval=true
    username: root
    password: Root@123456
    driver-class-name: com.mysql.cj.jdbc.Driver

-- 检查数据库是否存在
SHOW DATABASES;

Loading class `com.mysql.jdbc.Driver'. This is deprecated.

driver-class-name: com.mysql.cj.jdbc.Driver

useSSL=false                        # 不用SSL
serverTimezone=Asia/Shanghai        # 时区
characterEncoding=utf-8             # 编码
allowPublicKeyRetrieval=true        # 允许公钥检索

# 检查3306端口
netstat -ano | findstr 3306

[mysqld]
# 如果这行存在且是127.0.0.1，只能本地连
bind-address = 127.0.0.1

# 如果要远程连接，改成
bind-address = 0.0.0.0

# 检查3306是否被防火墙阻止
netsh advfirewall firewall show rule name=all | findstr 3306

# 添加放行规则
netsh advfirewall firewall add rule name="MySQL 3306" dir=in action=allow protocol=TCP localport=3306

-- 查看用户允许的host
SELECT user, host FROM mysql.user WHERE user = 'root';

-- 如果只有localhost，创建远程用户
CREATE USER 'root'@'%' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Public Key Retrieval is not allowed

Client does not support authentication protocol requested by server

url: jdbc:mysql://127.0.0.1:3306/mydb?allowPublicKeyRetrieval=true&useSSL=false

-- 登录MySQL
mysql -u root -p

-- 改成旧版认证方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;

[mysqld]
default_authentication_plugin=mysql_native_password

Caused by: java.net.SocketTimeoutException: connect timed out

spring:
  datasource:
    hikari:
      connection-timeout: 60000  # 60秒

Caused by: java.sql.SQLTransientConnectionException:
HikariPool-1 - Connection is not available, request timed out after 30000ms

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

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

-- 查看活跃连接
SHOW PROCESSLIST;

SET GLOBAL max_connections = 200;

spring:
  datasource:
    hikari:
      maximum-pool-size: 20

Caused by: javax.net.ssl.SSLException: Unsupported or unrecognized SSL message

url: jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false

Caused by: java.sql.SQLException:
The server time zone value 'xxx' is unrecognized

url: jdbc:mysql://127.0.0.1:3306/mydb?serverTimezone=Asia/Shanghai

SET GLOBAL time_zone = '+8:00';
SET SESSION time_zone = '+8:00';

Caused by: java.sql.SQLSyntaxErrorException:
Unknown database 'my_project'

CREATE DATABASE my_project DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Caused by: java.sql.SQLException:
Access denied for user 'root'@'localhost' to database 'my_project'

GRANT ALL PRIVILEGES ON my_project.* TO 'root'@'%';
FLUSH PRIVILEGES;

连接失败
  │
  ├─ MySQL服务启动了吗？ ── No → 启动服务
  │                Yes ↓
  ├─ 命令行能连上吗？ ── No → 服务有问题，看MySQL日志
  │              Yes ↓
  ├─ 配置文件信息对吗？ ── No → 改配置
  │                 Yes ↓
  ├─ URL参数对吗？ ── No → 加参数(useSSL/timezone/allowPublicKeyRetrieval)
  │            Yes ↓
  ├─ 认证方式兼容吗？ ── No → 改mysql_native_password
  │               Yes ↓
  ├─ 端口被占用了吗？ ── Yes → 换端口或杀进程
  │               No ↓
  ├─ 防火墙阻止了吗？ ── Yes → 添加放行规则
  │               No ↓
  └─ 还是连不上 → 看MySQL错误日志

# 查看日志位置
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error'"

# 查看日志文件
# Windows默认位置
# C:\ProgramData\MySQL\MySQL Server 8.0\Data\*.err

# 查看错误日志
mysql -u root -p -e "SHOW ERRORS;"