Connecting Remotely to MySQL Database on an Amazon EC2 Server

While this was tested using a TurnKey LAMP Server build (Debian Linux), it applies to any Debian/Ubuntu server with MySQL.

Top-Level View of Tutorial

  • Enable MySQL access through server’s firewall (Amazon AWS-EC2 Security Groups)
  • Create non-root MySQL user with % (any) Host permissions
  • Alter the bind-address in the MySQL configuration file (my.cnf) file
  • Restart MySQL

Amazon AWS Security Groups for your EC2 Server

aws-security_groups
Hop into Amazon AWS and select Security Groups on the left side. Select your server in the top box, then you can click on the INBOUND, then you can EDIT the list.

On the new page, you can select MySQL from the left drop-down box, which automatically sets it to TCP and port 3306. The final choice is the SOURCE needs to be set to Anywhere to allow you access it from any IP address. Click SAVE and you’re all done with Amazon AWS.

Create MySQL User with Remote Access Permissions

phpmyadmin_add-user_editNote: This step makes use of PHPMyAdmin, though the step can be recreated with a simple SQL query as well.

If you want to give remote access to only a single database, it’s best to first select that database on the left-side of PHPMyAdmin. You can then select the PRIVILEGES tab and click Add New User near the bottom. You can use whatever username you wish, but the important detail is to set the Host to Use Text Field and then set the text field to %. This allows access from any IP address. The remaining fields should be straight-forward (you can also use the image as a reference).

Hit Go on the bottom-right to save your new user and wrap up your work in this section.

Alter MySQL Configuration (my.cnf)

The following step requires either the ROOT user or use of the sudo command.

You’ll need to access the command line for your server, most likely via SSH. The commands below assume you are the root user.

The file you are looking for is located in /etc/mysql/ and is called my.cnf. You can edit it with your favorite editor, such as Vi or NANO. NANO is the most user-friendly on first-use, so you can edit the file with:

nano my.cnf

Find the line that looks like this:

bind-address = 127.0.0.1

Alter it as such:

bind-address = 0.0.0.0

Save your changes (in NANO press CTRL+X and follow the prompts to finish saving the file and exit).

Restart MySQL

The final step is to restart MySQL. The following command gets it done:

/etc/init.d/mysql restart

Takes a few seconds, but then you’re all set! You can now access the MySQL database via the new user you created from any external IP address!

Share and Enjoy:
  • Facebook
  • Twitter
  • Reddit
  • StumbleUpon
  • Digg
  • del.icio.us
  • Tumblr
  • PDF

14 thoughts on “Connecting Remotely to MySQL Database on an Amazon EC2 Server

  1. Pingback: Test driving the New MicroStrategy on Amazon Web Service (AWS) – Barefoot Data Science

  2. Pingback: Connecting Remotely to MySQL Database on an Amazon EC2 Server – Sampath Subasinghe – Call me Sam.

  3. Brian Bezanson

    Under Ubuntu 16.04 on AWS and using MySQL 5.7 the location of bind_address is:

    /etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1

    Also note for AWS on the linux server I also configured Ubuntu’s ufw firewall to allow 3306 traffic.

    Reply
  4. Javier

    I have an AWS LAMP that i acquired 1 month ago, (the one which belongs to the free tier), but can’t connect to MySQL from the outside, even though I followed all of your steps in this tutorial. Well, actually I don’t have PHPMyAdmin or wasn’t able to figure out hoy to locate it and load it.
    I’m only able to work from SUSE Linux console, not GUI available.
    The only thing I was able to do in MySQL was to run the scripts by pasting their content directly on the MySQL console via PuTTY. But I would need to connect from let’s say MySQL Query Browser from my home PC, as I will have to do several tasks and run a lot of inserts on my BD, and that’s not comfortable at all via PuTTY, as when I ran several inserts, the console became crazy. I finally figured out I could only run up to 50 inserts in a row so I could avoid errors on the console.
    Besides, opening the AWS port by using the security groups policies could be dangerous, couldn’t it? Because you would be allowing anyone from anywhere to intercept your communications and hack your database. I’m not a specialist in security, just saying because I’ve read some comments about it. I’m not saying this to criticize your solution, it’s just a thought. But could you help me to find out another way to enable connection from my home PC to the AWS MySQL?
    Also, I’m trying to connect from my Country, which is outside the US, maybe that’s causing the problem.
    The error I’m receiving is: Host ‘r186-53-13-100.dialup.adsl.anteldata.net.uy’ is not allowed to connect to this MySQL Server

    Reply
  5. Bob Pinkerton

    I am using a standard LAMP stack at AWS and I do not have the directory /etc/mysql/. There is a my.cnf in the /etc/ directory and it has no Bind command. Is this the correct configuration file?

    Thanks

    Reply
    1. Mario Lurig Post author

      Probably. There really isn’t a ‘standard’ LAMP stack at AWS, because you could be on any distro, but you can Google for “your disitribution” and “my.cnf location” to be sure.

      Reply
  6. MatAff

    Thanks for the very clear explanation. This was exactly what I needed! What I thought would take days to figure out, got done in an hour.

    For those not using PHPMyAdmin I used the coder below which came from: http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

    CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
    CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;
    GRANT ALL ON *.* TO ‘username’@’localhost’;
    GRANT ALL ON *.* TO ‘username’@’%’;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *