java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:868) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:864) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1746) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2191) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2222) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2017) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:779) 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:425) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
在报错日志中寻找重点
首先登录Unable to load authentication plugin ‘caching_sha2_password’.
之前看过我写的对于低版本navcat,sqlyog无法连接mysql8.0数据库问题原因以及解决方案这篇文章会发现是一个道理,这边文章不做过多赘述,我们直接解决,具体原因可以看上述文章
root@5cb9e3d9ea74:/# mysql -u root -p
# 输入密码 Enter password:
# 查询数据库 mysql> show databases;
下方是输出log
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)进入mysql数据库
mysql> use mysql;
下方是输出log
Database changed查看所有表
mysql> show tables ;
下方是输出log
+------------------------------------------------------+ | Tables_in_mysql | +------------------------------------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | replication_asynchronous_connection_failover | | replication_asynchronous_connection_failover_managed | | replication_group_configuration_version | | replication_group_member_actions | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +------------------------------------------------------+ 37 rows in set (0.01 sec)查看user表数据情况
mysql> select host,user,plugin from user;
下方是输出log
+-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | root | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | caching_sha2_password | +-----------+------------------+-----------------------+ 5 rows in set (0.00 sec)修改远程连接的账户的plugin字段值
mysql> update user set plugin='mysql_native_password' where user='root' and host='%';
下方是输出log
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0修改本地连接的账户的plugin字段值
mysql> update user set plugin='mysql_native_password' where user='root' and host='localhost';
下方是输出log
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0查看修改结果
mysql> select host,user,plugin from user;
下方是输出log
+-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | root | mysql_native_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | caching_sha2_password | +-----------+------------------+-----------------------+ 5 rows in set (0.00 sec)当您之后一直要使用您现在使用的版本的话 将这个字段的默认值改成mysql_native_password
mysql> alter table user alter column plugin set default 'mysql_native_password';
下方是输出log
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0检查修改完成的表状态
mysql> desc `user`;
下方是输出log
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
######## 新大陆这里发现默认值已经被改变了######
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)
然后就可以重启项目了了诸位!!!
