MySQL remote connection: Public vs. private IP address

Question

I just went through the obligatory pain of getting MySQL to accept a remote login. Yes, I had to create a user of the form ''@'%', and I did so. Yes, I had to GRANT ALL ON . to my new '@%' user and then flush privileges, and I did so. Yes, I had to open up port 3306 on the server, and I did so. But, trying to access MySQL via HeidiSQL, I continued to have no luck at all.

Cutting to the chase, my struggle was with the bind address. I'm running Ubuntu Linux on an AWS server instance, so I have a public IP address and a private IP address. And of course there's always the MySQL default IP address of 127.0.0.1. That default wasn't allowing me any remote connections, though, so I figured I should try the public IP. But in that case, MySQL would not start. Finally I took a shot and used the private IP address as the bind address, and everything fell into place: I was able to stop and restart MySQL, and I was able to connect from HeidiSQL

But I am totally confused. HeidiSQL addresses this server via its public IP address. But MySQL refuses to bind to the public IP address! (As I said, when I tried, MySQL wouldn't even start.) MySQL has the private IP address as its bind address. What's going on here? Why, to get a remote connection, do I need HeidiSQL targeting port 3306 at the public IP address but MySQL bound to the private IP address?

Answer 1

The explanation here is a straightforward one: You can't bind a listening socket to an IP address that your machine's IP stack isn't aware of.

In AWS (whether EC2 Classic or EC2/VPC) as in many network environments these days, your external IP is not configured in your machine -- it's mapped through an external device (router, firewall) to your machine's private IP via NAT (network address translation).

You can confirm that your server itself has no idea about the external IP:

 $ ifconfig

The simplest solution, of course, is binding to 0.0.0.0 unless you have a specific reason not to.

http://en.m.wikipedia.org/wiki/Network_address_translation

Answer 2

Try this:

The MySQL server's /etc/hosts file must contain server IP address so please verify it once..

$ sudo cat /etc/hosts