具有GRANT ALL PRIVILEGES的用户的CREATE命令被拒绝 [英] CREATE command denied for user that has GRANT ALL PRIVILEGES

问题描述

我正在尝试设置MySQL复制从属服务器，并且在运行LOAD DATA FROM MASTER;时遇到了非常困难的时光.是的，我知道它已被弃用，但是我正在运行MySQL 5.1，目前这不是我的问题.

I am trying to set up a MySQL replication slave, and am having a very difficult time running LOAD DATA FROM MASTER;. Yes, I know it is deprecated, but I am running MySQL 5.1, and that isn't my problem at the moment.

由于某种原因，MySQL一直告诉我CREATE命令被拒绝，但是对SHOW GRANTS的检查则相反.检查一下:

For some reason MySQL keeps telling me the CREATE command is denied, but a check of SHOW GRANTS says otherwise. Check this out:

mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA FROM MASTER;
ERROR 1142 (42000): CREATE command denied to user 'replicator'@'localhost' for table 'aggregate'
mysql>

在这里我觉得很奇怪的是，在调用LOAD DATA FROM MASTER时，它认为我是'replicator'@'localhost'，而SHOW GRANTS却说'replicator'@'%'.为了安全起见，我也为'replicator'@'localhost'赋予了相同的特权.

What I thought was odd here is that when calling LOAD DATA FROM MASTER, it thinks I am 'replicator'@'localhost', yet SHOW GRANTS says 'replicator'@'%'. Just to be safe, I gave the same privs to 'replicator'@'localhost' as well.

mysql> SHOW GRANTS FOR 'replicator'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'localhost' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

那么，为什么这一切都弄乱了呢?是的，我也做了很多次FLUSH PRIVILEGES.

So, any thoughts on why this is all messed up? Yes, I did FLUSH PRIVILEGES many times as well.

在此先感谢您提供的任何建议.

Thanks in advance for any advice you can give.

推荐答案

我想提出以下建议:

下次登录时，运行以下查询:

When you login next time run this query:

SELECT USER(),CURRENT_USER();

如果看到两个不同的用户名，则说明情况很奇怪

If you see two different usernames, you have a weird scenario

您可以在《 MySQL 5.0认证研究指南》(ISBN 0-672-32812-7)的第34或35章中找到

This you can find in the MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), Chapter 34 or 35

USER()呼应您尝试登录的身份

USER() echoes what you attempted to login as

CURRENT_USER()呼应mysql允许您登录的内容.

CURRENT_USER() echos what mysql ALLOWED YOU to login as.

尝试使用127.0.0.1作为复制器进行连接，并运行相同的查询.

Try connecting using as replicator using 127.0.0.1 and run the same query.

您可能还想确保正在使用的mysql版本的mysql.user中存在所有必要的列.如果您没有升级mysql.user表(从MySQL 4-MySQL 5或MySQL 5.0迁移到5.1)，则mysql.user中的列可能不同步.

You may also want to maker sure all necessary columns are present in mysql.user for the version of mysql you are using. If you did not upgrade the mysql.user table (from a migration from MySQL 4 - MySQL 5, or MySQL 5.0 to 5.1) columns in mysql.user could be out of sync.

这篇关于具有GRANT ALL PRIVILEGES的用户的CREATE命令被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！