{"id":2322,"date":"2014-08-28T14:20:35","date_gmt":"2014-08-28T14:20:35","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=2322"},"modified":"2014-08-28T14:23:00","modified_gmt":"2014-08-28T14:23:00","slug":"amazon-rds-mysql-experience","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/amazon-rds-mysql-experience\/","title":{"rendered":"Amazon RDS with MySQL – My Experience"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

No problem though, we can adjust that within the RDS Console.<\/p>\n

Allowing Triggers to be created<\/h3>\n

So, once you have your database setup, here’s are the next steps to all triggers to be created:<\/p>\n

    \n
  1. In the AWS Console, create a new “Parameter Group”.<\/li>\n
  2. Change “login_bin_trust_function_creators” to 1.<\/li>\n
  3. Go to “Instances”<\/li>\n
  4. Modify your Instance<\/li>\n
  5. Choose the “New Parameter” group you just created<\/li>\n
  6. Restart your instance<\/li>\n<\/ol>\n

    \"parameter-groups\"<\/p>\n

    Setting up Remote Access<\/h3>\n

    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.<\/p>\n

    Strangely, this option is not available in the AWS Console, you need to go to the EC2 Console.<\/p>\n

    https:\/\/console.aws.amazon.com\/ec2\/v2\/home?region=us-east-1#SecurityGroups:<\/p>\n

      \n
    1. Choose the default security group<\/li>\n
    2. Edit inbound rules<\/li>\n
    3. Add the IPs of the servers that should be granted access.<\/li>\n<\/ol>\n

      Importing your existing database<\/h3>\n

      Beyond that, migrating the existing database over to RDS was rather simple. A one-liner did the job:<\/p>\n

      mysqldump -u myLocalUsername<\/em> -pLocalDatabasePassword<\/em> mydatabase<\/em> | mysql -u myAmazonusername<\/em> -h xxxx.us-east-1.rds.amazonaws.com -pMyAmazonRDSPassword<\/em> amazonDatabase<\/em><\/p>\n

       <\/p>\n

      Hope that helps!<\/p>\n","protected":false},"excerpt":{"rendered":"

      Migrating a database over to Amazon RDS isn’t an all day event, but you will run into a couple snags. This post points out two issues that I ran into and quick solutions.<\/p>\n","protected":false},"author":1,"featured_media":2325,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[41,13,1],"tags":[289],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2322"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=2322"}],"version-history":[{"count":2,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2322\/revisions"}],"predecessor-version":[{"id":2326,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2322\/revisions\/2326"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media\/2325"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=2322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=2322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=2322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}