Navicat 连接MySQL8.0，错误：Authentication plugin 'caching_sha2_password' cannot be loaded

原创

catoop 2022-02-25 16:30:42 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者catoop的原创作品，请联系作者获取转载授权，否则将追究法律责任

在使用 Navicat Premium 12 连接MySQL数据库时会出现Authentication plugin ‘caching_sha2_password’ cannot be loaded的错误（Navicat 新版本应该会修复这个问题）。

出现这个原因是mysql8 之前的版本中加密规则是 mysql_native_password，而在mysql8之后，加密规则是caching_sha2_password，解决问题方法有两种：

一种是升级navicat驱动

一种是把mysql用户登录密码加密规则还原成mysql_native_password

还原 mysql_native_password 的方法为：

root@mysql8:/# mysql -uroot -p                                                                                                                                                                              
Enter password:                                                                                                                                                                                             
Welcome to the MySQL monitor.  Commands end with ; or \g.                                                                                                                                                   
Your MySQL connection id is 9                                                                                                                                                                               
Server version: 8.0.16 MySQL Community Server - GPL                                                                                                                                                         

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.                                                                                                                                

Oracle is a registered trademark of Oracle Corporation and/or its                                                                                                                                           
affiliates. Other names may be trademarks of their respective                                                                                                                                               
owners.                                                                                                                                                                                            

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                                              

mysql> use mysql;                                                                                                                                                                                           
Reading table information for completion of table and column names                                                                                                                                          
You can turn off this feature to get a quicker startup with -A                                                                                                                                              

Database changed                                                                                                                                                                   
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;                                                                                                                          
Query OK, 0 rows affected (0.50 sec)                                                                                                                                                                        

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';                                                                                                                     
Query OK, 0 rows affected (0.24 sec)                                                                                                                                                                        

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';                                                                                                                             
Query OK, 0 rows affected (0.17 sec)                                                                                                                                                                                                 

mysql> FLUSH PRIVILEGES;                                                                                                                                                                                    
Query OK, 0 rows affected (0.16 sec)