caching_sha2_password问题解决

遇到如下问题：

connection to database 'db' failed: [2059] Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_pasch file or directory

#降低密码等级和长度要求
set global validate_password.policy=LOW; 
set global validate_password.length=4;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用户的密码
ALTER USER 'zabbix'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES; #刷新权限

并修改统一策略

找到my.ini文件，在[mysqld]下添加

default_authentication_plugin=mysql_native_password

错误原因

新版本的MySQL新特性导致，导致认证方式有问题。

MySQL8.0版本默认的认证方式是caching_sha2_password;而在MySQL5.7版本则为mysql_native_password

可通过如下sql查看

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

参考

Authentication plugin ‘caching_sha2_password‘ cannot be loaded: /usr/lib64/mysql/plugin/