Accessing Self-Hosted MySQL Externally
Configure a self-installation of MySQL on Ubuntu to allow access from external sources, such as SequelPro or other GUIs.
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:
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.
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.
Grant all privileges to the new user, and always
flush privileges after making such modifications.
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
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.