connecting to mysql internal to ec2

Question

I have a db server db.example.com CNAME'd to its external ec2 domain ec2-some-ip.aws.com

I have a client ec2-another-ip.aws.com trying to connect to my db.example.com. I granted privileges:

grant all privileges on *.* to 'some_user'@'ec2-another-ip.aws.com';

and in the client try:

mysql -u some_user -h db.example.com

only to get:

ERROR 1130 (00000): Host 'my-client-internal-mac.compute-1.internal' is not allowed to connect to this MySQL server

So it obviously maps its external domain to the internet domain. So How do I get mysql to allow my client given the restriction below.

NOTE I can't use the internal ip as all these servers are ebs root mounted and i start/stop them all the time, so the internal ip changes, but i map an external elastic ip each time i spin it up so its external ip is always the same

Answer 1

The easy method is to grant access to someuser@%, which means "someuser from any host". This reduces security, but it may be good enough depending on your setup.

In particular, if your EC2 security settings don't allow public access on port 3306, this should be fine. The EC2 firewall will only allow access from machines launched by your account.

Answer 2

What exactly will ec2 firewall pass/deny depends on settings of security group. Hopefully default security group is configured to deny all traffic from any machines (ec2 or internet) that do not belong to the same security group. look here for a nice explanation Anyway it is easy to mistakenly open your ec2 mysql db to all internet if you are not care enough...

I am also struggling with the same problem: ec2 mysql client connects to ec2 mysql server via its private ip. Even if you assign elastic ip to mysql client it does not help since communication is done via private ip which is changed with each reboot. Maybe it is possible to force mysql client to connect via its public ip interface. It seems possible but on the other side it could be costly since communication via public ips in ec2 is paid!

So currently my db account is accessible from any host in internet ( someuser@% )and security is delegated to ip/security-group access layer.

Answer 3

I'm slightly uncomfortable with leaving my security "delegated to ip/security-group access layer". Actually, I'm not sure what that really means.

Furthermore, I dot not think it's a good idea to leave wildcard mysql grants open, unless my goal is to get hacked.

IMHO the most secure way to do this is to to use elastic IPs and hard-code the IPs in the MySQL grant table (ouch, but it's the best way).