[chef] Re: why does mysql on ec2 bind to priviate i.p?

[Charles Johnson < >]

The real problem here is an outdated limitation in mysql.

Unfortunately, mysql only takes one bind-address argument rather than an array, so it's impossible to listen on a limited subset of local addresses. You may decide to have mysql listen on all bound IP addresses, or one and only one bound IP address, but you many not listen a subset of all bound IP addresses (unless you bring local firewalls into the picture, but that's another discussion).

When running in an inherently insecure environment such as EC2, binding to 0.0.0.0 and allowing connections from any host is an unacceptable default. This leaves an unfortunate dilemma: Either bind to the private IP address and disallow traffic from localhost, or bind to localhost and only allow traffic from self.

If the default is to bind to localhost, this will cause countless hours of frustration as people try to figure out why the database isn't accessible from their external servers out of the box. However, since it's possible for a node in EC2 to access its own private IP address and resolve it to a hostname, the default that arguably solves the most problems and creates the least frustration is to bind to the private IP address.

Many people get around this problem by using unix sockets for communication to mysql if they're running the app and database on the same node. Unfortunately in your case, JDBC is 100% TCP/IP and doesn't support unix sockets. The solution for you is either to override the cookbook's default bind_address to 127.0.0.1, or to use a template to get the hostname into your application's config file instead of localhost.

tl;dr: The default behavior of the cookbook is sane, even though it's inconvenient for many use cases. You'll need to override the cookbook's default attribute back to localhost, or address the database via the hostname instead of localhost.

Thanks,

--Charles

From: S Ahmed < "> >
Reply-To: " "> " < "> >
Date: Wednesday, November 14, 2012 8:33 AM
To: " "> " < "> >
Subject: [chef] Re: Re: why does mysql on ec2 bind to priviate i.p?

If I set it to 127.0.0.1, does this mean I can't access it from another ec2 server then?

The problem is during deployment, I don't know the private i.p in advance and during deployment all my database configuration information has 'localhost' in it.  I understand what you guys are saying and it does make sense, I just have to modify my deployment process to first pull in the private ip's for each service (I should be doing this anyhow as eventually not everything is going to fit on a single instance!)

On Wed, Nov 14, 2012 at 9:26 AM, Joshua Timberman < " target="_blank"> > wrote:

On 11/13/12 9:28 PM, "S Ahmed" < "> > wrote:


>I noticed when I used the mysql recipe it installs mysql and binds it to
>the private i.p on ec2, why is that?
>
>My application can't connect as it uses 'localhost' (jdbc), but for some
>reason it works fine using ruby.

The reason for not using localhost is because in the opinion of the
cookbook, it is more useful to have the server accessible on the [private]
network so that other systems like application servers can connect to it.
We use the private address on cloud providers because, as Matt pointed
out, it may cost money for external bandwidth. Also, having your database
server accessible on the external network is bad :).

You can set the attribute `node['mysql']['bind_address']` in a role or on
the node itself, to use loopback.


For example, in a role:

default_attributes(
  "mysql" => {
    "bind_address" => "127.0.0.1"
  }
)

On the node itself would be the JSON equivalent of the above.