mysql开启远程连接

Bertha 。 2022-05-08 09:26 313阅读 0赞

### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60007, active 0
    ### The error may exist in file [D:\project\pedicure-server-manage\target\baking_backStage\WEB-INF\classes\net\$51zhiyuan\dev\yunzuliao_backStage\mapper\xml\ShopMapper.xml]
    ### The error may involve net.$51zhiyuan.dev.yunzuliao_backStage.mapper.ShopMapper.selectShopId
    ### The error occurred while executing a query
    ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60007, active 0] with root cause
     java.net.ConnectException: Connection timed out: connect
    	at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    	at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
    	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    	at java.net.Socket.connect(Socket.java:589)
    	at java.net.Socket.connect(Socket.java:538)
    	at java.net.Socket.<init>(Socket.java:434)
    	at java.net.Socket.<init>(Socket.java:244)
    	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:258)
    	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:305)
    	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2490)
    	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2527)
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2309)
    	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
    	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    	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:408)
    	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:419)
    	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1375)
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1431)
    	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:1844)

在本地的idea链接外网的数据库的时候，出现了如上的错误，究其原因，就是外网的数据库，没有开远程连接，下面就介绍如何开启mysql的远程连接  
1.打开cmd，启动mysql  
mysql -u root -p

2.查看user表  
mysql> use mysql;  
Database changed  
mysql> select host,user,authentication\_string from user;  
这里有两个点要注意  
（1）在进行任何对user表的操作之前，都要先写 use mysql，否则就会报错  
![在这里插入图片描述][70]  
（2）select语句最后的“；”很重要不能缺少，否则语句将不会执行  
（3）MySQL自5.7以后，移除了password字段，改为 authentication\_string，也就是说，如果你的mysql是5.7之前的版本那就  
mysql> select host,user,password from user;  
如果是5.7之后的版本，那就按照上边的写法，否则数据库不认得“password”这个字段  
![在这里插入图片描述][70 1]

3.通过授权法实现远程授权链接  
mysql> use mysql;  
Database changed

mysql> grant all privileges on *.* to root@’%’ identified by “password”;  
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user;  
![在这里插入图片描述][70 2]  
到此就完美解决啦

[70]: /images/20220508/cca938f932df4b059b9852bcbde1c2d2.png
[70 1]: /images/20220508/b379f1fea4ad4c629f7f5239e4179a42.png
[70 2]: /images/20220508/04eadc7f67f84fc485ed89ae77303da1.png