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:
- MySQL Bind-Address - how/from where MySQL listens for connections
- MySQL User security - who can connect, from where
- Firewall - not MySQL directly, what network traffic the server allows in/out
MySQL Bind Address
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.
bind-address setting tells MySQL whether it can listen on a TCP socket for new connections. We have three basic ways to configure
- MySQL can bind to no networks (connect over
localhostonly, e.g. a unix socket)
- MySQL can bind to all networks (
- 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-addressis to listen on all networks! If
bind-addressis 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
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.
email@example.com- Can connect using the loopback ipv4 network
root@::1- Can connect using the loopback ipv6 network
root@localhost- Can connect using the unix socket
localhost in MySQL will mean connecting over the Unix socket, even if the hostname
localhost resolves to IP address
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: 18.104.22.168
- Private Ipv4: 10.132.30.23
And an application server in the same data center with networks:
- Public Ipv4: 22.214.171.124
- 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 (
- Explicit IP addresses (
- Wildcards (
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
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.
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.
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,
126.96.36.199 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'@'188.8.131.52' IDENTIFIED BY 'some-strong-password';
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
-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
- Bind MySQL to
0.0.0.0if 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.
- MySQL will listen on a local unix socket as well, used when connecting to
- 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.
- Use firewalls in conjunction with MySQL user restrictions
- 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.