master user of AWS RDS does not have privileges to import data, nor to grant the required privileges

Question

I have made a new AWS RDS MySQL instance and I want to import some existing .sql data generated from an existing database using MySQL Workbench's export tool.

I connected to the database using the "admin" master user, then started the import.

However, I'm getting this error: ERROR 1227 (42000) at line 20: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I tried granting SUPER to the master user, and also tried creating a new user with SUPER, but got this error: Error changing account admin@%%: Missing GRANT OPTION privilege, even though the master user "admin" does have the GRANT OPTION privilege.

How can I make this work?

Answer 1

gyanendra giri was partially right, but his answer didn't help with MySQL Workbench. Here's how I solved it eventually:

Select Export to Self-Contained File, and make sure that Create Dump in a Single Transaction is not selected.

Then, click on Advanced Options...:

Set set-gtid-purged to "OFF":

Answer 2

You may have a definer line for a user which doesn't exist for current RDS database. You have something like /*!50017 DEFINER=`root`@`localhost`*/ at line 20. You may have to remove that part.

To remove:

sed -i -e 's/DEFINER=`root`@`localhost`//g' db_dump_name.sql

Or replace that user to an existing user. In your case may be admin

To find and replace:

sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`admin`@`%`/g' db_dump_name.sql

reference