【无标题】

项目场景：本地搭建一个开源项目时，数据库连接异常 caching_sha2_password

—Unable to load authentication plugin ‘caching_sha2_password’.

问题描述

数据库连不上了 异常信息关键词： Unable to load authentication
caching_sha2_password

具体的异常信息：
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
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: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:2385)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
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:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1643)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1709)
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2715)
Caused by: java.sql.SQLException: Unable to load authentication plugin ‘caching_sha2_password’.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1725)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1250)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2324)
… 13 common frames omitted

原因分析：

mysql版本不正确， mysql8.X.X 版本的 认证模块和 mysql5.x.x版本的认证模块不同：
mysql 8.x.x版本：authentication_plugin 是 ‘caching_sha2_password’；
mysql 5.x.x版本：authentication_plugin 是 ‘mysql_native_password’；

---

解决方案：

查看mysql版本：
SELECT VERSION();
8.4.2
查看mysql 插件
SELECT user,plugin FROM MYSQL.USER;
修改为5.x.x版本的配置
alter user ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘这里输入你的密码’
复查一下是否修改成功
SELECT user,plugin FROM MYSQL.USER;