Ubuntu – Remotely connect to Amazon AWS Mysql

amazon ec2MySQLnetworkingserverssh

I have an Amazon AWS EC2 instance running Ubuntu 14.04. where I installed MYSQL 5.5.

I am trying to connect to the MYSQL running on this instance from my local Windows10 machine running Navicat.

State of the env:

3306 is listening

netstat -an | grep 3306 -> tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

MYSQL is started

ubuntu@ip-***-**-**-**:/$ sudo service mysql status
mysql start/running, process 28015

select User, Host from mysql.user;

| blog             | %         |
| blog             | localhost |


mysql> show grants for 'blog'@'%';
+--------------------------------------------------+
| Grants for blog@%                                |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'%'                 |
| GRANT ALL PRIVILEGES ON `blogdb`.* TO 'blog'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

telnet from my PC to the server does not work -> need to sleep. will come back tomorrow :)

I am connected to the Ubuntu instance via SSH using a private key.

The former problem:

When trying to connect from windows using Navicat to MYSQL running on my AWS instance I get the following error:

Can't connect to Mysql server on '41.42.434.169' (10061 "Unknown error") -> the IP stated here is not mine

The Host name/IP Address I use is the Public IP of my AWS instance.

This was fixed by commenting the # bind-address = 127.0.0.1 in my.cnf

Now telnet from my PC works

EDIT

Now I get the following error:

Access denied for user blog@myIPprovider.com ( using password: YES )

Best Answer

For remote access:

Check etc\mysql\my.cnf to see that the bind-address is not set to 127.0.0.1. Either set it to 0.0.0.0, or, to be more secure, add your IP address: 

bind-address      = 127.0.0.1
bind-address      = your_public_ip

Create a user in the mysql table:

CREATE USER 'non-root-user'@'localhost' IDENTIFIED BY 'any_password_u_like';
CREATE USER 'non-root-user'@'%' IDENTIFIED BY 'any_password_u_like';

GRANT ALL ON *.* TO 'non-root-user'@'localhost';
GRANT ALL ON *.* TO 'non-root-user'@'%';

AWS SPECIFIC

Make sure you have an inbound rule for port 3306

Related Question