参考了很多资料:了解到:
很多用户在使用Navicat Premium 12连接MySQL数据库时会出现Authentication plugin ‘caching_sha2_password’ cannot be loaded的错误。
出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password.
那我们就改表mysql的加密规则:
先登录mysql
mysql -u root -p
一定要先赋予权限:
grant all privileges on *.* to root@'localhost' identified by '密码';
报错: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 ‘123456’’ at line 1
mysql8已经不支持这种写法:要用
grant all privileges on *.* to 'root'@'%' ;
或:
grant all privileges on *.* to 'root'@'localhost' ; mysql> use mysql; select user,host from user;
然后输入:
注意我的root,host是’%’
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
刷新权限:
FLUSH PRIVILEGES;
然后重新连接
意思是:
这里root是你的用户名,localhost就是你ip地址等,%表示远程的意思,也就是你表里面查出来的值
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’; #更新一下用户的密码
这里的password就是你的密码!!!
————————————————
版权声明:本文为CSDN博主「春风霓裳」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_43470725/article/details/122829814
- Authentication caching_sha password 错误 Navicatauthentication caching_sha password navicat authentication caching_sha password错误 authentication caching_sha password caching mysql authentication caching_sha password authentication caching_sha navicate password caching_sha password caching navicat caching_sha password错误caching caching_sha_password password错误navicat password runtimeerror cryptography caching_sha caching_sha插件password身份