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

Mysql -h xx.rds.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';

还是不走运。


当前回答

在我的情况下(试图执行一个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' ...

其他回答

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

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

解决方案:假设你的数据迁移,种子和更新在你的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=OFF SET @@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.

然后用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额外更新hjpotter92的答案。

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

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

当我们创建一个新的RDS DB实例时,默认的主用户不是根用户。但仅获得该DB实例的某些特权。该权限不包括SET权限。现在,如果你的默认主用户试图执行mysql SET命令,那么你将面临这个错误:访问被拒绝;您需要(至少一个)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';

然后我就能还原了。