MySQL 错误 1396 (HY000)：操作 CREATE USER 失败，因为 'username'@'localhost' IDENTIFIED BY 'mypassword';
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17008610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 1396 (HY000): Operation CREATE USER failed for 'username'@'localhost' IDENTIFIED BY 'mypassword';
提问by Fahim Parkar
Mistakenly I deleted my root user from my mysql.usertable.
我错误地从我的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): Operation CREATE USER failed for 'username'@'localhost'
As per this SO answer, I tried FLUSH PRIVILEGES;, but still I get same error.
根据this SO answer，我尝试过FLUSH PRIVILEGES;，但仍然遇到相同的错误。
Any idea what is going wrong?
知道出了什么问题吗？
Answer
回答
I also had to delete the same from mysql.dbtable
我还必须从mysql.db表中删除相同的内容
delete from mysql.db where user='username';
That's it...
就是这样...
采纳答案by Prabhu
If you use DROP USERcommand 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 mysqlor run FLUSH PRIVILEGESbefore creating the same user because there are issues with privileges.
To restart mysql in linux based systems
您必须在创建相同用户之前restart 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 REVOKEcommand if you know which privileges that the user has
REVOKE如果您知道用户拥有哪些权限，您也可以使用命令
REVOKE privillege1, privillege2, ... FROM deleted_user@host
回答by RandomSeed
This user must be referenced in other tables from the mysqlsystem 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).
永远不要弄乱这个模式的另一个原因（对不起，我无能为力）。
回答by Greg Woz
As for the above, passwordfield does not exist in my case and mysql throws an error.
对于上述情况，password在我的情况下不存在字段，并且 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'));
