一、Mysql远程登录报错解决办法
1.简介
使用8.0以上版本的Mysql,远程连接mysql会报错“2059-Authentication plugin ‘caching_sha2_password’ cannot be loaded”,因为8.0版本的Mysql和之前版本的Mysql的密码认证方式发生了变化,由mysql_native_password改变为caching_sha2_password。
8.0Version
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec) mysql> show variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.01 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 | | localhost | root | caching_sha2_password | +-----------+------------------+-----------------------+ 5 rows in set (0.00 sec)
5.7Version
mysql> select version(); +------------+ | version() | +------------+ | 5.7.15-log | +------------+ 1 row in set (0.00 sec) mysql> show variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | mysql_native_password | +-------------------------------+-----------------------+ 1 row in set (0.01 sec) mysql> select host,user,plugin from mysql.user; +-----------+-----------+-----------------------+ | host | user | plugin | +-----------+-----------+-----------------------+ | localhost | root | mysql_native_password | | localhost | mysql.sys | mysql_native_password | | % | root | mysql_native_password |
2.解决办法一
兼容之前版本的解决办法
ALTER USER 'root'@'%' IDENTIFIED BY '12345678' PASSWORD EXPIRE NEVER; #修改加密规则 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678'; #更新一下用户的密码 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 mysql>
3.解决办法二
若想在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
评论前必须登录!
注册