nacos访问mysql出现问题

最新推荐文章于 2024-05-30 15:01:11 发布
最新推荐文章于 2024-05-30 15:01:11 发布
阅读量864 收藏
点赞数
文章标签： nacos 兼容mysql问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zuocheng217/article/details/118228299
版权

tail -f start.out

at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 15 more
Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1684)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2085)
        ... 28 more
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:523)
        at com.alibaba.nacos.config.server.service.BasicDataSourceServiceImpl$SelectMasterTask.run(BasicDataSourceServiceImpl.java:317)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
        ... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.GeneratedConstructorAccessor75.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:377)
        at com.mysql.jdbc.Util.getInstance(Util.java:360)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2144)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2069)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        at sun.reflect.GeneratedConstructorAccessor73.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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 15 more
Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1684)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2085)
        ... 28 more
^C
[root@localhost logs]# ls
cmdb-main.log              config-pull-check.log  nacos_gc.log.0.current  naming-raft.log
config-client-request.log  config-pull.log        nacos.log               naming-rt.log
config-dump.log            config-server.log      naming-distro.log       naming-server.log
config-fatal.log           config-trace.log       naming-event.log        start.out
config-memory.log          istio-main.log         naming-performance.log
config-notify.log          nacos-address.log      naming-push.log
[root@localhost logs]# ps -ef | grep nacos | grep -v grep  | wc -l
1
[root@localhost logs]# ps -ef | grep nacos | grep -v grep  | wc -l
1
[root@localhost logs]# ps -ef | grep nacos | grep -v grep  | wc -l
1
[root@localhost logs]# tail -f start.out
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 15 more
Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1684)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2085)
        ... 28 more
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:523)
        at com.alibaba.nacos.config.server.service.BasicDataSourceServiceImpl$SelectMasterTask.run(BasicDataSourceServiceImpl.java:317)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
        ... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.GeneratedConstructorAccessor75.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:377)
        at com.mysql.jdbc.Util.getInstance(Util.java:360)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2144)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2069)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        at sun.reflect.GeneratedConstructorAccessor73.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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 15 more
Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1684)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2085)
        ... 28 more

如上启动3个nacos实例时，后端报错主要在
Caused by: java.sql.SQLException: Unable to load authentication plugin ‘caching_sha2_password’.
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
于是百度搜索下呵呵~！

参考https://blog.csdn.net/h996666/article/details/81779299
1）使用navicat登录mysql（pass)
use mysql
select host,user,plugin from user;
在这里插入图片描述
看第一行，root加密方式为caching_sha2_password。
caching_sha2_password 是8.x，mysql_native_password是5.x
可以使用命令将他修改成mysql_native_password加密模式：
update user set plugin=‘mysql_native_password’ where user=‘root’;
参考https://blog.csdn.net/h996666/article/details/80921913
但是，我这里重启后mysql后在次登录mysql登不上了！！！！

[root@localhost ~]# docker exec -it 867b549845a5 /bin/bash
root@867b549845a5:/# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@867b549845a5:/# mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

主要参考https://blog.csdn.net/zkzbhh/article/details/100214667

docker 找my.cnf
find / -name mysql/my.cnf
找到mysql/my.cnf文件配置添加
[mysqld]
skip-grant-tables

启动
docker restart 

进入
docker exec -it

无密码登录
mysql -uroot -p

use mysql;
select user,authentication_string,host from user;

更新为空
update user set authentication_string='' where user='root';
flush privileges;

退出mysql，把第一步的skip-grant-tables注释。再重启mysql

使用root用户登录
修改root密码
alter user user() identified by "123456";
flush privileges;


设置远程主机登录
update user set user.Host='%' where user.User='root'; 
flush privileges;
报错不予理会
https://blog.csdn.net/yuanhong55/article/details/75000208
https://www.cnblogs.com/6J2B2/p/12127486.html


附加授权（远程连接MYSQL提示Host is not allowed to connect to this MySQL server）
GRANT all ON *.* TO 'root'@'%' IDENTIFIED BY '123456' ；
flush privileges；

2）进入docker mysql 控制台
参考https://blog.csdn.net/waterflying2015/article/details/81047128

docker exec -it mysql bash
mysql -u root -p
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

这个估计和1）一样吧pass，而且没有试过

3）连接超时问题
参考https://blog.csdn.net/a393720496/article/details/109159845

以上都没有解决我的问题！！！！

**

最终解决：版本兼容问题

**
在nacos安装目录下创建目录plugins/mysql，找到对应mysql版本jar包
我这里是mysql-connector-java-8.0.21.jar

