Donnie

不积跬步无以至千里

远程访问mysql8.0

从mysql5.6换到mysql8.0注意点

1.连接测试的时候就会报错:client does not support authentication protocol requested by server; consider upgrading MySQL client,这里的错误信息就是不支持身份认证方式,需要配置认证方式,默认的密码加密方式是:caching_sha2_password,而现在很多客户端工具还不支持这种加密认证方式。
所以需要在my.conf加入:

    default_authentication_plugin=mysql_native_password

2.分配权限 使用以前方式会出现异常
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '123456' 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 '123456' WITH GRANT OPTION' at line 1
正确分配如下:


    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'  WITH GRANT OPTION;
    FLUSH   PRIVILEGES;

3.客户端连接会出现:this authentication plugin is not supported,需要重新更新密码


    ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

    flush privileges;

注意是使用mysql_native_password对新密码进行编码
注意在连接mysql的url上加上&allowNativePasswords=true
如beego


    root:123456@tcp(192.168.1.17:3306)/db?charset=utf8&allowNativePasswords=true

赞赏支持