MySQL 不允许用户登录：错误 1524
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34935619/
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
MySQL Won't let User Login: Error 1524
提问by MikeJava
Server version: 5.7.10 MySQL Community Server (GPL)
In MySQL, I have a user mangos. The user worked perfectly when I created it. After rebooting my computer, though, attempting to login to mangos yielded this output:
在 MySQL 中，我有一个用户 mangos。当我创建它时，用户工作得很好。但是，在重新启动计算机后，尝试登录 mangos 会产生以下输出：
$ mysql -u mangos -p
Enter password:
ERROR 1524 (HY000): Plugin '*some_random_long_hash_I_cannot_remember' is not loaded
$
It kind of reminded me of a password hash, so after investigating mysql.user, I found that mangos had no password! I updated the password:
这让我想起了密码哈希，所以在调查 mysql.user 后，我发现 mangos 没有密码！我更新了密码：
SET PASSWORD FOR 'mangos'@'127.0.0.1' = PASSWORD('mangos');
FLUSH PRIVILEGES;
Now, I get:
现在，我得到：
ERROR 1524 (HY000): Plugin '*3FBBDB84EA2B2A0EA599948396AD622B7FF68183' is not loaded
3FBBDB84EA2B2A0EA599948396AD622B7FF68183is the same number shown in the password column of mysql.user for mangos, and is a different number than originally. I still can't log in.
3FBBDB84EA2B2A0EA599948396AD622B7FF68183与 mangos 的 mysql.user 的密码列中显示的数字相同，并且与原始数字不同。我还是无法登录。
How do I make MySQL recognize a password properly?Is that even the issue here?
如何让 MySQL 正确识别密码？这甚至是这里的问题吗？
Edits:
编辑：
mysql> SELECT * FROM mysql.user WHERE user = 'mangos' \G
*************************** 1. row ***************************
Host: localhost
User: mangos
Password: *3FBBDB84EA2B2A0EA599948396AD622B7FF68183
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: *3FBBDB84EA2B2A0EA599948396AD622B7FF68183
authentication_string: NULL
password_expired: N
回答by miken32
It appears your user table is corrupted. Likely the reboot you mentioned triggered an upgrade to MySQL and the mysql_upgradescript was not run. This should resolve the situation:
看来您的用户表已损坏。您提到的重启可能触发了对 MySQL 的升级，并且mysql_upgrade脚本没有运行。这应该可以解决以下情况：
mysql_upgrade -u root -ppassword --skip-grant-tables
mysql -u root -ppassword -e "UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'mangos'; FLUSH PRIVILEGES"
Source: http://kb.odin.com/en/126676
来源：http: //kb.odin.com/en/126676
Providing the --forceoption to mysql_upgradewill re-apply the upgrade scripts even if an upgrade has already been done. This may be needed in case of partial restoration from backup.
提供--force选项mysql_upgrade将重新应用升级脚本，即使升级已经完成。在从备份部分恢复的情况下可能需要这样做。
Also worth mentioning, the command to change a user password has changed in MySQL 5.7.6/ MariaDB 10.2.0and forward:
另外值得一提的是，更改用户密码的命令在MySQL 5.7.6/ MariaDB 10.2.0及以后版本中发生了变化：
ALTER USER mangos IDENTIFIED BY 'mangos';
This is now the preferred method for setting the password, although the older SET PASSWORDsyntax is not officially deprecated.
这是现在设置密码的首选方法，尽管旧的SET PASSWORD语法并未正式弃用。
回答by knocte
mysql_upgrade(suggested by @miken32) wasn't working for me, so I had to do it the hard way, by shutting down the service and using mysqld_safe, as explained here.
mysql_upgrade（由@miken32 建议）对我不起作用，所以我不得不通过关闭服务并使用mysqld_safe，如解释here以艰难的方式做到这一点。
UPDATE: Actually, that didn't work either, so I had to do the hard hard hard way (beware, this deletes all your databases):
更新：实际上，这也不起作用，所以我不得不采取艰难的方式（注意，这会删除您的所有数据库）：
sudo killall mysqldsudo rm -rf /var/lib/mysqlsudo apt-get purge mysql-server- Install
mysql-serverpackage again.
sudo killall mysqldsudo rm -rf /var/lib/mysqlsudo apt-get purge mysql-servermysql-server再次安装包。
回答by Ajeet Khan
I got the issue resolved from here. Run mysql_upgrade -u root -pto fix the issue or if you are facing this error while creating a user than try using "BY" instead of "WITH" in your query.
我从这里解决了问题。运行mysql_upgrade -u root -p以解决问题，或者如果您在创建用户时遇到此错误，请尝试在查询中使用“ BY”而不是“ WITH”。
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xyz';
回答by Lahcene AISSA
I added the following line to the my.ini file and it worked perfectly :
我将以下行添加到 my.ini 文件中，它运行良好：
[mysqld]
#Path to installation directory. All paths are usually resolved relative to this.
basedir = "C:/Program Files (x86)/EasyPHP-Devserver-17/eds-binaries/dbserver/mysql5717x86x191205223118/"
skip-grant-tables <=== added this line
Hope it helps,
希望能帮助到你，
cheers
干杯
回答by Andrew Poodle
I came across this issue on a 5.7.12 box that was replicating from a 5.6 box. The 5.6 box had been downgraded from 5.7 as it's part of a pre-prod test environment.
我在从 5.6 盒复制的 5.7.12 盒上遇到了这个问题。5.6 盒已从 5.7 降级，因为它是预生产测试环境的一部分。
As part of the downgrade process, the 5.6 box got it's permissions screwed (run mysql_upgrade --force on 5.6 after a downgrade solves it) but in the process it appears that some of the permissions changes issued on the 5.6 server were pushed to the 5.7 server.
作为降级过程的一部分，5.6 框的权限被搞砸了（在降级解决后在 5.6 上运行 mysql_upgrade --force）但在此过程中，似乎在 5.6 服务器上发布的一些权限更改被推送到 5.7服务器。
Ultimately, the authentication_string password hash ended up in the plugin column for root@localhost.
最终，authentication_string 密码哈希最终出现在 root@localhost 的插件列中。
Fixing moving the hash to the correct column and inserting the mysql_native_password into the 'plugin' column for that user cured the problem.
修复将哈希移动到正确的列并将 mysql_native_password 插入到该用户的“插件”列中解决了问题。
I'm now working on replicating the issue so I can submit a bug report, but there were a number of factors involved so it's proving hard to replicate.
我现在正在努力复制这个问题，以便我可以提交错误报告，但涉及到许多因素，因此证明很难复制。
