linux连接数据库问题

Question

老师您好，我在购买RDS时，因为刚有个打折，但只能选深圳区(华南1)的RDS，然后就买了。但在购买ECS时，买的是广州区的(华南3)，然后我是通过RDS的外网连接数据库，登录admin控制台，在windows的idea上测试通过，没问题。但当我把程序打包到云主机linux上面运行时，在admin登录界面一登录就会报告数据库连接不上的错误，目前测试过连接RDS的数据库报下面这个错误，也测试过在云主机linux上连接自己windows的mysql数据库也连接不上，也是报下面这个同样的错误。百度看到的帖子就让改c3p0连接池配置，但也没有，还是报同样的错误。不知道老师能不能帮到我，麻烦了。
yml文件中c3p0的配置
#配置c3p0的数据池
c3p0:
#配置数据库名
jdbcUrl: jdbc:mysql://rm-wz96ah30nk3g5n7poto.mysql.rds.aliyuncs.com:3306/courseimooc?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
#配置username
user: courseimooc
#配置password
password: KEYu3dcourseimooc
#配置driver
driverClass: com.mysql.cj.jdbc.Driver
#配置最小连接池数据
minPoolSize: 2
#配置最大连接数
maxPoolSize: 30
#最大空闲时间
maxIdleTime: 1800000
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数,每次增加3个连接
acquireIncrement: 3
#最大sql语句
maxStatements: 1000
#初始化连接池数
initialPoolSize: 3
idleConnectionTestPeriod: 60
acquireRetryAttempts: 30
acquireRetryDelay: 1000
breakAfterAcquireFailure: false
testConnectionOnCheckout: false
#每次获得连接时都检查连接的有效性
testConnectionOnCheckin: true

linux上报告的错误
13:00:24 INFO c.i.c.s.c.console.LogAspect :85 ------------- 【用户】开始 -------------
13:00:24 INFO c.i.c.s.c.console.LogAspect :86 请求地址: POST http://172.17.80.233:9001/system/admin/user/login
13:00:24 INFO c.i.c.s.c.console.LogAspect :87 类名方法: com.imooc.course.system.controller.admin.UserController.login
13:00:24 INFO c.i.c.s.c.console.LogAspect :88 远程地址: 172.17.80.233
13:00:24 INFO c.i.c.s.c.console.LogAspect :110 请求参数: [{“imageCode”:“mm55”,“imageCodeToken”:“TK2Od6o5”,“loginName”:“admin”,“password”:“6e85878419a157238105acdbbb22866f”},“Request”]
13:00:24 INFO c.i.c.s.c.admin.UserController:154 发送登录请求前的 password 第一次加密 = 6e85878419a157238105acdbbb22866f
13:00:24 INFO c.i.c.s.c.admin.UserController:169 后端收到 password 第二次加密 = 413d98161771007939d6e8bc334dd9cf
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@700dd7a8] was not registered for synchronization because synchronization is not active
13:00:24 INFO c.m.v.c.i.AbstractPoolBackedDataSource:212 Initializing c3p0 pool… com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1bqqhgpaf1p8jrfq18qae3s|6da21078, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1bqqhgpaf1p8jrfq18qae3s|6da21078, idleConnectionTestPeriod -> 60, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/courseimooc?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800000, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 30, maxStatements -> 1000, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
13:00:54 WARN c.m.v.r.BasicResourcePool :223 com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@b8a749 – Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
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:827)
at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:447)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
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:1176)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1163)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)atcom.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1908)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
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.GeneratedConstructorAccessor50.newInstance(Unknown Source)
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.NativeSocketConnection.connect(NativeSocketConnection.java:91)
at com.mysql.cj.NativeSession.connect(NativeSession.java:150)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:947)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:817)
… 12 common frames omitted
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method)
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.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:606)
at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
… 15 common frames omitted
13:00:54 WARN c.m.v.r.BasicResourcePool :223 com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@135dd89e – Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

Answer 1

甲蛙 2021-02-26 18:17:53

百度乱说

ECS和RDS不在一个区，发布到生产后，也应该用外网访问

基本的连接地址、数据库名、用户名、密码不要配置错

另外检查ECS安全组配置，对外访问是不是全关闭了

• 提问者 超人迪加123 #1

  连接地址，数据库名，用户名，密码都确认正确，安全组配置出口方向是默认配置，所以向外访问都是放行的。
  情况有了一些进展，不使用c3p0，不连接云数据库，用jdbc连接云主机中自己安装的mysql8.0连接成功了，登录了控制台。明天再试试使用c3p0连接云主机自己安装的mysql看看会不会复现错误。
  在连接云数据库测试时，看报错信息Access denied for user 'courseimooc'@'localhost' (using password: YES)可能是因为云数据库的默认账号courseimooc@localhost导致被拒绝的，后面应该是%才是开放给所以ip连接。在阿里云控制台中找不到在哪里修改权限。

• 甲蛙 回复 提问者 超人迪加123 #2

  可以看一下RDS的白名单配置试试

• 提问者 超人迪加123 回复 甲蛙 #3

  在云数据库创建了新的更高权限的账户，使用原配置c3p0，成功连接新账户，调试成功。