ERROR 2059 (HY000)

SYMPTOMS

客户端远程连接数据库时出现验证错误

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: No such file or directory

CASE

该原因是 MySQL8 以后，加密规则是 caching_sha2_password，MySQL8 以前加密规则是 mysql_native_password

SOLUTION

1. 修改 my.cnf 文件，将加密规则改为 mysql_native_password，重启服务

   sed -i '/\[mysqld\]/a default_authentication_plugin=mysql_native_password' my.cnf

2. 重置密码，刷新权限

   alter user 'root'@'%' identified by 'new_pass' password expire never;
   alter user 'root'@'%' identified with mysql_native_password by 'new_pass';
   flush privileges;