mysql登录报错RROR 2059 (HY000): Authentication plugin 'caching_sha2_password'...

jiawang

mysql登录报错如下：

[root@sztech setup]# mysql -uroot -p

Enter password:

ERROR 2059 (HY000): Authentication plugin'caching_sha2_password' cannot be loaded:/usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared objectfile: No such file or directory

报错原因：

出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password，从MySQL 8.0.4开始， MySQL服务器的默认身份验证插件从mysql_native_password更改为caching_sha2_password。

解决办法

把mysql用户登录密码加密规则还原成 mysql_native_password 模式

解决步骤：

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
+-----------+------------------+-----------------------+

4 rows in set (0.03 sec)

将caching_sha2_password修改为mysql_native_password即可

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

刷新

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

再次检查

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)