拒绝用户'root'@'localhost‘ubuntu20.04和MYSQL8的MYSQL8访问

Question

我一直在犯这个错误。我一直在看一些QA网站，试图重新设置我的本地mysql我已经在[mysqld]下添加了D1，现在我在安全模式下运行。我遵循了这些步骤

mysqld_safe --skip-grant-tables &
use mysql;
update user set password=PASSWORD(”my_password”) where user=’root’;

当我进入更新的最后一步时，我会得到以下错误：

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 '("my_password") where user="root"' at line 1

我也试过

    UPDATE mysql.user SET 
    authentication_string=PASSWORD(''), 
    plugin='mysql_native_password'
   WHERE User='root' AND Host='localhost';

还有这个

ALTER USER 'root'@'localhost IDENTIFIED WITH mysql_native_password BY 'root';

但是得到同样的错误：

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 'Ihost IDENTIFIED WITH mysql_native_password BY 'root'' at line 1

不知道如何在此重新设置我的密码。

Answer 1

我每次都在更新对我有用的内容，请遵循以下步骤

1. 以根用户sudo mysql -u root身份登录
2. 选择mysql db use mysql;
3. 检查所需用户使用的插件，如果它是auth_login？使用此查询mysql_native_password将其更改为UPDATE user SET plugin='mysql_native_password' WHERE User='root';
4. 现在使用此查询更新根用户或任何其他用户的密码ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
5. 使用sudo systemctl restart mysql重新启动mysql服务