Skip to content
May 18, 2012 / Kyle

Allowing MySQL on Amazon’s EC2 to accept remote connections

Hello, This blog is moving over to www.whoisthestudent.com. More Amazon EC2 articles to come!

Hopefully see you there!


Amazon’s EC2 is a really great platform for well, everything!

I recently got a LAMP server setup some development, instead of having a LAMP server running locally. It is always a nice idea to see your code in an actual production environment, not local setting.

One little hitch people will come across after setting up a LAMP server on the EC2, is how do you allow remote connections to your mysql database. 

It is actually really simple, just a few small extra steps are needed!

Ok, it’s get started…

  1. Head over to your myself configuration. file. located at /etc/mysql/my.cnf and look for the “bind-address” this should have a value of 0.0.0.0
  2. Restart your mysql server
  3. Next we need to open the port on the EC2. Log into your user account and on the left hand side, head down to “Security Groups“. Look for the security group that is associated with your EC2 instance. (You can find the name of the security group for your instance to the right hand side of the instance name on main instance listing page). Once you have found the security group, click on it and under the “Inbound” tab you will see a list of port rules for the inbound traffic.Here you can open the MySQL Port (3306 by default unless you changed it). You will see a list of predefined ports for services that you can select to quickly add. Once of these is for MySQL (Others include HTTP, SSH etc).Don’t forget to click “Apply Rule Option” at the bottom when you are finished.
  4. Finally, we need to make a non “root” user account that will be used to access the database remotely. To access mysql from the terminal type: mysql -u root -pAnd you will then be prompted for your password. Once you have opened MySQL run the following command: GRANT ALL PRIVILEGES ON *.* TO bob@’%’ identified by “yourpasswordhere”;By running this command, you will be giving access to a user called bob, with the password yourpasswordhere and it will be accessable from any location.

And your done!

Advertisements

8 Comments

Leave a Comment
  1. Andrew / Mar 14 2013 2:54 pm

    Thank you very much! This is exactly what we needed!!!

  2. Patrick / Apr 23 2013 6:18 am

    Hi, I have followed all the steps above, but I still get the error:
    SQLSTATE[28000] [1045] Access denied for user ‘myuser’@’myipaddress’ (using password: YES)
    Please can you help?

    • Patrick / Apr 25 2013 11:43 am

      I got it to work in the end, I had set up mySQL myself on Ubuntu and hadn’t realised the RDS database was different. now I am connecting to the RDS database which is in the correct security group.

  3. Dan Gaz / Jun 4 2013 11:39 pm

    Nice Guide 🙂
    And Working!

  4. Arthven / Jul 25 2013 1:36 pm

    Thanks, worked like a charm!

  5. Jason Fang / Feb 6 2014 7:05 pm

    bind-address is an important step I missed… it is working now. Thx a lot!

  6. emccullough92 / Oct 8 2014 2:22 pm

    This worked like a charm! Thanks!

  7. Luis Guillermo Moreno / Nov 11 2014 3:31 am

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: