Configuring MySQL to Listen on a Specific Network Interface

2018-03-09 · mysql, networking, security, linux

A CentOS box I set up once had MySQL listening on every interface, including the public one, and only a firewall rule stood between it and the internet. That rule got flushed during a network change. Nothing bad happened, but I didn't sleep well that night. Now I always bind MySQL to the private address as well, so it doesn't depend on the firewall alone.

A fresh install listens on all interfaces on CentOS and only on 127.0.0.1 on Ubuntu, and neither suits a server with a public NIC and a private back-end network. Below is how bind-address works, how I pair it with a firewall, and how to check from outside that it's listening where you intended. It applies to MySQL 5.6 and 5.7 and MariaDB 10.1 and 10.2 on CentOS 7 and Ubuntu 16.04. I'm assuming two interfaces, say eth0 public and eth1 private, and root access.

Pick the address

ip -4 addr show

Pick the private address, for example 10.10.0.20 on eth1. Note that bind-address takes a single IP address or hostname, not an interface name and not a list. If you need MySQL reachable on two specific addresses but not a third, you must bind to 0.0.0.0 and rely on the firewall for the rest.

Set bind-address

On CentOS 7 the option belongs in /etc/my.cnf; on Ubuntu 16.04 in /etc/mysql/mysql.conf.d/mysqld.cnf for MySQL or /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB.

[mysqld]
bind-address = 10.10.0.20
port         = 3306

Useful values:

127.0.0.1 accepts TCP only from the local machine.
10.10.0.20 accepts TCP only on that address.
0.0.0.0 accepts on all IPv4 addresses.
:: accepts on all IPv4 and IPv6 addresses (requires IPv6 enabled on the host).

The Unix socket at /var/lib/mysql/mysql.sock (CentOS) or /var/run/mysqld/mysqld.sock (Ubuntu) is unaffected by bind-address; local clients keep working.

Restart:

sudo systemctl restart mysqld      # CentOS, MySQL
sudo systemctl restart mysql       # Ubuntu, MySQL
sudo systemctl restart mariadb     # MariaDB

Firewall anyway

Binding isn't a substitute for a firewall. It's the second layer. Allow 3306 only from the application subnet.

CentOS 7 with firewalld:

sudo firewall-cmd --permanent --new-zone=dbclients
sudo firewall-cmd --permanent --zone=dbclients --add-source=10.10.0.0/24
sudo firewall-cmd --permanent --zone=dbclients --add-port=3306/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --zone=dbclients --list-all

Ubuntu 16.04 with ufw:

sudo ufw allow from 10.10.0.0/24 to 10.10.0.20 port 3306 proto tcp
sudo ufw status numbered

Don't add a generic allow 3306 rule. The whole point is that the public interface never sees the port.

Tighten the accounts

Binding controls where the socket listens, but accounts control who can log in. Now that connections only arrive on the private network, I narrow the host patterns to match:

CREATE USER 'app'@'10.10.0.%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'10.10.0.%';

And remove any leftover 'app'@'%' accounts:

SELECT User, Host FROM mysql.user WHERE Host = '%';
DROP USER 'app'@'%';

While you are here, add skip-name-resolve to the [mysqld] section so the server never waits on reverse DNS for a connection on the private network.

Check it from both sides

First the listening socket on the server:

sudo ss -ltnp | grep mysqld
LISTEN 0 80 10.10.0.20:3306 *:* users:(("mysqld",pid=1187,fd=21))

The local address column should show your private IP, not *:3306 or 0.0.0.0:3306.

Then test from an application host on the private network:

mysql -h 10.10.0.20 -u app -p -e "SELECT USER(), @@hostname;"

And from a machine on the public side, or from the server's own public IP, confirm it is refused:

nc -zv 203.0.113.50 3306
# nc: connect to 203.0.113.50 port 3306 (tcp) failed: Connection refused

Connection refused means nothing is listening on that address, which is what a correct bind-address gives you. If the firewall is dropping packets instead, you get a timeout rather than a refusal; either result is acceptable, but the refusal proves the bind is right independent of the firewall.

Gotchas

[ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address in the error log, and the service fails to start. The IP in bind-address is not configured on any interface, often because a DHCP lease changed or the config was copied from another server. Fix the address or, on a host whose private IP comes from DHCP, consider binding to 0.0.0.0 and enforcing with the firewall.

MySQL starts before the network is up on a reboot and hits the same error. Add After=network-online.target and Wants=network-online.target to a systemd override with sudo systemctl edit mysqld, then systemctl daemon-reload.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.0.20' (111) from the application host. Error 111 is connection refused, so the server is not listening on that address; check ss -ltnp. Error (113) is "no route to host", which points at the firewall.

Local scripts break after binding to the private IP. They were connecting with -h 127.0.0.1. Either use the socket (drop the -h, or use -h localhost), or add --protocol=tcp -h 10.10.0.20.

Three layers for one port feels like overkill until one of them fails.