Categories
#DEV

You do not have the SUPER Privilege and Binary Logging is Enabled

Recently while trying to upgrade our project management app, I encountered this error, “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)”.

What we initially thought was the lack of TRIGGER privileges for that MySQL user. But even after giving the MySQL user all necessary privileges, we continued to experience that error during a backup process. By the way, you would need to specify the SUPER privileges for the user running the import into the database along with the CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION and ALTER FUNCTION privileges. We use Amazon RDS for storing and managing data across all our websites and as many of you might be aware, Amazon isn’t keen on giving super privileges to users of their system.

To fix this issue, here is the solution that worked for us…

1) Open the Amazon RDS Console.
2) Go to the “Parameter Groups”
3) Create a New Parameter Group (You can add to existing custom parameter group if you got one). On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
4) Click on “Edit Parameters”
5) Look for the parameter “log_bin_trust_function_creators” and set its value to ‘1’
6) Click on “Save Changes”
7) Open “Instances” and Expand your Desired MySQL Instance
8) Click on “Instance Action” and Select “Modify”
9) Select the Parameter Group and enable “Apply Immediately”.
10) Click on “Continue” and Confirm Changes

It’s best to reboot for changes to reflect. Select the instance and reboot your MySQL instance. That should do the job. For those of you on traditional MySQL environment, you can specify the log_bin_trust_function_creators option in two ways.

1) Specify on Server Start as this “–log-bin-trust-function-creators=1”
2) By setting it to “1” using a Global Statement

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Alternatively, If you are not planning to use your MySQL server for the replication consider turning the binary logging off by removing the option –log-bin from the command options for the mysqld utility starting that MySQL server. Hope that helps developers experiencing similar issues with importing and exporting SQL or should I say, while creating a dump 😉

Categories
#DEV

Import MySQL Database via SSH

We have been migrating a lot of our MySQL databases across to Amazon RDS due to our extensive use of AWS services. In the process of doing this, we realized the traditional PhpMyAdmin UI doesn’t do the job anymore.

It will timeout, and we need to keep uploading the same SQL multiple times to get it to import bit by bit partially. It can become quite a handful and somewhat frustrating especially when your database is more than 100MB or so in size.

An alternative way is to import through good ole SSH. This is how we do it. We export the database SQL file. Then, we login to our remote database (Amazon RDS) and import it to that remote DB instance. These commands are not suitable for importing to localhost MySQL.

1) Login to your MySQL with root user by using the following command.

mysql -h main.xxxxxxxxxxx.us-east-1.rds.amazonaws.com -P 3306 -u YOURUSERNAME -p

2) Once you are in, you can select the database you want to import into by entering the following:

USE DATABASENAME;

3) You should get a successful database selected message. Then enter the following to import desired SQL file

SOURCE FILENAME.sql

4) You should get a whole load of queries being executed. After a while, it should stop and hopefully you have a fully imported database ready to be used.

Happy Migrating!