MySQL8.0.11连接错误2059解决方法

阅读 332
收藏 1
2018-05-12
原文链接:mp.weixin.qq.com

导读MySQL8.0采用了新的SHA-2插件加密方式,导致很多第三方可视化软件和编程语言接口连接错误,解决这个问题只需要还原native方式,对于编程语言还需要修改配置文件my.cnf或my.ini。

图形化管理工具Navicat连接提示: 验证插件[caching_sha2_password]不能被加载

编程语言PHP7连接提示 :客户端不知道服务器要求的验证方法[caching_sha2_password]

①根本原因

我们看看数据用户表user

mysql> SELECT    ->    host,user,plugin,authentication_string    -> FROM mysql.user    -> ORDER BY user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+| host      | user             | plugin                | authentication_string                                                  |+-----------+------------------+-----------------------+------------------------------------------------------------------------+| localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              || localhost | mysql.session    | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              || localhost | mysql.sys        | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              || %         | root             | caching_sha2_password | $A$005$IR(36rn2wo6gwOqFyOoCpkY4dpE5yqNKqDd.ex6hs.GN6bwcsW5zvIZ9yC || localhost | root             | caching_sha2_password | $A$005$IR(36rn2wo6gwOqFyOoCpkY4dpE5yqNKqDd.ex6hs.GN6bwcsW5zvIZ9yC |+-----------+------------------+-----------------------+------------------------------------------------------------------------+5 rows in set (0.00 sec)

注意到第4行数据 host='%' AND user ='root',认证方式(plugin)就是caching_sha2_password,远程连接匹配这一行生效。字段authentication_string 存放的是加密后的字符串。

注意:直接Update修改plugin需要同时修改加密字符串(这里不建议这样做)。

远程连接:主机名为任意非localhost的连接,通配符%

本地连接:主机名为localhost或127.0.0.1的连接

②解决办法

方案一:修改认证方式

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';Query OK, 0 rows affected (0.01 sec)

修改认证方式为mysql_native_password并设置密码为12345678。可能用到的SQL语句:

/*解决错误ERROR 1819:设置密码可为8位简单密码 */SET GLOBAL validate_password.policy=0; ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '你的密码'; /* 刷新权限,MySQL8 并不需要这一句,测试是这样 */FLUSH PRIVILEGES; 

方案二:新建用户

/* 创建native 验证方式的用户用户名为userNative,密码为12345678*/CREATE USER 'userNative'@'%'IDENTIFIED WITH mysql_native_password BY '12345678';/* 授权 */GRANT ALL PRIVILEGES ON *.* TO  'userNative'@'%';

到这里Navicat就能连接成功了。其他第三方图形管理工具也可以。

当前浏览器不支持播放音乐或语音,请在微信或其他浏览器中播放 我们不一样 大壮 - 我们不一样

然鹅编程语言PHP还是上面的错误,这说明PHP7和Navicat连接方式不一样。

或者说编程语言(不知道Java、Python有待确认)和第三方可视化软件连接MySQL的方式不一样。接着用一个并不存在的用户名userNative111测试连接,也是同样的错误提示缺少sha2,这说明编程语调用时还是caching_sha2_password验证方式。

目前,让PHP连接MySQL8除了账号还要修改配置文件

Linux/windows都是在配置文件中新增

default_authentication_plugin=mysql_native_password

③修改配置文件

Linux系统:

[root@codenight ~]# systemctl restart mysqld.service

修改MySQL配置文件,位于/etc/my.cnf。完成后重启MySQL服务

Windows系统:

打开MySQL配置文件my.ini(若不存在自己建立),增加一行

[mysqld] # port 设置端口 ,若设置3306是默认端口可生路# basedir 设置MySQL的安装目录  # datadir 设置MySQL数据库的数据的存放目录port = 3380basedir=E:\myServer\mysql-8.0.11-winx64\datadir=E:\myServer\mysql-8.0.11-winx64\data# mysql8.0.11 默认值为caching_sha2_passworddefault_authentication_plugin=mysql_native_password[client]# cmd.exe MySQL命令行所用端口 与上方保持一致port = 3380# https://dev.mysql.com/doc/refman/8.0/en/connecting.html

测试成功

④总结

  1. MySQL8修改权限立即生效,不需要FLUSH PRIVILEGES(网上还在误传)

  2. 当host='localhost',因为具体指明了主机名优先级高于通配符%需要留意组合

相关阅读:

MySQL8.0.11安装指南(Windows/Linux)

MySQL5.7.21安装

评论