Main menu

AWS RDS Database restore problem – fixed

I have created a MultiAZ MariaDB instance and trying to restore a database from command line from ec2 instance. Connecting to an RDS is same as connecting a regular SQL instance, replace the host with your RDS endpoint. RDS Database restore sometime cause issues with default setting, let’s see

AWS RDS Database restore from ec2 instance

mysql -h mydb.cq4vo2q43yde.us-east-1.rds.amazonaws.com -u admin -p mydatabase < mydatabase.sql

and getting the issue that tells

ERROR 1419 (HY000) at line 119: 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)

By default RDS not allowed any user crate or insert trigger into the databases. Let’s have a quick solution.

We we launch a RDS instance it create a default Parameter Groups having the default database parameter. You can’t change the default parameters but you can add your own Parameter Group.
Create a new parameter group and edit the parameters, search for log_bin_trust_function_creators and modify the value to 1.

 

AWS RDS Parameter Group

AWS RDS Parameter Group Change
Navigate to RDS instances and restart the RDS.

After this try to restore your database again if you getting any issues like this..

Packets larger than max_allowed_packet are not allowed.

Go to the your Parameter group, edit and search for max_allowed_packet and set the value something higher like 1073741824.
Restart the RDS instance.

ERROR 1227 (42000) at line 2525: Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

This is a command error while restore the tirggers. User defined the trigger not avaible on your MySQL instance. Create the user defined the your backup or change the username in your dump.

Open your dump file and search for DEFINER paraments. Check the value and create the user in your database.

grant all on yourdb.* to `user`@`%` identified by 'password';

Restore your Database and you are good to go.

More information about RDS parameter group can be found here..

FacebookTwitterGoogle+RSS