分享交流
合作共赢!

Mysql基础使用方法总结【二】

一、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
赞(0) 打赏
未经允许不得转载:琼杰笔记 » Mysql基础使用方法总结【二】

评论 抢沙发

评论前必须登录!

 

分享交流,合作共赢!

联系我们加入QQ群

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