MySQL/Amazon RDS error: "you do not have SUPER privileges..."

user_702275 0
Viewed 122086

I'm attempting to copy my mysql database from an Amazon EC2 to an RDS:

I successfully did a mysqldump of my database into my root folder using this:

root@ip-xx-xx-xx-xx:~# mysqldump my_database -u my_username -p > my_database.sql

Then I tried to transfer this .sql file to my new RDS database:

root@ip-xx-xx-xx-xx:~# mysql my_database -u my_username -p -h  
my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql

Unfortunately, I get following error message:

You do not have the SUPER privilege and binary logging is enabled 
(you *might* want to use  the less safe log_bin_trust_function_creators variable)

I tried to GRANT SUPER.. in a variety of ways but I'm getting errors when I try to do that too. Typing mysql > FLUSH privileges; doesn't work either.

I'm a mysql beginner so sorry for such an easy question. Thoughts?

amazon-rds sql-grant amazon-web-services amazon-ec2 mysql
8 Answers

For me, there was only 2 commands in my dump file which required SUPER privileges:

  • SET @@GLOBAL.gtid_purged
  • SET @@SESSION.SQL_LOG_BIN

According to the mysqldump docs you can disable these with --set-gtid-purged=OFF.

Then looking at man mysqldump:

Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.

So I decided to add --set-gtid-purged=OFF to my mysqldump command and then I could successfully import the resulting dump file.

user_13934980

To complete @arun-r's answer, a reboot is necessary when you create a new parameter group.

enter image description here

user_656030

After used arun-r answer, if the problem is not solved you need to modify your dump file. It is simple.

In the dump file you will find lines like :

DELIMITER ;;
CREATE DEFINER=`username_from_dumped_database`@`host_from_dumped_database` PROCEDURE `procedure_or_function_name`()
BEGIN

You have to replace :

  • username_from_dumped_database by your username on rds database.
  • host_from_dumped_databse by %

I don't know why but this trick worked for me. A simple text editor is enough to do this.

user_55897680
Related