Arthas217
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
nacos-mysql
04-23
4. **权限信息表**：用于实现Nacos的权限控制，包括用户、角色、权限关联等信息，确保只有授权的用户或角色才能访问和操作特定的配置。 在实际使用过程中，我们还需要关注Nacos与MySQL的连接配置。Nacos启动时会...
nacos-server-2.0.3(Mysql配置方式).zip
10-10
4. **验证连接**：启动成功后，访问Nacos的Web管理界面（默认是`http://localhost:8848/nacos`），如果能正常登录且展示服务列表，说明Nacos已经成功连接到MySQL数据库。 5. **使用Nacos**：现在，你可以通过Nacos...
nacos配置文件中注解与中文导致打包失败
qq_45277581的博客
01-05 2116
nacos配置文件中注解与中文导致打包失败
初始导入项目时发生java.sql.SQLException: connect error
m0_55783367的博客
03-17 7097
初始导入项目时发生错误无法启动 2021-03-17 10:38:10 [ERROR] [main ] [] c.a.d.p.DruidDataSource - {dataSource-1} init error java.sql.SQLException: connect error, url jdbc:log4jdbc:ABase://IP:PORT/DATABASE, driverClass net.sf.log4jdbc.sql.jdbcapi.DriverSpy at com.alibaba.dr
Nacos启动报错
最新发布
m0_57440463的博客
05-30 233
Nacos 启动报错 Caused by: java.lang.NullPointerException
JDBC连接数据库连接不上
weixin_43542635的博客
05-11 1492
意外情况 突然连接不上 linux 上的 mysql 在Linux登录到MySQL数据:mysql -uroot -p 输入密码 切换到mysql数据库 mysql>use mysql 输入grant all privileges on *.* to root@"%" identified by "密码"; mysql> grant all privileges on *.* to root@"%" identified by "*****"; 这表示是给本地ip赋予了所有的权限，包括
JDBC连接数据库
zj23qq的专栏
08-31 511
Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code. import java.sql.* ; // for standard JDBC programs import java.math.* ; // for BigDeci
已解决java.sql.SQLException异常的正确解决方法，亲测有效！！！
小明的Java问道之路
01-26 1万+
已解决java.sql.SQLException异常的正确解决方法，亲测有效！！！
nacos 1.4.1 m1没有问题
05-13
如果某个实例出现问题，Nacos会将其从服务列表中移除，避免了故障传播。 5. **多数据源支持**：Nacos可以连接多种数据库，如MySQL、Oracle等，提供多样化的数据存储方案，满足不同场景的需求。 6. **高可用性**：...
naco支持所有版本mysql
10-03
Nacos能够适配这些不同版本的MySQL，意味着用户可以根据项目需求选择最适合的数据库版本，而无需担心与Nacos的集成问题。 在MySQL 8.0版本中，引入了诸多重要改进，包括更强大的ACID（原子性、一致性、隔离性和持久...
解决java.sql.SQLException: com.mysql.cj.jdbc.Driver报错
weixin_54246980的博客
03-14 1万+
类似这两类的报错的时候经总结可能有如下几个问题： 1.首先检查你的jar/pom文件的mysql版本号: mysql5.x.x的版本配置应为:"com.mysql.jdbc.driver" mysql6.x.x及在6以上的版本配置应为："com.mysql.cj.jdbc.driver"
java.sql.SQLException: connect error, url jdbc:mysql//localhost:3306
aaajjjljuvg的博客
10-11 8815
今天在回顾SSM时，写了一个小Demo. 当使用Junit在Test包下测试service层时，发生了一件很奇怪的事，一直报同一个错，但是不退出程序： mysql-connerctorl版本： <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.2
启动nacos报Caused by: java.sql.SQLNonTransientConnectionException
qq_42139461的博客
08-20 3121
启动nacos报Caused by: java.sql.SQLNonTransientConnectionException 现象如下： Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. at com.mysql.cj.jdbc.exceptions.SQLError.c
【已解决】：java.sql.SQLException 问题
热门推荐
04-21 4万+
【已完美解决】：java.sql.SQLException 问题
connect error, url jdbc://localhost:3306/route, driverClass com.mysql.jdbc.Driver
weixin_55345631的博客
12-17 8407
可能是application.yml中的文件配置错了 要改为 问题就解决了 在配置的过程中一定要细心
mysql驱动P6SpyDriver java.sql.SQLException: connect error
qq690452074的专栏
06-21 2257
java.sql.SQLException: connect error, url jdbc:mysql://xx.xx.xx.xxx:3306/abc?useUnicode=true&characterEncoding=UTF-8&serverTimeZone=GMT, driverClass com.p6spy.engine.spy.P6SpyDriver at com.alibaba.druid.pool.DruidAbstractDataSource.cre...
java.sql.SQLException: connect error, url http://www.example.com, driverClass com.mysql.jdbc.Driver
gulang54321的博客
12-15 6484
解决方法就是将pom文件的那段文字删除，不知到怎么出现的
Nacos启动失败 Caused by java.sql.SQLNonTransientConnectionException Could not create connection
weixin_45683240的博客
04-26 5462
Nacos启动失败 Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据及流量管理。 Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。 Nacos 是构建以“服务”为中心的现代
nacos引用mysql
04-23
Nacos是一个用于动态服务发现、配置管理和服务管理的开源平台。它支持多种数据源，包括MySQL数据库。通过将Nacos与MySQL集成，可以实现将配置...应用程序可以通过访问Nacos来获取配置信息，并在运行时动态更新配置。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值