java - 使用公网IP远程访问MySQL数据库出错

Question

我有一个 MySQL 数据库。我的 friend 正在尝试使用我的公共(public) IP 从另一台计算机访问它。

这是他用来测试连接的程序:

public class Main {
    public static void main(String args[]) {

        String url = "jdbc:mysql://IPadresss:3307/javabase";
        String username = "bob";
        String password = "bob";
        Properties properties = new Properties();
        properties.setProperty("bob", "bob");
        properties.setProperty("bob", "bob");
        properties.setProperty("useSSL", "false");
        properties.setProperty("autoReconnect", "true");

        System.out.println("Connecting database...");

        try (Connection connection = DriverManager.getConnection(url, properties)) {
            System.out.println("Database connected!");
        } catch (SQLException e) {
            throw new IllegalStateException("Cannot connect the database!", e);
        }
    }
}

这是我的客户 friend 得到的错误:

<小时/>

Connecting database...
Exception in thread "main" java.lang.IllegalStateException: Cannot connect the database!
 at SQLConnector.Main.main(Main.java:23)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
 at com.mysql.jdbc.Util.getInstance(Util.java:408)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
 at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2163)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2088)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:208)
 at SQLConnector.Main.main(Main.java:20)
Caused by: java.sql.SQLException: Access denied for user ''@'Freinds ip' (using password: NO)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
 at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
 at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2104)
 ... 13 more

请帮我找出我连接服务器的方法有什么问题，我已远程向我的 friend (用户“bob”)授予权限。

Answer 1

因此，您遇到以下错误:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure java.net.ConnectException: Connectionrefused

发生这种情况的原因有多种，我引用的是 this answer

If you get a SQLException: Connection refused or Connection timed outor a MySQL specific CommunicationsException: Communications linkfailure, then it means that the DB isn't reachable at all.

This can have one or more of the following causes:

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL. [Assuming the one you mentioned is correct]
4. DB server is down.
5. DB server doesn't accept TCP/IP connections.
6. DB server has run out of connections.
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

To solve the one or the other, follow the following advice:

1. Verify and test them with ping.
2. Refresh DNS or use IP address in JDBC URL instead.
3. Verify it based on my.cnf of MySQL DB.
4. Start the DB.
5. Verify if mysqld is started without the --skip-networking option.
6. Restart the DB and fix your code accordingly that it closes connections in finally.
7. Disable firewall and/or configure
8. firewall/proxy to allow/forward the port.