Let's cover how to secure your MySQL installation.

On Digital Ocean, Linode and similar clouds, private networks are open to the entire data center (e.g. Newark or NYC3). They are NOT private to your specific account. My Linode server in Newark can potentially communicate to your Linode server in Newark over their private network address.

If your database requires access from other servers, there are a few steps you need to take to secure your MySQL installation.

Our Available Tools

MySQL comes with plenty of network security tools. Here are the essential ones:

1. MySQL Bind-Address - how/from where MySQL listens for connections
2. MySQL User security - who can connect, from where
3. Firewall - not MySQL directly, what network traffic the server allows in/out

MySQL Bind Address

The bind-address configuration within MySQL tells MySQL on which networks it can listen for connections.

Note that MySQL is usually configured to accept connections from a local socket file (a unix socket). The hostname "localhost" usually implies it's using the unix socket. Unix sockets are faux-files, so they are only accessible from within the local server.

The bind-address setting tells MySQL whether it can listen on a TCP socket for new connections. We have three basic ways to configure bind-address:

• MySQL can bind to no networks (connect over localhost only, e.g. a unix socket)
• MySQL can bind to all networks (0.0.0.0)
• MySQL can bind to a specific network, e.g. a public network that the whole internet can reach, or a private network that can only be reached from within a data center

The default configuration for bind-address is to listen on all networks! If bind-address is commented out or not defined, then it's inherently an insecure setting.

The more restrictive we can be, the better. If our application is on the same server as the database, we can close mysql from binding to any network (choosing instead to listening only on the local unix socket). More common is to also bind to the loopback network address 127.0.0.1 so both localhost (unix socket) and 127.0.0.1 (tcp socket) connections work, but nothing else.

Such a setup looks like this:

[mysqld]
# Unix socket settings (making localhost work)
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock

# TCP Socket settings (making 127.0.0.1 work)
port            = 3306
bind-address    = 127.0.0.1

MySQL User Security

In addition to setting what networks MySQL listens on, we can set where users are allowed to connect from. This means we can say "user my_app_user can only connect to MySQL from the server whose address is 192.168.33.10".

Let's see how that looks in MySQL.

What users exist

Run the following to see what users exist on the MySQL server:

mysql> SELECT User, Host from mysql.user;
+-----------+--------------------+
| User      | Host               |
+-----------+--------------------+
| root      | 127.0.0.1          |
| root      | ::1                |
| mysql.sys | localhost          |
| root      | localhost          |
+-----------+--------------------+
4 rows in set (0.00 sec)

We can see that we have three root users and one system user.

• root@127.0.0.1 - Can connect using the loopback ipv4 network 127.0.0.1
• root@::1 - Can connect using the loopback ipv6 network ::1
• root@localhost - Can connect using the unix socket

Note that localhost in MySQL will mean connecting over the Unix socket, even if the hostname localhost resolves to IP address 127.0.0.1.

Application Users

We need to make MySQL users for our applications to use.

Let's pretend that our MySQL server is in a single region with networks:

• Public Ipv4: 159.203.81.145
• Private Ipv4: 10.132.30.23

And an application server in the same data center with networks:

• Public Ipv4: 104.131.100.163
• Private Ipv4: 10.132.51.34

Since these two servers are within the same private network (10.132.*.*), they can communicate to each other. Let's set the application server to be able to connect to the MySQL server.

We have a few tools we can use:

• Hostnames (example.com)
• Explicit IP addresses (192.168.10.10)
• Wildcards (192.168.10.%)
• Netmasks

Here we use an explicit IP address, some wildcards, and then some netmasks to see how they can be used:

-- From the MySQL Server, create a new user

-- Create a user that can only connect from this one server
CREATE USER 'my_app_user'@'10.132.51.34' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from any IP starting in `10.132.51.`
CREATE USER 'my_app_user'@'10.132.51.%' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from any IP starting in `10.132.`
CREATE USER 'my_app_user'@'10.132.%' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from any IP starting in `10.`
CREATE USER 'my_app_user'@'10.%' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from `10.132.51.*`
CREATE USER 'my_app_user'@'10.132.51.0/255.255.255.0' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from `10.132.*.*`
CREATE USER 'my_app_user'@'10.132.0.0/255.255.0.0' IDENTIFIED BY 'some-strong-password';

-- Create a user that can connect from `10.*.*.*`
CREATE USER 'my_app_user'@'10.0.0.0/255.0.0.0' IDENTIFIED BY 'some-strong-password';

The first example there is very specific - the user can only connect to MySQL if they are connecting from that one server at ip address 10.132.51.34.

The next few examples use wildcards (%). Wildcards use the same principles as LIKE matching in MySQL , and are best used to match hostnames (e.g. 'my_app_user'@'%.example.com').

Finally we have some netmask examples. Netmasks are specific to IPv4 network addresses (they don't work for IPv6 currently).

In a cloud like Digital Ocean or Linode, I create multiple users who can only connect from a specific IP address (no wildcard, no netmask), since all other options leave open the possibility that someone else's server can connect to your MySQL instance.

Public Networks

The concepts here work for a public network as well. If your database needs to be connected from other regions, then the private network won't work. In this case, you "should" look into setting up a VPN or SSL Tunnel, so a private network can be setup across regions.

However, we can get fairly secure without a VPN, which are complex to setup and maintain.

You can bind your MySQL to either all networks (if you also need private network access), 0.0.0.0, or to the public network, 159.203.81.145 in our example.

User setup is then the same - you can define the IP address or hostname the user can connect from.

-- Create a user that can only connnect from this one server
CREATE USER 'my_app_user'@'104.131.100.163' IDENTIFIED BY 'some-strong-password';

Firewall

On top of what MySQL provides, we can (should) also use our firewalls to protect us.

For example, we can set our firewall to only accept connections to port 3306 if they are coming to our private network (the destination is our private network):

# Append rule to the end of the INPUT chain
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -d 10.132.30.23 -j ACCEPT

# Or, insert it into the middle of our chain (position 3 here)
sudo iptables -I INPUT 3 -p tcp -m tcp --dport 3306 -d 10.132.30.23 -j ACCEPT

The -d flag sets the destination network.

Alternatively, we can set what network interface allows traffic to port 3306:

# Append rule to the end of the INPUT chain
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -i eth1 -j ACCEPT

# Or, insert it into the middle of our chain (position 3 here)
sudo iptables -I INPUT 3 -p tcp -m tcp --dport 3306 -i eth1 -j ACCEPT

This works well on Digital Ocean where the eth1 interface (-i) is your private network.

Linode, however, doesn't setup a network interface for the private traffic, so this firewall rule wouldn't work there.

And of course we can set rules to only allow traffic from specific IP addresses or networks, using both netmask and CIDR notation.

# Allow traffic from a specific IP
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -s 10.132.51.34  -j ACCEPT

# Allow traffic from a netmask range of IP addresses 10.132.51.*
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -s 10.132.51.0/255.255.255.0  -j ACCEPT

# Allow traffic from a CIDR range of IP addresses 10.132.51.*
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -s 10.132.51.0/24  -j ACCEPT

TL;DR

1. Bind MySQL to 0.0.0.0 if you want it to listen on all networks, private and public. Bind to the private network Ip address of the MySQL to listen only on the private network.
2. MySQL will listen on a local unix socket as well, used when connecting to localhost
3. Setup new MySQL users to be restrictive in where they can connect from - a specific IP address or as small a range of IPs as possible.
4. Use firewalls in conjunction with MySQL user restrictions

Resources

• More on MySQL User security
• Connecting to a remote MySQL server securely over SSH from your workstation
• MySQL bind-address, firewall and mysql users

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.