错误1396（HY000）：“用户名”@“localhost”IDENTIFIED BY'mypassword'的操作CREATE USER失败;

Mistakenly I deleted my root user from my mysql.user table.

我错误地从mysql.user表中删除了我的root用户。

delete from mysql.user where user='username';

To make same root user, I am trying to fire below query,

要创建相同的root用户，我试图触发查询，

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

I get error as

我得到错误

ERROR 1396 (HY000): Operation CREATE USER failed for 'username'@'localhost'

ERROR 1396（HY000）：“用户名”@“localhost”的操作CREATE USER失败

As per this SO answer, I tried FLUSH PRIVILEGES;, but still I get same error.

根据这个SO答案，我尝试了FLUSH PRIVILEGES;但是我仍然得到同样的错误。

Any idea what is going wrong?

什么是错的？

Answer

I also had to delete the same from mysql.db table

我还必须从mysql.db表中删除相同的内容

delete from mysql.db where user='username';

That's it...

而已...

3 个解决方案

#1

If you use DROP USER command to remove the user, then the user gets removed completely. If you have to add user with same username later, you can use FLUSH PRIVILEGES command to completely remove privileges from the mysql memory.

如果使用DROP USER命令删除用户，则会完全删除用户。如果以后必须添加具有相同用户名的用户，则可以使用FLUSH PRIVILEGES命令从mysql内存中完全删除权限。

DROP USER username@hostname;

You have to restart mysql or run FLUSH PRIVILEGES before creating the same user because there are issues with privileges. To restart mysql in linux based systems

您必须在创建同一用户之前重新启动mysql或运行FLUSH PRIVILEGES，因为存在特权问题。在基于linux的系统中重启mysql

sudo service mysql restart

to restart in windows systems

在Windows系统中重新启动

net stop mysql
net start mysql

To flush privilege in mysql prompt

在mysql提示符中刷新权限

FLUSH PRIVILEGES;

You can alternatively use REVOKE command if you know which privileges that the user has

如果您知道用户拥有哪些权限，您也可以使用REVOKE命令

REVOKE privillege1, privillege2, ... FROM deleted_user@host

#2

This user must be referenced in other tables from the mysql system schema. I would recreate the user the same way you deleted it:

必须在mysql系统架构的其他表中引用此用户。我会以与删除它相同的方式重新创建用户：

INSERT INTO mysql.user (user, host, password)
VALUES ('root', 'localhost', PASSWORD('pasw'));

Then

然后

FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

Yet another reason to never mess with this schema (sorry, I couldn't help).

另一个永远不会弄乱这个架构的原因（抱歉，我无法帮助）。

#3

As for the above, password field does not exist in my case and mysql throws an error.

至于上面的说法，我的情况下不存在密码字段，而mysql会抛出错误。

This command helped:

这个命令帮助：

INSERT INTO mysql.user (user, host, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) VALUES ('user', 'localhost', '', '', '', 'mysql_native_password', PASSWORD('pass'));

#1

DROP USER username@hostname;