Turn off authentication-plugin on Mysql 8.0 commercial server

最新推荐文章于 2025-03-06 17:10:47 发布

原创最新推荐文章于 2025-03-06 17:10:47 发布 · 220 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql

本文介绍了MySQL8.0中用户管理的方法，包括通过更改指定用户的认证插件以及修改MySQL配置文件来解决登录限制的问题，并提供了创建新用户的命令示例。

Mysql 8.0 commercial server uses caching_sha2_password as the default authentication plugin as you can see in the table mysql.user. Meanwhile, Mysql also restricts the specified users to log in from the specified hosts.

There are some articles mentions the following method to add user:
grant select,insert,update,delete on . to test1@“%” Identified by “abc”;
And I tried several times and Mysql noticed that there’s some syntax error.

Method 1: Change the auth plug-in of the specified user.
The following method runs ok on my environment:
2、ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘you password’; #更新一下用户的密码
3、FLUSH PRIVILEGES; #刷新权限
4、alter user ‘root’@‘localhost’ identified by ‘you password’; #再重置下密码
就可以解决问题了。

F.Y.I.
To add a new user, the following commands works:
mysql> CREATE USER ‘aus’@’%’ IDENTIFIED BY ‘AtHome1234.’;
GRANT all ON aus.* TO ‘aus’@’%’;

Method 2: Change the configuration of mysql, to be specific, /etc/my.cnf.
uncomment the following line:
default-authentication-plugin=mysql_native_password
I deem this will work, although I don’t test it.

References:
1.https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html
2.https://dev.mysql.com/doc/refman/8.0/en/connection-access.html
3.https://jingyan.baidu.com/article/414eccf6b41e6b6b431f0aec.html