Just today I moved one of my client’s ecommerce databases over to Amazon RDS. All told, the process was only mildly painful. I’m impressed with the low-cost vs amount of storage, reliability and speed that comes with it.
I ran into two issues while migrating the existing database over to Amazon RDS. The first issue that you run into is that you are not granted “SUPER user” privileges. This causes problems when you attempt to create triggers or functions, as it does some kind of logging that requires “SUPER” privileges.
No problem though, we can adjust that within the RDS Console.
Allowing Triggers to be created
So, once you have your database setup, here’s are the next steps to all triggers to be created:
- In the AWS Console, create a new “Parameter Group”.
- Change “login_bin_trust_function_creators” to 1.
- Go to “Instances”
- Modify your Instance
- Choose the “New Parameter” group you just created
- Restart your instance
Setting up Remote Access
So, I think for most of us, we’ll be using RDS as a public database. In that case, you need to identify which servers are able to access the database.
Strangely, this option is not available in the AWS Console, you need to go to the EC2 Console.
https://console.aws.amazon.com/ec2/v2/home?region=us-east-1#SecurityGroups:
- Choose the default security group
- Edit inbound rules
- Add the IPs of the servers that should be granted access.
Importing your existing database
Beyond that, migrating the existing database over to RDS was rather simple. A one-liner did the job:
mysqldump -u myLocalUsername -pLocalDatabasePassword mydatabase | mysql -u myAmazonusername -h xxxx.us-east-1.rds.amazonaws.com -pMyAmazonRDSPassword amazonDatabase
Hope that helps!