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
- mysql server has port 3306 open on iptables
- server in aws has security group with 3306 open
- mysql has a user granted all permissions on any ip 'user'@'%'
- 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))