拒绝访问;您需要(至少一个)SUPER特权来执行此操作

所以我尝试将sql文件导入rds (1G MEM, 1个CPU)。sql文件大约是1.4G

Mysql -h xxxx.rs.amazonaws.com -u user -ppass——max-allowed-packet=33554432 db <db.sql

它被卡在:

ERROR 1227 (42000) at line 374: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

实际的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_user在rds中不存在，所以我这样做:

GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';

还是不走运。

317062 次浏览
小开
最佳答案

要么从sqldump文件中删除DEFINER=..语句，要么用CURRENT_USER替换用户值。

RDS提供的MySQL服务器不允许另一个用户使用DEFINER语法(以我的经验)。

你可以使用sed脚本从文件中删除它们:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
小开

只是一个MacOS额外更新hjpotter92的答案。

为了让sed在MacOS中识别模式，你必须在=符号之前添加一个反斜杠，就像这样:

sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' file.sql
小开

问题

下面的语句或行转储文件创建问题

定义者= username@ ' %

简单的解决方案

你可以绕过的解决方案是删除SQL转储文件中的所有条目，并从GCP控制台. dump文件中导入数据。

 cat DUMP_FILE_NAME.sql |  sed -e 's/DEFINER=`<username>`@`%`//g' > NEW-CLEANED-DUMP.sql

上面的命令将帮助从转储文件中删除所有这些行，并创建不带定义者的新转储文件。

尝试导入新文件 (NEW-CLEANED-DUMP.sql)。

如果你在AWS RDS

你可能会看到问题，如果你的转储文件较大，你可以检查前20行使用

head -30 filename

看到输出后，查找行和行号

SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

我们将按行号删除这些行，例如17、18、24行号

sed -e '24d;17d;18d' file-name.sql > removed-line-file-name.sql
小开

我注释了*.sql文件中所有以SET开头的行，它工作了。

小开

如果你的转储文件没有DEFINER，确保下面的这些行也被删除(如果它们存在的话)，或者用-- 注释掉:

一开始:

-- SET @@SESSION.SQL_LOG_BIN= 0;
-- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';

最后:

-- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

注意注释字符是"破折号空格"包括空间。

更好的解决方案是通过在mysqldump命令中包含选项--set-gtid-purged=OFF来完全停止将这些行写入转储文件。

小开

另一个有用的技巧是使用选项--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;

我不确定DEFINER。

小开

问题:你试图导入数据(使用mysqldump文件)到你的mysql数据库，但似乎你没有权限执行该操作。

解决方案:假设你的数据迁移，种子和更新在你的mysql数据库，采取快照使用mysqldump和导出到文件

mysqldump -u [username] -p [databaseName] --set-gtid-purged=OFF > [filename].sql

来自mysql文档:

GTID -全局事务标识符(GTID)是创建的唯一标识符 并与原始服务器上提交的每个事务相关联 (主)。该标识符不仅对其所在的服务器惟一 起源，但在给定复制中的所有服务器中是唯一的 设置。所有事务和所有事务之间是1对1的映射 GTIDs . < / p >

——set-gtid-purged =了 SET @@GLOBAL。gtid_purged没有被添加到输出中，SET @@SESSION。说明回显中不添加Sql_log_bin =0。对于以下服务器 未使用gtid，请使用此选项或AUTO。只使用此选项 对于正在使用gtid的服务器，如果您确定这是必需的 GTID集已经存在于目标服务器上的gtid_purged中 不应更改，或者是否计划识别并添加任何缺失 手动GTIDs。< / p >

