What's the best way to let MySQL on one server listen for requests from two other servers?
I have a MySQL database server on server 1. I want my Rails applications 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 becomes possible to bind the bind address to one and only one IP address. I am unable to list the A and B IPs in my.cnf.
On the other hand, if I comment on skip-networking, the gate is wide open.
Is there a middle ground? What are you doing so that the DB server can listen to requests from multiple application servers and stay safe?
If MySQL is running on Linux:
I am very inclined towards using iptables (aka netfilter, Linux firewall) to manage incoming traffic to various ports. It is easy to use and very reliable.
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
source to share
The bind address is the local IP address of the server, not valid client addresses. In your situation, you can provide a static address for your server (instead of localhost) or if your IP address might change, just comment it out.
Again, to clarify: bind-address
is the address where the server listens for client connections (you may have multiple network adapters or multiple IP addresses, etc.). It is also possible to change port
which mysql you want to listen to.
You want you to set a root password if you haven't already:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');
Then you would use other ways to restrict access to MySql to something like a local network (i.e. your firewall).
source to share
More information on iptables:
The iptables commands above must be inserted into existing iptables tables, otherwise you must delete existing things and start from scratch with the commands above.
The insert is not complicated, but it depends a little on the Linux distribution you are using, so I'm not sure which to recommend.
To start from scratch, you first need to flash and eXpunge the existing tables:
iptables -F
iptables -X
Then paste in the iptables firewall rules you need to use following the model given in my previous answer.
Then save the iptables rules. This again depends on the distribution. On most Red Hat productions (Red Hat, Fedora, CentOS), running:
service iptables save
Voila, your custom rules are saved. If iptables is enabled (see "Chkconfig --list iptables", it should be ": on" at runlevels 3 and 5 depending on your situation, but it is safe to set it to "on" on both 3 and 5 in either case), then your rules will go to reboot.
You can check the current running iptables rules at any time. Here are some commands that do this with varying levels of verbosity:
iptables -L
iptables -L -n
iptables -L -n -v
Without -n, it will try to find domain names and display them instead of IP addresses - this might not be desirable if DNS doesn't work 100% perfectly. So that's why I almost always use -n.
-v stands for "verbose", is a little harder to read, but gives more information.
NOTE. If you are starting from scratch, other services running on this machine may not be protected by iptables. Spend some time figuring out how to insert MySQL rules into existing tables. It's better for your system security.
source to share
The database server will listen for an indefinite number of clients.
Each Rails client application identifies a database server.
The DB server is patiently waiting for a connection. He doesn't know how many clients there are or where the connections are coming from.
Edit
"how do you securely configure the database to which servers should it accept requests?"
What are networks, firewalls and routers for?
This is why the database requires credentials from Rail applications.
source to share