MySQL 拒绝访问; 您需要（至少其中之一）此操作的 SUPER 权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44015692/
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; you need (at least one of) the SUPER privilege(s) for this operation
提问by kenpeter
So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G
所以我尝试将 sql 文件导入 rds（1G MEM，1 CPU）。sql文件好像是1.4G
mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db < db.sql
mysql -h xxxx.rds.amazonaws.com -u 用户 -ppass --max-allowed-packet=33554432 db < db.sql
It got stuck at:
它卡在：
ERROR 1227 (42000) at line 374: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
The actual sql content is:
实际的sql内容是：
/*!50003 CREATE*/ /*!50017 DEFINER=`another_user`@`1.2.3.4`*/ /*!50003 TRIGGER `change_log_BINS` BEFORE INSERT ON `change_log` FOR EACH ROW
IF (NEW.created_at IS NULL OR NEW.created_at = '00-00-00 00:00:00' OR NEW.created_at = '') THEN
SET NEW.created_at = NOW();
END IF */;;
another_useris not existed in rds, so I do:
another_userrds 中不存在，所以我这样做：
GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';
Still no luck.
仍然没有运气。
回答by hjpotter92
Either remove the DEFINER=..statement from your sqldump file, or replace the user values with CURRENT_USER.
DEFINER=..从 sqldump 文件中删除该语句，或将用户值替换为CURRENT_USER.
The MySQL server provided by RDS does not allow a DEFINERsyntax for another user (in my experience).
RDS 提供的 MySQL 服务器不允许DEFINER其他用户使用语法（以我的经验）。
You can use a sedscript to remove them from the file:
您可以使用sed脚本将它们从文件中删除：
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
回答by Jeremy Jones
If your dump file doesn't have DEFINER, make sure these lines below are also removed if they're there, or commented-out with --:
如果您的转储文件没有DEFINER，请确保也删除下面的这些行（如果它们在那里），或者用 注释掉--：
At the start:
在开始时：
-- SET @@SESSION.SQL_LOG_BIN= 0;
-- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
At the end:
在末尾：
-- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
回答by marcel
Just a MacOS extra update for hjpotter92 answer.
只是 hjpotter92 答案的 MacOS 额外更新。
To make sedrecognize the pattern in MacOS, you'll have to add a backslash before the =sign, like this:
要sed识别 MacOS 中的模式，您必须在符号前添加反斜杠=，如下所示：
sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' file.sql
回答by quimm2003
Another useful trick is to invoke mysqldump with the option --set-gtid-purged=OFF which does not write the following lines to the output file:
另一个有用的技巧是使用选项 --set-gtid-purged=OFF 调用 mysqldump，它不会将以下行写入输出文件：
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
not sure about the DEFINER one.
不确定 DEFINER 一个。
回答by avivamg
Problem: You're trying to import data (using mysqldump file) to your mysql database ,but it seems you don't have permission to perform that operation.
问题：您正在尝试将数据（使用 mysqldump 文件）导入您的 mysql 数据库，但您似乎没有执行该操作的权限。
Solution: Assuming you data is migrated ,seeded and updated in your mysql database, take snapshot using mysqldump and export it to file
解决方案：假设您的 mysql 数据库中的数据已迁移、播种和更新，请使用 mysqldump 拍摄快照并将其导出到文件
mysqldump -u [username] -p [databaseName] --set-gtid-purged=OFF > [filename].sql
From mysql documentation:
GTID- A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.
--set-gtid-purged=OFFSET @@GLOBAL.gtid_purged is not added to the output, and SET @@SESSION.sql_log_bin=0 is not added to the output. For a server where GTIDs are not in use, use this option or AUTO. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.
从 mysql 文档：
GTID- 全局事务标识符 (GTID) 是在源服务器（主服务器）上创建并与提交的每个事务相关联的唯一标识符。此标识符不仅对于它起源的服务器是唯一的，而且在给定复制设置中的所有服务器中都是唯一的。所有事务和所有 GTID 之间存在一对一的映射。
--set-gtid-purged=OFFSET @@GLOBAL.gtid_purged 没有添加到输出中，SET @@SESSION.sql_log_bin=0 没有添加到输出中。对于未使用 GTID 的服务器，请使用此选项或 AUTO。如果您确定目标服务器上的 gtid_purged 中已经存在所需的 GTID 集并且不应更改，或者您计划手动识别和添加任何丢失的 GTID，则仅将此选项用于正在使用 GTID 的服务器。
Afterwards connect to your mysql with user root ,give permissions , flush them ,and verify that your user privileges were updated correctly.
然后使用 root 用户连接到您的 mysql，授予权限，刷新它们，并验证您的用户权限是否已正确更新。
mysql -u root -p
UPDATE mysql.user SET Super_Priv='Y' WHERE user='johnDoe' AND host='%';
FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'johnDoe';
+------------------------------------------------------------------+
| Grants for johnDoe |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `johnDoe` |
| GRANT ALL PRIVILEGES ON `db1`.* TO `johnDoe` |
+------------------------------------------------------------------+
now reload the data and the operation should be permitted.
现在重新加载数据，应该允许操作。
mysql -h [host] -u [user] -p[pass] [db_name] < [mysql_dump_name].sql
回答by Mike Dubs
* Answer may only be applicable to MacOS *
* 答案可能仅适用于 MacOS *
When trying to import a .sql file into a docker container, I encountered the error message:
尝试将 .sql 文件导入 docker 容器时，遇到错误消息：
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
拒绝访问; 您需要（至少其中之一）此操作的 SUPER 权限
Then while trying some of the other suggestions, I received the below error on my MacOS (osx)
然后在尝试其他一些建议时，我在 MacOS (osx) 上收到以下错误
sed: RE error: illegal byte sequence
sed: RE 错误: 非法字节序列
Finally, the following command from this resourceresolved my "Access Denied" issue.
最后，来自该资源的以下命令解决了我的“拒绝访问”问题。
LC_ALL=C sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' fileName.sql
So I could import into the docker database with:
所以我可以使用以下命令导入 docker 数据库：
docker exec -i dockerContainerName mysql -uuser -ppassword table < importFile.sql
Hope this helps! :)
希望这可以帮助！:)
回答by Harsh Manvar
Statement
陈述
DEFINER=
username@`%
定义者=
username@`%
is an issue in your backup dump.
是备份转储中的问题。
The solution that you can work around is to remove all the entry from sql dump file and import data from GCP console.
您可以解决的解决方案是从 sql 转储文件中删除所有条目并从 GCP 控制台导入数据。
cat DUMP_FILE_NAME.sql | sed -e 's/DEFINER=
<username>@%//g' > NEW-CLEANED-DUMP.sql
cat DUMP_FILE_NAME.sql | sed -e 's/DEFINER=
<username>@%//g' > NEW-CLEANED-DUMP.sql
Try importing new file(NEW-CLEANED-DUMP.sql).
尝试导入新文件（NEW-CLEANED-DUMP.sql）。
回答by Tengerye
I commented all the lines start with SETin the *.sqlfile and it worked.
我评论SET了*.sql文件中所有以开头的行并且它有效。
