在用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>