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
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
Note: 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!
Pingback: cant login to db ec2 .com Sign In Online Support Customer Service - gologinme.com
Thanks! It helped me
Ubuntu mysql conf file : /etc/mysql/mariadb.conf.d/50-server.cnf
Pingback: Connect to AWS MySQL database via Node JS – inneka.com
It works if I’m in my machine or in a project inside of the EC2 instance, but it not working if I’m trying to connect from a lambda function
thanks!!!
for mariadb on ubuntu:
etc/mysql/maridadb.conf.d/50-server.cnf
i launched a AWS ec2 and i deployed php application using apache2 and githhub …i got a database file(contact.php ……including DATABSE IP ,DATABSE PASSWORD ,DATABSE USER) from one of my developer so i justt want to know that how we where i put this file so that our php aplliaction deployed successfully and users create their profile using my php website……PLZZZZ HEPL ME
Pingback: Test driving the New MicroStrategy on Amazon Web Service (AWS) – Barefoot Data Science
After spending two hours trying to figure this out, I finally got it after following your article. Thank you!!!
Pingback: Connecting Remotely to MySQL Database on an Amazon EC2 Server – Sampath Subasinghe – Call me Sam.
It works. Superb bro… Thanks a lot :)
Thanks ! It helped me.
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.
Thanks for the advice!!!
Thanks Brother :) … finally something helped me
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
what is default password for available mysql in EC2?
I think you should open SSH tunnel using pem file, as exposing db port might be quite risky
Could you please help us to do it that way, I’ve been trying wih my PK using Navicat but haven’t succeeded. Thanks in advance
Thank you so much. Loved this tutorial.
Thanks very much!!
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
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.
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’@’%’;