mac 下jdbc连接mysql 8.0 失败

新手上路,请多包涵

问题描述

mac 下无法使用jdbc连接mysql8.0

Mysql环境

debian9 虚拟机

mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL) #已经允许远程连接

尝试方法

Mac下使用IDEA,Datagrip均无法连接到虚拟机。Mac与虚拟机在同一网段,192.168.0.x,未使用代理连接。
但是可以使用tableplus,mysqlworkbench 等工具连接。

预测为jetbrains产品有问题。
测试在另外一台windows系统下,使用 datagrip连接mysql,成功连接。
预测mac环境有问题。
编写jdbc代码测试

依赖

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

代码

public static void main(String[] args) {
    Connection conn = null;
    try {
        String userName = "root";
        String password = "root";

        String url = "jdbc:mysql://192.168.0.115:3306/mysql";
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(url, userName, password);
        System.out.println("connection");
    } catch (Exception e) {
        System.out.println("error");
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

报错

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

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.mysql.driver.MysqlDriver.main(MysqlDriver.java:23)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    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.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:562)
    at com.mysql.cj.protocol.a.NativeProtocol.readServerCapabilities(NativeProtocol.java:514)
    at com.mysql.cj.protocol.a.NativeProtocol.beforeHandshake(NativeProtocol.java:404)
    at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1450)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:165)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
    ... 6 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:556)
    ... 12 more

通用代码在windows下测试是成功连接的。感觉问题不在mysql服务器上,应该是mac下jdbc连接mysql8.0 有问题,网上搜了很多,没有解决。
到这里就没有头绪了。
mac java环境

java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
阅读 9.4k
3 个回答
✓ 已被采纳新手上路,请多包涵

windows装 debian虚拟机测试了下,mac可以连接。
排查网络问题,接过mac后台启动了ssr,坑爹了。打开ssr设置了下就好了。

1、MySQL 8.0 有新的安全认证机制,具体可查看官方文档

2、目前很多数据库可视化工具尚不支持MySQL 8.0 的新安全认证机制

3、最简单的解决办法是在安装dmg包的时候有选项,可降级安全认证方式,可选择连接到数据库的认证方式

这个问题折磨我一个多月,现在终于链接上了,原来Mac必须允许用户远程访问数据库才能链接mysql,
https://www.cnblogs.com/tingguoguoyo/p/11005584.html看了这个帖子解决的
1、打开终端,进入MySQL之后

2、输入以下语句,进入mysql库:

use mysql;

3、更新域属性,'%'表示允许外部访问:

update user set host='%' where user ='root';

4、执行以上语句之后再执行(刷新配置):

FLUSH PRIVILEGES;

5、再执行授权语句:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题