java - 无法从java连接到mysql服务器

Question

我在这行代码处收到错误 “Communications link failure”:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:3306/db-name", "mu-user-name", "my-password");

我检查了 this post 中的所有内容:

• 我在 etc/mysql 的 my.cnf 中增加了 max-allowed-packet: max_allowed_pa​​cket = 5073741824------ [mysqldump] max_allowed_pa​​cket = 1G
• 绑定(bind)地址是:127.0.0.1
• 所有超时值都等于一个数字
• 服务器(新服务器)上尚未安装 Tomcat
• my.cnf中没有skip-networking
• 我可以 ping 服务器
• 我通过 ssh 连接到 mysql 数据库

当我将查询字符串更改为:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:22/127.0.0.1:3306/db-name", "mu-user-name", "my-password");

我收到错误 Packet for query is too large (4739923 > 1048576)。您可以通过设置 max_allowed_pa​​cket' 变量在服务器上更改此值。

虽然我已经更改了 my.cnf 上的数据包大小并在之后重新启动了 mysql 服务。

有什么建议吗？

注意:

我可以用这段代码通过ssh连接，但这种方式似乎不合理!我可以在 main 中连接一次，然后我应该将连接传递给所有类。

public my-class-constructor() {

        try {
            go();
        } catch (Exception e) {
            e.printStackTrace();
        }

        mySqlCon = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://" + rhost + ":" + lport + "/";
        String db = "my-db-name";
        String dbUser = "dbuser";
        String dbPasswd = "pass";
        try {
            Class.forName(driver);
            mySqlCon = DriverManager.getConnection(url + db, dbUser, dbPasswd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void go() {
        String user = "ssh-user";
        String password = "ssh-pass";
        String host = "ips-address";
        int port = 22;
        try {
            JSch jsch = new JSch();
            Session session = jsch.getSession(user, host, port);
            lport = 4321;
            rhost = "localhost";
            rport = 3306;
            session.setPassword(password);
            session.setConfig("StrictHostKeyChecking", "no");
            System.out.println("Establishing Connection...");
            session.connect();
            int assinged_port = session.setPortForwardingL(lport, rhost, rport);
            System.out.println("localhost:" + assinged_port + " -> " + rhost
                    + ":" + rport);
        } catch (Exception e) {
            System.err.print(e);
            e.printStackTrace();
        }
    }

Answer 1

看看here .看起来下面描述了你的情况

The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

因此，看起来您还需要更改客户端上的 max_allowed_pa​​cket:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:3306/db-name?max_allowed_packet= 5073741824", "mu-user-name", "my-password");