然后用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`                     |
+------------------------------------------------------------------+

现在重新加载数据和这个操作应该被允许。

mysql -h [host] -u [user] -p[pass] [db_name] < [mysql_dump_name].sql
小开

*答案可能只适用于MacOS *

当尝试将.sql文件导入docker容器时，我遇到了错误消息:

< p >拒绝访问;您需要(至少一个)SUPER特权 这个操作< / p >

然后，在尝试其他一些建议时，我在MacOS (osx)上收到了以下错误

sed: RE错误:非法字节序列

最后，这个资源中的以下命令解决了我的“访问被拒绝”问题。

LC_ALL=C sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' fileName.sql

所以我可以导入到docker数据库:

docker exec -i dockerContainerName mysql -uuser -ppassword table < importFile.sql

希望这能有所帮助!:)

小开

要导入.sql.gz格式的数据库文件，请删除定义器并使用以下命令导入

zcat path_to_db_to_import.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db_name

  1. 之前，使用下面的命令导出。sql.gz格式的数据库。

    mysqldump -u user -p old_db | gzip -9 > path_to_db_exported.sql.gz; < / p >

  2. 导入导出的数据库并使用以下命令删除定义器，

    zcat path_to_db_exported.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db < / p >

小开

当您恢复备份时，请确保对旧备份和新备份尝试使用相同的用户名。

小开

完整的解决方案

以上所有的解决方案都是好的。这里我要把所有的解结合起来这样它就适用于所有的情况。

  1. 固定的定义者

Linux和Mac

sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' file.sql
< p > 对于Windows
下载atom或notepad++，用atom或notepad++打开转储sql文件，按Ctrl+F
搜索单词定义者，并从所有地方删除行定义者= __ABC0@%(或者对你来说可能略有不同)并保存文件。
例如
创建定义器= __abc0@__abc1过程__abc2
创建过程__abc0

  1. 删除3行 从转储文件中删除所有这3行。您可以使用sed命令或在Atom编辑器中打开文件并搜索每一行，然后删除该行。
    示例:打开Dump2020。sql在Atom，按ctrl+F，搜索设置@@SESSION。SQL_LOG_BIN = 0，删除该行 
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
  1. 生成的文件有问题 如果您生成的转储文件可能会遇到一些问题。SQL文件不正确。但在这里，我不打算解释如何生成转储文件。但是你可以问我(＿)
小开

需要“开启”;服务器参数log_bin_trust_function_creator "在服务器端。这一个你可以很容易地找到左侧刀片，如果它是蔚蓝玛丽亚db。

小开

当我们创建一个新的RDS DB实例时，默认的主用户不是根用户。但仅获得该DB实例的某些特权。此权限不包括集权限。现在，如果您的默认主用户试图执行mysql 集命令，那么您将面临这个错误:拒绝访问;您需要(至少一个)SUPER或system_variable_admin权限来执行此操作

解决方案1

注释掉或删除这些行

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 1;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';

解决方案2

您还可以使用-f选项来加载转储文件的其余部分，从而忽略这些错误。

mysql -f <REPLACE_DB_NAME> -u <REPLACE_DB_USER> -h <DB_HOST_HERE> -p < dumpfile.sql
小开

如果它有帮助，当我试图在我的AWS MySQL RDS上恢复一个DB转储时，我得到了这个错误:

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER,
SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

我不需要更改DEFINER或删除/注释行。我刚刚做了:

GRANT SESSION_VARIABLES_ADMIN ON *.* TO myuser@'myhost';
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO myuser@'myhost';

然后我就能还原了。

小开

转储中的问题。

请尝试通过以下方式转储:

mysqldump -h databasehost --user=databaseusername --password --single-transaction databasename  | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip > /tmp/database.sql.gz

然后，尝试按以下方式导入:

zcat /tmp/database.sql.gz | mysql -h database_host -u username -p databasename
小开

以上的方法对我来说都没用。我必须做到以下几点:

  • 在mysqldump中使用以下标志:

    mysqldump --databases <db1> <db2> --master-data=1 --single-transaction --order-
by-primary --foce -r all.sql -h<host> -u<user> -p<password>

  • 删除如下行:

    CHANGE MASTER TO MASTER_LOG_FILE='binlog.....
    • 在我的文件中，这是第22行，所以我运行:sed -i '22d' all.sql

  • 将数据导入RDS:

    mysql -h<host> -u<user> -p<password>
$ source all.sql
小开

在我的情况下(试图执行一个SQL文件到AWS RDS)，我的SQL语句的开头看起来像这样:

DROP VIEW IF EXISTS `something_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `something_view`...

我所要做的就是删除上面语句的ALGORITHM=UNDEFINED DEFINER='root'@'%' SQL SECURITY DEFINER部分来修复它。

所以新的语句是这样的:

CREATE VIEW 'something_view' ...

提交答案