I'm trying use a host restricted MySQL user when connecting from one Amazon EC2 instance to another. I wanted to use elastic ip address for their dns function, but you can probably ignore the Amazon EC2 references in this question.

I think my question is how to define the dns name of the mysql client by the client when the connecting as a user to mysql?

I apologise if I have problem wrong. Perhaps it is more generic - how do define local machines host name for use with any outgoing connection?

The problem is the client app has two dns names but I need to to use the other. The details are:

db.fixedip.amazonaws.com                  app.fixedip.amazonaws.com
db.randomdns.ec2.internal                 app.randomdns.ec2.internal

Actual machine instance
MySQL on Linux                            App on Windows 2003

From app

mysql -uuser -p -hdb.fixedip.amazonaws.com
ERROR 1130 (HY000): Host 'app.randomdns.ec2.internal' is not allowed to connect to this MySQL server

db.fixedip.amazonaws.com conveniently resolves to the internal IP address (which can change) but the MySQL client isn't identified as its non-random app.fixedip.amazonaws.com public dns name.

This doesn't seem to work :)

mysql -uuser@app.fixedip.amazonaws.com -p -hdb.fixedip.amazonaws.com

UPDATE: The current answer redefines the problem - don't bother with hosts in mysql and use alternative host restriction. I'll accept that unless anyone else would like to comment on the original question

  • So you have MACHINEA and MACHINEB. MACHINEA is known externally as MACHINEA_EXTERNAL and internally as MACHINEA_INTERNAL. You want, when MACHINEA connects to MACHINEB for MACHINEA to identify itself as MACHINEA_INTERNAL? Or you want MACHINEB to interpret all communications from MACHINEA to resolve as MACHINEA_INTERNAL? – Driftpeasant Nov 28 '11 at 21:40
  • If I follow your names correctly I actually need to match MACHINEA_EXTERNAL in MySQL because MACHINEA will become MACHINEC/D/E/F... I'm sure if MACHINEA communicates it's name or if MACHINEB resolves it nor am I sure if MySQL is involved. Hence posting the question. – KCD Nov 29 '11 at 0:19

A standard way to implement secure MySQL database access in EC2 is to use EC2 security groups. Only allow connections to the MySQL database server port (3306) from EC2 instances in a specific security group, say, "dbclient". Put any database client EC2 instance in the "dbclient" security group.

Configure MySQL itself to allow connections from your authenticated user at any host ("%").

This gets around the problem of having to keep track of what the internal IP addresses are for all of your EC2 instances, especially since these change when you stop and start an instance, even if you assign elastic IP addresses.

Here's an article I wrote that can help you connect from the database client to the database server using an internal IP address based on an Elastic IP address DNS name for the server:

Using Elastic IP to Identify Internal Instances on Amazon EC2

This helps get around the problem of the database server IP address changing when it is stopped and started or replaced.

  • fancy seeing you here. Yes I've read your article, very helpful, and yes I am using elastic IP's which is why the external IP addresses are fixed. Perhaps the short answer is no and I may just update the database user after each start/launch – KCD Nov 29 '11 at 0:31
  • The approach you describe (depending on the hostname of the connecting instance) is actually less secure than using security groups. If your client instance is stopped, terminates, or fails, then another EC2 customer will get that internal IP address and DNS name with their new instance and your permissions would allow a connection from their instance. A security group restricts connections to instances you own and designate. – Eric Hammond Nov 29 '11 at 3:41
  • Hi Eric. Yes I use security groups exactly as you suggest. The better conclusion I am reaching is that I will be ok with a low risk of any hijacked host also obtaining other user credentials for mysql. Thanks for your feedback – KCD Nov 30 '11 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.