MySQLNonTransientConnectionException求助：Java无法连接本地MySQL

Hey there! I’ve been in your shoes before—struggling to get Java connected to MySQL 8.0 when Workbench and cmd work perfectly. Let’s break down the issue and fix it step by step.

The Core Problem #

MySQL 8.0 switched its default authentication plugin to caching_sha2_password, but your JDBC driver version (5.1.31) doesn’t support this newer, more secure method. That’s why your local tools work (they handle the new auth) but Java throws a connection error. Here are two reliable solutions:

1. Upgrade Your JDBC Driver (Recommended) #

Updating to a modern driver is the best long-term fix, as it keeps you aligned with MySQL’s security standards and avoids future compatibility issues.

• Use MySQL Connector/J 8.0.x: This series is fully compatible with Java 8 and MySQL 8.0. Grab the latest stable release (like 8.0.33) from Maven or the MySQL official site.
• Update your code:
  • Replace the old driver class com.mysql.jdbc.FabricMySQLDriver with com.mysql.cj.jdbc.Driver (the official driver class for MySQL 8.0+).
  • Add critical parameters to your JDBC URL—especially the timezone (MySQL 8.0 requires this to avoid timezone mismatches).
  • You can skip manually registering the driver too; Java 6+ automatically loads drivers via the META-INF/services/java.sql.Driver file in the JDBC JAR.

Here’s a corrected code example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnector {
    public static void main(String[] args) {
        // Update with your database name, credentials, and preferred timezone
        String jdbcUrl = "jdbc:mysql://localhost:3306/your_database?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true";
        String dbUser = "your_username";
        String dbPassword = "your_password";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword)) {
            System.out.println("Connection successful! 🎉");
        } catch (SQLException e) {
            System.err.println("Connection failed:");
            e.printStackTrace();
        }
    }
}

• serverTimezone=UTC: Fixes common timezone-related errors with MySQL 8.0.
• useSSL=false: Safe for local development (enable this in production if needed).
• allowPublicKeyRetrieval=true: Resolves connection issues with the caching_sha2_password plugin for local setups.

If you’re using Maven, add this dependency to your pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

2. Fall Back to Legacy Authentication (Temporary Fix) #

If you can’t upgrade the driver right now, you can switch your MySQL user’s authentication method to the older mysql_native_password that your 5.1.31 driver supports:

1. Log into MySQL via Workbench or command prompt as a root user.
2. Run these commands (replace your_user and your_password with your actual credentials):

ALTER USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

3. Restart your MySQL server to apply the changes.

After this, your existing Java code should connect without modifying the driver or URL (though adding the timezone parameter is still a good practice).

Quick Sanity Checks #

• Double-check your JDBC URL: Ensure the hostname (localhost), port (default 3306), database name, username, and password are all correct.
• Verify MySQL is running: Confirm the MySQL server is active in the Windows Services panel.
• Check firewall settings: Make sure your local firewall isn’t blocking connections to port 3306.

内容的提问来源于stack exchange，提问作者Alexey Bugerya