【MySQL】使用JDBC时，无法连接远程MySQL数据库

朱雀 2022-06-14 11:39 304阅读 0赞

# 使用JDBC时，无法连接远程MySQL数据库 #

--------------------

*  使用JDBC时无法连接远程MySQL数据库
    
     *  问题
     *  解决过程
     *  总结

--------------------

## 问题 ##

今天连接MySQL的时候，程序很久没有反应，在过了几分钟后向我抛出了一堆异常：

警告: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@3d121db3 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
    
    java.sql.SQLException: Connections could not be acquired from the underlying database!
    
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:692)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
        at cn.wjinlong.spring.jdbc.JDBCTest.testDataSource(JDBCTest.java:43)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
        at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
        at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
        at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
        at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
        at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
        at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
        at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
        at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
        at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
    Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1469)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
        ... 24 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.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 sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)
        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.SQLError.createCommunicationsException(SQLError.java:989)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2192)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2225)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
        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:389)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
        at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
        at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
        at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
        at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
    Caused by: java.net.ConnectException: Connection refused: 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 com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
        ... 20 more

## 解决过程 ##

异常的大概意思就是无法从数据库获得连接，所以：  
1. 我首先去检查了数据库配置文件中的用户名、密码和url等信息，确保了自己没有手残地多打了几个空格，检查了几遍后，发现配置文件是没有问题的。  
2. 在确保我的配置信息完全没有问题的情况下，我又想到可能我的数据库的用户权限可能存在问题，于是我去检查了用户的权限：  
SQL语句为：`SHOW GRANTS FOR 'test'@'%';` test为用户名，%为host。  
![这里写图片描述][SouthEast]  
可以看到，test用户拥有对test数据库的所有权限。  
3. 这时候我就很懵逼了，想不到哪里出了问题，之前也连接过很多次，为啥会出问题呢？仔细想了想，这次和之前的唯一不同就是，这次连接的是远程的MySQL，之前连接的都是本机的MySQL，于是我就想到是不是远程的防火墙禁止外部访问呢？于是去查询了远程主机的防火墙状态：  
![这里写图片描述][SouthEast 1]  
可以看到，防火墙并没有拦截的规则。  
4. 很气，哈哈哈。这真是奇怪啊，想了几遍，还是没有头绪，突然聪明的小脑瓜灵光一闪，我打算使用Navicat（图形化连接数据库工具）连接一下远程的数据库，看看到底是哪里的问题。  
![这里写图片描述][SouthEast 2]

> 2003 - Can’t connect to MySQL server on ‘xx.xx.xx.xx’ (10061 “Unknown error”)

成功的报了错，我很开心，毕竟证明程序没有问题，而且有了错误编号，于是就去搜索相关问题。有人说这个错误可能是MySQL服务没有启动，吓得我瑟瑟发抖，这本应该是我首先想到的问题，赶紧去检查了下MySQL 的状态：  
![这里写图片描述][SouthEast 3]  
虚惊一场，我就说么，怎么会犯这种错误。  
5. 没办法了，是时候祭出我的大法器啦——[官方文档][Link 1]  
从官网找到文档，又找到了连接错误出现的地方。点击连接可以直达。  
![这里写图片描述][SouthEast 4]  
从文档中发现一段话，和我的问题高度吻合。  
于是我去查看了下MySQL的配置文件：  
![这里写图片描述][SouthEast 5]  
注意红框里的bind-address=127.0.0.1，这是MySQL的msyql默认设置。这时候我，们看一下MySQL的端口情况:  
![这里写图片描述][SouthEast 6]  
好像发现了些问题，注意我红框里的127.0.0.1，这里就是问题的原因。这个稍后再解释，我们先把bind-addrss=12.70.0.1注释掉，重启MySQL，再次连接，连接成功！！

这时候我们看一下MySQL的端口情况：  
![这里写图片描述][SouthEast 7]

此时的Local Address已经从127.0.0.1–>0.0.0.0这就表示，从外部访问数据库时可以通过和本机的任何ip进行通信，而不是本地回环ip127.0.0.1。

## 总结 ##

关于bind-address为127.0.0.1是MySQL的默认设置，这样的设置会阻止远程的数据库连接。所以当你要从远程访问MySQL的时候，再MySQL的配置文件中可以把这一行注释掉，或者把127.0.0.1换成你客户端的ip。

[SouthEast]: /images/20220614/04fb8f6ab55c4fbb96375c32b89a99b8.png
[SouthEast 1]: /images/20220614/d37af1394717494c85d7a0e46219e67b.png
[SouthEast 2]: /images/20220614/927be5863776437d846d4124cc279f10.png
[SouthEast 3]: /images/20220614/b73eaeb6b3da4fb2ac574b6f377f501f.png
[Link 1]: https://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html
[SouthEast 4]: /images/20220614/bdf2af0e8691471ca99841593b8aadf5.png
[SouthEast 5]: /images/20220614/65a20eaa391246e9a1c7c353a1e396d5.png
[SouthEast 6]: /images/20220614/ed3858731d564edf96c7ee2fb43cde5a.png
[SouthEast 7]: /images/20220614/a852c37509664cdda9ea4e75b81ae2f9.png