(本文档仅供参考)
问题描述
连接MySQL数据源,报错信息如下
Caused by: 获取数据库连接失败:获取数据库连接失败:null, message from server: "Host '192.168.1.163' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'",datasourceId=DS.smartbi at smartbi.connectionpool.ConnectionPool.lambda$doGetConnection$0(ConnectionPool.java:853) at ...(...) at smartbi.connectionpool.SmartbiPoolableConnection.doGetConnection(SmartbiPoolableConnection.java:84) Caused by: java.sql.SQLException: 获取数据库连接失败:null, message from server: "Host '192.168.1.163' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" at smartbi.connectionpool.SmartbiPoolableConnection.doGetConnection(SmartbiPoolableConnection.java:84) at smartbi.connectionpool.ConnectionPool.lambda$doGetConnection$0(ConnectionPool.java:849) at smartbi.monitor.MetricHelper$.withSpan(MetricHelper.scala:87) at smartbi.monitor.MetricHelper.withSpan(MetricHelper.scala) at smartbi.connectionpool.ConnectionPool.doGetConnection(ConnectionPool.java:812) at smartbi.connectionpool.ConnectionPool.driverConnect(ConnectionPool.java:630) at smartbi.connectionpool.ConnectionPool.getConnection(ConnectionPool.java:958) at smartbi.connectionpool.ConnectionPool.getConnection(ConnectionPool.java:897) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1310) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabase(DBSQLResultStore.java:1274) at smartbi.freequery.querydata.store.DBSQLResultStore.ensureGridDataInMemDB(DBSQLResultStore.java:6412) at smartbi.freequery.querydata.store.DBSQLResultStore.getGridDataInternal(DBSQLResultStore.java:5733) at smartbi.freequery.querydata.store.SQLResultStore.getGridData(SQLResultStore.java:280) at smartbi.freequery.querydata.store.SQLResultStore.getGridData(SQLResultStore.java:270) at smartbix.augmenteddataset.bo.impl.BusinessViewProxy.getGridData(BusinessViewProxy.java:307) at smartbix.augmenteddataset.bo.impl.SQLViewBO.getGridData(SQLViewBO.java:170)
解决方案
错误信息是指:当一个主机因为多次连接失败(如认证错误、网络问题等)而触发了MySQL的内置安全机制时,MySQL会临时阻止该主机发起新的连接请求,这是一种防御措施,旨在防止可能的恶意攻击或误配置导致的资源耗尽。
可以在mysql服务器上的shell终端中执行如下命令,解除该IP地址的封锁状态,示例:
mysqladmin -u root -p flush-hosts
建议同步检查mysql的最大连接配置,看是否因为连接数不够,导致出现此问题。
另,若报错的是一键部署包中的MySQL库,由于安装时为了减小安装包的大小,删除了部分文件,无法执行“mysqladmin -u root -p flush-hosts”,对此,处理方案如下:
1、调整最大连接数,默认是500,在my.cnf里,调整max_connections的值
2、重启MySQL
cd
<安装目录>
/Mysql/
.
/mysqld
restart
3、加参数useSSL=false:数据连接-测试时提示如下:连接池初始化失败: SOL错误:Communications link failure