未提供密码时如何禁用 MySQL root 登录?

2022-01-22 00:00:00 root passwords mysql login

MySQL 安装在我的笔记本电脑上，它工作正常，除了我可以在不提供 root 密码的情况下登录.我也可以通过提供 root 密码登录.如果提供的密码不匹配，则拒绝访问.最初安装 MySQL 时，root 密码已更改为我自己选择的密码.我今天才注意到无密码登录.

MySQL is installed on my laptop and it works fine, except that I am allowed to log in without supplying the root password. I can also log in by supplying the root password. If the supplied password doesn't match, it denies access. The root password was changed to something of my own choosing when I originally installed MySQL. I just noticed the no-password logins today.

所以，如果没有提供密码，我需要停止对 root 帐户的访问.到目前为止，我尝试使用以下方法重置 root 密码:

So, I need to stop access to the root account when a password isn't supplied. What I've tried so far is to reset the root password with:

mysqladmin -u root password TopSecretPassword

然后我登录到控制台并发出:

I then logged in to the console and issued:

mysql> flush privileges;  exit;

我仍然可以通过以下方式登录 MySQL:

I'm still able to log in to MySQL with: 

%> mysql -u  {enter}

如何阻止这种行为?

其他细节:

%> mysql -u  {enter}

mysql>SELECT USER(), CURRENT_USER();
> root@localhost, root@localhost

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='root' AND password='';
> COUNT(*)
> 0

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='';
> COUNT(*)
> 0

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='root';
> COUNT(*)
> 1

%> vi /etc/my.cnf
/skip-grant-tables
> E486: Pattern not found: skip-grant-tables

推荐答案

目前的答案不再适用于 MySQL 8.确实:

Current answers are no longer ok for MySQL 8. Indeed:

在 MySQL 8.0 中，caching_sha2_password 是默认的身份验证插件，而不是 mysql_native_password，后者是 MySQL 5.7 中的默认值.

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password, which was the default in MySQL 5.7.

所以解决方法是运行mysql，然后

So the solution is to run mysql, then

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'; EXIT;

然后 mysql -uroot -p 试试它是否有效.(我不记得是否需要 service mysql restart).

and then mysql -uroot -p to try if it worked. (I don't remember if service mysql restart was necessary).

如果您已经应用了这个实际问题的主要答案中的技术，这里是如何为 MySQL 8 恢复它:

If you already applied the technique from the main answer of this actual question, here is how to revert it for MySQL 8:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE user = 'root' AND host = 'localhost'; 
FLUSH PRIVILEGES;

相关文章