拒绝访问;您需要(至少其中之一)此操作的 SUPER 权限
所以我尝试将 sql 文件导入 rds(1G MEM，1 CPU).sql文件好像是1.4G
So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G
mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db <数据库.sql
mysql -h xxxx.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 中不存在，所以我这样做:
another_user is not existed in rds, so I do:
GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';
仍然没有运气.
推荐答案
从 sqldump 文件中删除 DEFINER=.. 语句，或用 CURRENT_USER 替换用户值>.
Either remove the DEFINER=.. statement from your sqldump file, or replace the user values with CURRENT_USER.
RDS 提供的 MySQL 服务器不允许其他用户使用 DEFINER 语法(以我的经验).
The MySQL server provided by RDS does not allow a DEFINER syntax for another user (in my experience).
您可以使用 sed 脚本将它们从文件中删除:
You can use a sed script to remove them from the file:
sed 's/sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
相关文章