MySQL 错误 1044 (42000)：“root”的所有权限访问被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21714869/
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 1044 (42000): Access denied for 'root' With All Privileges
提问by latvian
I have strange error. I am logged in local Mysql as root via command line. After creating database:
我有奇怪的错误。我通过命令行以 root 身份登录本地 Mysql。创建数据库后：
create database some_db;
Then giving privileges to some user:
然后授予某些用户权限：
grant all privileges on some_db.* to some_user@'localhost' identified by 'password';
This is giving error:
这是给出错误：
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'some_db'
The permissions for the root(show grants;) shows:
root(show grants;) 的权限显示：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8919C53DC7A4DFBF3F8584382E96463583EB7FDA'
I am also making sure i am logged in as root:
我还确保我以 root 身份登录：
select current_user();
And this confirms that i am logged in as 'root'@'localhost'
这证实了我以 'root'@'localhost' 身份登录
I have created database and assigned user multiple times and never had an issue. Why i am unable assign user to the database created by root while logged in as root?
我创建了数据库并多次分配用户，但从未遇到过问题。为什么我无法在以 root 身份登录时将用户分配给由 root 创建的数据库？
p.s. from other posts, i am thinking the issue may be caused due to some strange users
ps来自其他帖子，我想这个问题可能是由于一些奇怪的用户引起的
select host, user from mysql.user;
Some users that i have not added but does show up:
一些我没有添加但确实出现的用户：
MY_COMPuTER_name.local | ''
MY_COMPuTER_name.local | root
I tried to delete these users
我试图删除这些用户
drop user 'root'@'MY_COMPuTER_name.local';
drop user ''@'MY_COMPuTER_name.local';
However, while it states query run successful, the users are not dropped even after flush privileges. Why i am unable delete users? Any help is much appricated
然而，虽然它声明查询运行成功，但即使在刷新权限之后用户也不会被删除。为什么我无法删除用户？任何帮助都非常有用
回答by Amarnath Balasubramanian
First, Identify the user you are logged in as:
首先，确定您登录的用户：
select user();
select current_user();
The result for the first command is what you attempted to login as, the second is what you actually connected as. Confirm that you are logged in as root@localhostin mysql.
第一个命令的结果是您尝试登录的身份，第二个是您实际连接的身份。确认您root@localhost以 mysql 的身份登录。
Grant_privto root@localhost. Here is how you can check.
Grant_priv到root@localhost。这是您可以检查的方法。
mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+-------------------------------------------+------------+------------+
| host | user | password | Grant_priv | Super_priv |
+-----------+------------------+-------------------------------------------+------------+------------+
| localhost | root | ***************************************** | N | Y |
| localhost | debian-sys-maint | ***************************************** | Y | Y |
| localhost | staging | ***************************************** | N | N |
+-----------+------------------+-------------------------------------------+------------+------------+
You can see that the Grant_priv is set to N for root@localhost. This needs to be Y. Below is how to fixed this:
您可以看到 root@localhost 的 Grant_priv 设置为 N。这需要是 Y。以下是解决此问题的方法：
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';
I logged back in, it was fine.
我重新登录，没问题。
回答by Ashwin
If you get an error 1044 (42000) when you try to run SQL commands in MySQL (which installed along XAMPP server) cmd prompt, then here's the solution:
如果在 MySQL（随 XAMPP 服务器一起安装）cmd 提示符下尝试运行 SQL 命令时出现错误 1044 (42000)，则解决方法如下：
Close your MySQL command prompt.
Open your cmd prompt (from Start menu -> run -> cmd) which will show: C:\Users\User>_
Go to MySQL.exe by Typing the following commands:
关闭 MySQL 命令提示符。
打开您的 cmd 提示符（从开始菜单 -> 运行 -> cmd），它将显示：C:\Users\User>_
通过键入以下命令转到 MySQL.exe：
C:\Users\User>cd\
C:\>cd xampp
C:\xampp>cd mysql
C:\xxampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -u root
C:\Users\User>cd\
C:\>cd xampp
C:\xampp>cd mysql
C:\xxampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -u root
Now try creating a new database by typing:
mysql> create database employee;if it shows:
Query OK, 1 row affected (0.00 sec) mysql>Then congrats ! You are good to go...
现在尝试通过键入以下内容来创建一个新数据库：
mysql> create database employee;如果它显示：
Query OK, 1 row affected (0.00 sec) mysql>那么恭喜！你已准备好出发...
回答by latvian
The reason i could not delete some of the users via 'drop' statement was that there is a bug in Mysql http://bugs.mysql.com/bug.php?id=62255with hostname containing upper case letters. The solution was running following query:
我无法通过“drop”语句删除某些用户的原因是 Mysql http://bugs.mysql.com/bug.php?id=62255中存在一个错误，主机名包含大写字母。该解决方案正在运行以下查询：
DELETE FROM mysql.user where host='Some_Host_With_UpperCase_Letters';
I am still trying to figure the other issue where the root user with all permissions are unable to grant privileges to new user for particular database
我仍在尝试解决另一个问题，即具有所有权限的 root 用户无法向特定数据库的新用户授予权限
