MySQL 8.0 error 2059 Authentication plugin caching sha2 password

MySQL8.0.11版本默认的认证方式是caching_sha2_password ，MySQL5.7版本则为mysql_native_password。

想在MySQL8.0版本中继续使用旧版本中的认证方式需要在my.cnf 文件中配置并重启，因为此参数不可动态修改。

mysql> set global default_authentication_plugin='mysql_native_password';
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable

写入my.cnf 文件后重启MySQL：

vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password

第二种解决方法：兼容新老版本的认证方式。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER; #修改加密规则 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用户的密码 
FLUSH PRIVILEGES; #刷新权限

--创建新的用户： create user root@'%' identified WITH mysql_native_password BY 'root'; grant all privileges on *.* to root@'%' with grant option; flush privileges; --在MySQL8.0创建用户并授权的语句则不被支持： mysql> grant all privileges on *.* to root@'%' identified by 'root' with grant option;

       ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'root' with grant option' at line 1