所以我尝试将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';
还是不走运。
问题:你试图导入数据(使用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
