How To Get Remote MySql Access On AWS Ec2

Question

I have a setup in ec2 where i have 2 servers, one just hosts mysql while the other houses the software. I need the software server to have remote mysql access to the mysql server, ideally wanting it on the private ip so it's only accessible from inside the ec2 setup.

The problem here is, i have opened 3306 in AWS on the security group and added a user that has access granted on any ip address as well as opening 3306 on iptables. Yet i cannot connect to the mysql server remotely.

I have tried it via the elastic public ip with sequel pro and it just says it's unable to connect to the host. What am i possibly missing?

So summary

1. mysql server has port 3306 open on iptables
2. server in aws has security group with 3306 open
3. mysql has a user granted all permissions on any ip 'user'@'%'
4. ssh works fine

weirdly as well the my.cnf is no more than about 20 lines or so, seems to be a lot of missing config?

the server is mysql 8 and running on centos 7

Output of "ss -lntp":

State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 100 127.0.0.1:25 *:* users:(("master",pid=9365,fd=13)) LISTEN 0 128 *:3306 *:* users:(("mysqld",pid=28332,fd=34)) LISTEN 0 128 *:111 *:* users:(("rpcbind",pid=697,fd=4),("systemd",pid=1,fd=27)) LISTEN 0 128 *:22 *:* users:(("sshd",pid=1352,fd=3)) LISTEN 0 100 [::1]:25 [::]:* users:(("master",pid=9365,fd=14)) LISTEN 0 70 [::]:33060 [::]:* users:(("mysqld",pid=28332,fd=32)) LISTEN 0 128 [::]:111 [::]:* users:(("rpcbind",pid=697,fd=6),("systemd",pid=1,fd=29)) LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1352,fd=4)) 

Answer 1

Private IP addresses should work. If you had open 3306 incoming on the DB security group make sure that the web/app server security group has outgoing 3306 as well.

Also, you do not mention that, but are the server on the same subnets? In the same AZ? In the same region? If they are in the same subnet, private ips should work providing you have the SGs correct, but if you tried a more complex setup, you may need additional configuration.