一、问题描述:
在 VMware 虚拟机的客户机 CentOS7 里面安装运行有 Docker 的 MySQL 8.0,由于当前 CentOS7 默认的 MySQL 客户端版本太低(5.5.60) ,导致连接服务器时出现以下的错误:
[xxx@yyyMac page_demo $ mysql -h 【IP地址】 -uroot -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
二、问题原因:
低版本mysql 5.x的客户端认 mysql_native_password 认证插件,而高版本mysql 8.x认 caching_sha2_password 插件
三、解决方案:
-
用高版本的 MySQL,或者进入该 Docker 容器,登录 MySQL 服务器。
-
执行 MySQL shell 命令查看服务器的版本:
> select version()
+-----------+
| version() |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.00 sec)
3.查看当前默认的密码认证插件:
> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
4.查看当前所有用户绑定的认证插件:
> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | healthchecker | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
5.假如想更改 root 用户的认证方式
# 修改加密规则
> ALTER USER 'root'@'%' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;
# 更新用户密码
> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY ‘123456Aa?';
# 赋予 root 用户最高权限
> grant all privileges on *.* to root@'%' with grant option;
# 刷新权限
> flush privileges;
注意:在这之后,将不再支持以下的权限授予语句:
grant all privileges on *.* to root@'%' identified by '123456' with grant option;