Granting privileges for Importing data to AWS Lightsail MySQL

Question

I have a dump main.sql which I dumped from a database from inside lightsail.

I have then created a new database in another lightsail instance and I am trying to import the dump main.sql into that database. I have turned on Import mode and Public mode.

I login in mysql workbench with the credentials provided.
While trying to  import, I get this error.
"D:\main.sql"
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

I try granting access to the user dbmasteruser with those privileges with following command
GRANT SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'dbmasteruser'@'%';

but get this following error
Error Code: 1045. Access denied for user 'dbmasteruser'@'%' (using password: YES)

I now believe that the user doesn't have the specific grants which should be given if aws has provided me with that user.

How can I provide all the grants needed to that user given to me by aws itself? I have too much of dependency on that dump which needs to be imported as it is.

Answer

Hello.

Were there any warnings or errors when creating a dump file from the database?  
What often happens is that code related to GTID is included in the obtained dump file, resulting in an error.  
If you open the dump file and find a line with a command like the one below, try deleting it.  
```
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
```
Alternatively, try using "--set-gtid-purged=OFF" as a command option when obtaining the dump file.

Answer

Are you using the admin user to grant those privileges? Did you run `FLUSH PRIVILEGES` after updating and granting access?

Granting privileges for Importing data to AWS Lightsail MySQL

Relevant content