Navicat Premium连接不上MySQL，报错：“Authentication plugin 'caching_sha2_password' cannot be loaded……”

在用Navicat Premium连接MySQL数据的时候，会遇到以下错误提示 

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found 

原因是：安装完MySQL后，root 的密码默认是用 caching_sha2_password 插件加密的。而客户端找不到 caching_sha2_password 插件，于是登录不上。

那这种情况的话就需要修改root密码加密类型，更改为 mysql_native_password

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

这时候如果是出现了这个错误说明密码的策略不满足要做，需要修改策略。先查询一下MySQL变量中密码的验证方式

我们需要将validate_password.policy修改为LOW

$ set global validate_password.policy=LOW;

在执行以下命令就可以了

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

 

Docker 中MySQL镜像出现了类型问题解决办法类似 

进入到Docker中mysql容器

$ docker exec -it mysql8 /bin/bash

登录到容器中mysql

root@dbf73b002f97:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 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> 

设置权限（为root分配权限，以便可以远程连接） 【可选】

mysql> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

 

重置密码规则和密码 

mysql> show variables like '%password%';
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 0               |
| disconnect_on_expired_password               | ON              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
+----------------------------------------------+-----------------+
14 rows in set (0.00 sec)

[可选]
mysql> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'root'@'%' IDENTIFIED by 'password' password expire never;
Query OK, 0 rows affected (0.01 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>