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

tim peterson picture tim peterson · Jul 22, 2012 · Viewed 90.9k times · Source

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?

Answer

arun-r picture arun-r · Jun 16, 2015
  1. Open the RDS web console.
  2. Open the “Parameter Groups” tab.
  3. Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm. Select the just created Parameter Group and issue “Edit Parameters”.
  4. Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ’1′.
  5. Save the changes.
  6. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
  7. Select the just created Parameter Group and enable “Apply Immediately”.
  8. Click on “Continue” and confirm the changes.
  9. Wait for the "Modifying" operation to be completed.
  10. Again, open the “Instances” tab. Expand your MySQL instance and expand “Instance Action” tab and select "Reboot".