MySQL 尝试授予权限时，用户 'root'@'localhost' 的访问被拒绝。如何授予权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8484722/
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
Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
提问by Steven Scotten
I've looked at a number of similar questions and so I'm demonstrating that I've checked the basics. Though of course, that doesn't mean I haven't missed something totally obvious. :-)
我看过一些类似的问题，所以我证明我已经检查了基础知识。当然，这并不意味着我没有遗漏一些非常明显的东西。:-)
My question is: why am I denied access on a user with the privileges to do what I'm trying to do and where I have already typed the password and been granted access? (For the sake of completeness, I tried typing the wrong password just to make sure that MySQL client would deny me access at program start.)
我的问题是：为什么我拒绝访问有权执行我正在尝试执行的操作以及我已经输入密码并获得访问权限的用户？（为了完整起见，我尝试输入错误的密码只是为了确保 MySQL 客户端会在程序启动时拒绝我访问。）
Background:
背景：
Logged in to the shell of the machine running the MySQL server via ssh, I log in as root:
通过 ssh 登录到运行 MySQL 服务器的机器的 shell，我以 root 身份登录：
[myname@host ~]$ mysql -u root -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62396
Server version: 5.5.18-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Awesome. My reading of the answers to similar questions suggests that I should make sure the the privileges are current with what is in the grant tables
惊人的。我对类似问题的答案的阅读表明我应该确保权限与授权表中的内容是最新的
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
Next make sure I am who I think I am:
接下来确保我是我认为的我：
mysql> SELECT user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
...and really reallymake sure:
...并且真的要确保：
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
So far so good. Now what privileges do I have?
到现在为止还挺好。现在我有什么特权？
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[OBSCURED]' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Now that's a little hard to read, so lets try this way (you will also get to see that there is a non-localhost 'root' user):
现在这有点难以阅读，所以让我们尝试这种方式（您还将看到有一个非本地主机“root”用户）：
mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password: *[OBSCURED]
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*************************** 2. row ***************************
Host: [HOSTNAME].com
User: root
Password: *[OBSCURED]
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)
Awesome! MySQL thinks that I am root@localhost and root@localhost has all those privileges. That means I ought to be able to do what I want, right?
惊人的！MySQL 认为我是 root@localhost 并且 root@localhost 拥有所有这些特权。这意味着我应该能够做我想做的事，对吧？
mysql> GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
How could I have screwed up something this basic?
我怎么能搞砸这么基本的东西？
Side note: for anyone who wants to suggest that I not have a user named root with all privileges, that's great and something I'll consider doing once I can give another user some privileges.
旁注：对于任何想要建议我没有一个拥有所有权限的名为 root 的用户的人来说，这很好，一旦我可以给另一个用户一些权限，我就会考虑这样做。
Thank you!
谢谢！
采纳答案by Marc Alff
Notice how the output of
注意输出如何
SHOW GRANTS FOR 'root'@'localhost';
did not say 'ALL PRIVILEGES' but had to spell out what root@localhost has.
没有说“所有特权”，但必须说明 root@localhost 有什么。
GRANT ALL PRIVILEGES will fail, because a user can not grant what he/she does not have, and the server seem to think something is not here ...
GRANT ALL PRIVILEGES 将失败，因为用户无法授予他/她没有的东西，并且服务器似乎认为某些东西不在这里......
Now, what's missing then ?
现在，还缺少什么？
On my system, I get this:
在我的系统上，我得到了这个：
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y <----------------------------- new column in 5.5
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: <------------------------------- new column in 5.5
authentication_string: <------------------------------- new column in 5.5
1 row in set (0.00 sec)
There are also new tables in 5.5, such as mysql.proxies_user: make sure you have them.
5.5中也有新表，比如mysql.proxies_user：确保你有它们。
When installing a brand new mysql server instance, the install script will create all the mysql.* tables with the proper structure.
安装全新的 mysql 服务器实例时，安装脚本将创建所有具有正确结构的 mysql.* 表。
When upgrading from an old version, make sure the proper upgrade procedure (mysql_upgrade) is used, which will add the missing tables / columns.
从旧版本升级时，请确保使用正确的升级过程 (mysql_upgrade)，这将添加缺少的表/列。
It is only a guess, but it seems mysql_upgrade was not done for this instance, causing the behavior seen.
这只是一个猜测，但似乎 mysql_upgrade 没有为此实例完成，导致了所见的行为。
回答by Aryo
I also had the same problem with this but on Windows after upgrading to MySQL 5.5 from MySQL 5.1. I already tried changing, creating, and resetting password mentioned in here, here, here, and here, no clue. I still get the same error:
我也遇到了同样的问题，但在从 MySQL 5.1 升级到 MySQL 5.5 后在 Windows 上。我已经尝试过更改、创建和重置此处、此处、此处和此处提到的密码，没有任何线索。我仍然收到相同的错误：
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I'm able to connect normally, show all databases, do selects and inserts, create and add users, and but when it comes to GRANT, I'm screwed up. Those access denied error shows up again.
我能够正常连接，显示所有数据库，选择和插入，创建和添加用户，但是当涉及到 GRANT 时，我搞砸了。那些访问被拒绝的错误再次出现。
I managed to solve this problem by fixing the privileges by the following command on the MySQL server bin/ directory as mentioned in here:
我设法通过作为中提到的MySQL服务器bin /目录下面的命令固定的特权来解决这个问题在这里：
C:\MySQL Server 5.5\bin> mysql_upgrade
Then, the problem gone away. I hope this solution works on Linux too since usually MySQL provide the same command both on Linux and Windows.
然后，问题就迎刃而解了。我希望这个解决方案也适用于 Linux，因为通常 MySQL 在 Linux 和 Windows 上都提供相同的命令。
回答by Anuj Gupta
This might happen when you attempt to grant all privileges on all tables to another user, because the mysql.users table is considered off-limits for a user other than root.
当您尝试将所有表的所有权限授予另一个用户时，可能会发生这种情况，因为 mysql.users 表被认为是非 root 用户的禁止访问。
The following however, should work:
但是，以下应该有效：
GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;
Note that we use `%`.* instead of *.*
请注意，我们使用 `%`.* 而不是 *.*
回答by phil_w
This happened to me when I tried to install a higher MySQL version than the one coming with the distribution.
当我尝试安装比发行版附带的 MySQL 版本更高的 MySQL 版本时，这发生在我身上。
I erased the old version then installed the new one (rpm -e ... then rpm -i MySQL-server* ) But did not realize that the files in /var/lib/mysql were still from the older version (with differences as explained by Marc Alff - thanks!)
我删除了旧版本然后安装了新版本（rpm -e ...然后 rpm -i MySQL-server* ）但没有意识到 /var/lib/mysql 中的文件仍然来自旧版本（与由 Marc Alff 解释 - 谢谢！）
I could have done an mysql_upgrade, but as I wanted to start from scratch I did:
我本来可以做一个 mysql_upgrade，但是因为我想从头开始，所以我做了：
# su - mysql
$ rm -rf /var/lib/mysql/*
$ mysql_install_db
# /etc/init.d/mysql start
Then set root password (/usr/bin/mysqladmin -u root password), and all worked as expected with the GRANT commands...
然后设置 root 密码（/usr/bin/mysqladmin -u root 密码），一切都按预期使用 GRANT 命令...
回答by tzp
I had the same problem, i.e. all privileges granted for root:
我遇到了同样的问题，即授予 root 的所有权限：
SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*[blabla]' WITH GRANT OPTION
...but still not allowed to create a table:
...但仍然不允许创建表：
create table t3(id int, txt varchar(50), primary key(id));
ERROR 1142 (42000): CREATE command denied to user 'root'@'localhost' for table 't3'
Well, it was cause by an annoying user error, i.e. I didn't select a database. After issuing USE dbnameit worked fine.
嗯，这是由一个恼人的用户错误引起的，即我没有选择数据库。发出USE dbname 后，它工作正常。
回答by Mithun Sasidharan
Basically this error comes when you have not specified a password, it means that you have an incorrect password listed in some option file.
基本上，当您没有指定密码时会出现此错误，这意味着您在某些选项文件中列出了不正确的密码。
Read this DOCon understanding how to assign and manage Passwords to accounts.
阅读此文档以了解如何为帐户分配和管理密码。
Also , Check if the permission on the folder /var/lib/mysql/mysqlis 711 or not.
另外，检查文件夹的权限/var/lib/mysql/mysql是否为 711。
回答by William Turrell
On Debian (Wheezy, 7.8) with MySQL 5.5.40, I found SELECT * FROM mysql.user WHERE User='root'\Gshowed the Event_privand 'Trigger_priv` fields werepresent but notset to Y.
在Debian（喘息，7.8）与MySQL 40年5月5日，我发现SELECT * FROM mysql.user WHERE User='root'\G表明，Event_priv与“Trigger_priv`领域都存在，但不能设置为Y.
Running mysql_upgrade(with or without --force) made no difference; I needed to do a manual:
运行mysql_upgrade（有或没有--force）没有区别；我需要做一个手册：
update user set Event_priv = 'Y',Trigger_priv = 'Y' where user = 'root'
update user set Event_priv = 'Y',Trigger_priv = 'Y' where user = 'root'
Then finally I could use:
然后最后我可以使用：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION
…and then use it more precisely on an individual database/user account.
…然后在单个数据库/用户帐户上更精确地使用它。
回答by gloriphobia
You may have come to this question with MySQL version 8 installed (like me) and not found a satisfactory answer. You can no longer create users like this in version 8:
您可能在安装了 MySQL 版本 8 的情况下（像我一样）遇到了这个问题，但没有找到满意的答案。您不能再在版本 8 中创建这样的用户：
GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
The rather confusing error message that you get back is: ERROR 1410 (42000): You are not allowed to create a user with GRANT
您返回的相当混乱的错误消息是： ERROR 1410 (42000): You are not allowed to create a user with GRANT
In order to create users in version 8 you have to do it in two steps:
为了在版本 8 中创建用户，您必须分两步完成：
CREATE USER 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]';
GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' WITH GRANT OPTION;
Of course, if you prefer, you can also supply a limited number of privileges (instead of GRANT ALL PRIVILEGES), e.g. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
当然，如果您愿意，您也可以提供有限数量的权限（而不是GRANT ALL PRIVILEGES），例如GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
回答by Nav
Typing SHOW GRANTS FOR 'root'@'localhost';showed me some obscured password, so I logged into mysql of that system using HeidiSQL on another system (using rootas the username and the corresponding password) and typedGRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;
输入SHOW GRANTS FOR 'root'@'localhost';显示了一些模糊的密码，所以我在另一个系统上使用 HeidiSQL 登录到该系统的 mysql（用作用root户名和相应的密码）并输入GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;
and it worked when I went back to the system and logged on usingmysql -uroot -pthepassword;
当我回到系统并使用登录时它起作用了mysql -uroot -pthepassword;
回答by pevik
I run at this when I tried to add privileges to performance_schema, which is mysql bug http://bugs.mysql.com/bug.php?id=44898(workaround to add --single-transaction).
当我尝试向 performance_schema 添加权限时，我运行了这个，这是 mysql 错误http://bugs.mysql.com/bug.php?id=44898（添加 --single-transaction 的解决方法）。
