mysql主机被阻止

nbewdwxp  于 2021-07-03  发布在  Java
关注(0)|答案(1)|浏览(314)

我们目前在生产服务器上有一个主要问题。我们在一台服务器上有一个mysql数据库(ubuntu14.04和mysql版本5.5.57)。
我们有另一台服务器,所有的应用程序都与服务器1上的数据库通信。5天来,我们一直在第二台服务器上收到此错误:

05-09-2017 00:00:01 WARN com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@623841b8 -- 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.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2395)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2316)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
  Caused by: java.sql.SQLException: null,  message from server: "Host 'IP_ADDRESS' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2334)

我们已经尝试了5天来解决这个问题,但每次我们重新启动mysql的几个小时,然后错误再次开始。我已经将max\u connect\u错误从10增加到50,但这只是延长了错误。
然后我们还将日志级别调整为2,但是在错误日志中唯一看到的是中止的连接,没有“拒绝”的连接。
有什么方法可以让我看到实际的连接错误吗?请帮忙解决这个问题,我不知道怎么解决。我们也没有dba,所以它只是一个不是数据库Maven的开发团队。
编辑:该应用程序是一个javamaven应用程序,使用hibernate连接到数据库。下面是hibernate配置文件:

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.username">USERNAME_HERE</property>
    <property name="hibernate.connection.password">PASSWORD_HERE</property>
    <property name="hibernate.connection.url">jdbc:mysql://SERVER_HERE:3306/voda_soccer?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.autoReconnect">true</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
    <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
    <property name="show_sql">false</property> 

    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">3000</property>

    <mapping class="com.myapp.sms.producer.entity.Customer"></mapping>
    <mapping class="com.myapp.sms.producer.entity.Gifting"></mapping>
    <mapping class="com.myapp.sms.producer.entity.BulkCampaign"></mapping>
</session-factory>

我们总是在最后一个区块中关闭连接:

} finally {
        if (session != null) {
            try {
                session.close();
            } catch (Exception e) {}
        }
        session = null;
    }

我知道我们可以刷新主机或重启mysql,但我不能在生产服务器上一直这样做。我想知道为什么会出现连接错误,以及在哪里可以看到这些错误?

abithluo

abithluo1#

似乎存在连接泄漏,或者可能有长时间运行的查询阻止其他代码段获取连接。
根据您使用的数据源工具(c3p0、hikari等),您可以通过jmx(jsconsole、visualvm)监视您的池。
您还可以使用flexy池进行一些监视。
mysql工作台还提供了一些有用的工具,以便获得有关负责的操作的更多信息。

相关问题