What's the best way to allow MySQL on one server to listen to requests from two other different servers?

Question

I have my MySQL database server on Server 1. I want to have my Rails apps on two other servers - say A and B to be able to connect to this Server 1. What's the best way to do this?

In the my.cnf file it appears I can use the bind-address to bind to one and only one IP address. I can't specify the IP addresses of both A and B in my.cnf.

On the other hand, if I comment skip-networking, the gates are wide open.

Is there a golden mean? What are you folks doing to allow a DB server to listen to requests from multiple app servers and still stay secure?

Answer 1

The bind address is the local IP address of the server, not the allowable client addresses. In your situation, you can provide the static address of your server (in place of localhost) or, if your IP might change, just comment it out.

Again, to clarify: the bind-address is the address on which the server listens for client connections (you could have multiple NICs, or multiple IP addresses, etc.). It is also possible to change the port you want mysql to listen to.

You will want to make sure you configure the root password if you haven't already:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');

You would then use other means to restrict access to MySql to something like the local network (i.e. your firewall).

Answer 2

A DB server will listen to an indefinite number of clients.

Each client Rails app identifies the DB server.

The DB server waits patiently for connections. It has no idea how many clients there are or where the connections come from.

Edit

"how do you securely configure the DB wrt what servers to accept requests from?"

That's what networks, firewalls and routers are for.

That's why the database requires credentials from the Rail apps.

Answer 3

In addition to getting the bind address right you'll need to open the correct port, create or configure the users and some other details. This explains it pretty clearly.

Answer 4

If MySQL is running on Linux:

I am very biased towards using iptables (a.k.a. netfilter, the Linux firewall) to control incoming traffic to various ports. It's simple to use and very robust.

iptables -A INPUT -p tcp -s server1address/32 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -s server2address/32 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

Answer 5

More info about iptables:

The iptables commands above must either be inserted in the existing iptables tables, or else you must delete the existing stuff and start from scratch with the commands above.

Insertion is not hard, but it depends a little bit on the Linux distribution you use, so I'm not sure what to recommend.

To start from scratch, you need to Flush and eXpunge the existing tables first:

iptables -F
iptables -X

Then insert the iptables firewall rules that you need to use, following the model indicated in my previous answer.

Then save the iptables rules. This is again distribution-dependent. On most Red Hat derivatives (Red Hat, Fedora, CentOS), it's enough to run:

service iptables save

Voila, your custom rules are saved. If the iptables service is enabled (check with "chkconfig --list iptables", it must be ":on" on runlevels 3 and 5, depending on your situation, but it's safe to set it ":on" on both 3 and 5 in any case) then your rules will survive the reboot.

At any time, you can check the current running iptables rules. Here's a few commands that do that, with various levels of verbosity:

iptables -L
iptables -L -n
iptables -L -n -v

Without -n, it will try to lookup the domain names and display them instead of IP addresses - this may not be desirable if DNS is not working 100% perfect. So that's why I almost always use -n.

-v means "verbose", a bit harder to read but it gives more information.

NOTE: If you start from scratch, other services running on that machine may not be protected by iptables anymore. Spend some time and figure out how to insert the MySQL rules in the existing tables. It's better for your system's security.