Managed databases are great. We've spent the last decade spinning up and monitoring databases through GUIs, and can be hard to imagine doing databases any other way. But here's the thing. If we were to imagine returning to old-school database management in such a way where we weighed the benefits and costs objectively, I think a lot of us would realize that when it comes to added value, managed databases aren't nearly as great as they are overpriced. Don't mistake this perspective as a call to arms: there are plenty of scenarios where features like automated backups are worth the cost. After spending the last few years powering a kinda-shitty blog with various managed dbs, I can say I'm not living in one of those scenarios. At my current bandwidth usage, a low-end Google Cloud SQL server costs me 100 dollars per month: that's a lot of money for a product with no revenue stream. This post is dedicated to the devs who want to build an empire on a 20 dollar Digital Ocean droplet, microservices be damned.

As you're waiting for apt-get install mysql-server to finish installing, let's address the dolphin in the room: MySQL doesn't allow external connections by default. I adore interacting with MySQL databases via the command line shell as much as the next self-hating masochist, but we'll need to accomplish work at some point. That means the remote database we just set up needs to be open-minded enough to allow a connection from, say, the IP address of our personal local machine, which happens to have a sexy GUI installed for this very purpose.

Making these kinds of configuration changes to any service or web server is always a bit of fun. You think your day might suck until you cone home and a piece of software treats you like a cyber criminal, kicking and screaming while we attempt the most basic out-of-the-box functionality.

The fine print here is that we wouldn't recommend messing with any of these settings unless you know what you're doing. Then again, if you knew what you were doing you probably wouldn't be reading this. The point is, if you mess up, it's your fault because we warned you.

Modifying Our MySQL Config

The first thing we'll need to touch is the MySQL config found here on Ubuntu:

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Here you can set various configurations for MySQL, such as the port number, default user, etc. The line we're interested in is bind-address.

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# https://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address           = 127.0.0.1

bind-address tells MySQL to only open connections with the provided IP address. By default this value is set to your localhost, thus rendering MySQL inaccessible to anything which isn't the machine it's running on. Unfortunately bind-address can only accept a single value, so we can't use it as a whitelist. The only option we have is to open MySQL to everybody, which is extremely dangerous on its own. We'll cover how to properly whitelist IPs in a moment.

Create a New User

Now we need to create a user with which to access the DBL:

mysql -u root -p -h localhost -P 3306

Use the CREATE USER command to create a new homie. In the example below, 'myuser' is the name of the new user, and '%' is from which location the user will be permitted to make changes. This is usually 'localhost', for example. In this case, we added '%' which means everywhere.

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'password';

Grant all privileges to the new user, and always flush privileges after making such modifications.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'todd'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

With these changes made, restart MySQL.

$ sudo service mysql restart

Checking Your Firewall Settings

Your MySQL instance is now configured to accept outside connections, but there's a very good chance your server's firewall will block any incoming connections on the port MySQL listens to. MySQL listens to port 3306 by default, which I would highly advise changing if you're going to go through with exposing your database. Most people leave their MySQL port listening to the default, which makes it most susceptible to attacks.

To check the ports your server allows, execute ufw status:

Status: active

To                         Action      From
--                         ------      ----
22/tcp                     LIMIT       Anywhere                  
Nginx Full                 ALLOW       Anywhere                  
22/tcp (v6)                LIMIT       Anywhere (v6)             
Nginx Full (v6)            ALLOW       Anywhere (v6)  

Your server's firewall will almost certainly match the above by default, where only port 22 is open (the port we use to SSH). Check he port number your MySQL instance is listening on in your MySQL config, and open this port via ufw allow [YOUR_PORT]. Now we can check ufw status again:

Status: active

To                         Action      From
--                         ------      ----
22/tcp                     LIMIT       Anywhere                  
Nginx Full                 ALLOW       Anywhere                  
3306                       ALLOW       Anywhere                  
22/tcp (v6)                LIMIT       Anywhere (v6)             
Nginx Full (v6)            ALLOW       Anywhere (v6)             
3306 (v6)                  ALLOW       Anywhere (v6)  

Assuming this was done correctly, your DB should now be able to receive read/write queries from an external source.

Again, I'd can't stress enough that you take the proper precautions when exposing your database to the internet. Utilize the IP whitelist as well as enforcing some sort of SSL on all incoming connections. You will almost surely be hacked otherwise... although perhaps "hacked" isn't the right term. If you tore all the doors off your house and left it unattended forever, the ensuing events would speak more to your benevolent charitable contributions to society rather than a stranger's act of robbery.