MySQL/Amazon RDS error: “you do not have SUPER pri

Question

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

I successfuly 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 1

As defined in AWS documentation triggers, procedures, and functions are disabled by default because binary logging is enabled by default. Disabling basically makes your db more secure, but if you have properly secured through the network it won't matter.

Follow these steps and your problem will be fixed https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

Also you shouldn't use definers when creating procedures. A simple sed command can remove it.

Answer 2

Per http://getasysadmin.com/2011/06/amazon-rds-super-privileges/, you need to set log_bin_trust_function_creators to 1 in AWS console, to load your dump file without errors.

If you want to ignore these errors, and load the rest of the dump file, you can use the -f option:

mysql -f my_database -u my_username -p -h  
my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql

The -f will report errors, but will continue processing the remainder of the dump file.

Answer 3

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".

Answer 4

In addition to editing

log_bin_trust_function_creators = 1

you need to remove all DEFINER from your dump file, check the following link for SED command that can help cleaning your sql dump file.

https://www.percona.com/blog/2014/07/02/using-mysql-triggers-and-views-in-amazon-rds/#comment-10968243

Answer 5

The problem with triggers and stored procedures in the dump file is that these definitions include the user who the stored procedure should be created by, the DEFINER. The user most likely doesn't exist in the RDS so a error is then raised. To be able to load the dump file you can remove the DEFINER using sed or Perl and create the stored procedure/trigger with the user who is performing the import.

perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < mysqldump.sql > mysqldump.fixed.sql

Now you should be able to load the fixed dump file

mysql my_database -u my_username -p -h rds_host < mysqldump.fixed.sql

As said in earlier answer, you should set the DB Parameter:

log_bin_trust_function_creators = 1