Accessing MySQL Externally

For those with self-hosted MySQL instances

In the previous post we got familiar with the basics of creating and navigating MySQL databases. The next most common thing to ask might be "great, but how can I use this in any meaningful way?"

MySQL installations default to refusing connections outside of the local machine's IP address, as we should expect. That said, relational databases aren't usually being used by a single person on a single machine forever (but if you do, we should hang out). It goes without saying that our MySQL instance should be focusing on uptime and accessibility, or in other terms, far away from our destructive personalities.

I adore maintaining databases in the command line as much as the next self-hating masochist, but we'll need to accomplish work at some point, which means that remote database we just set ho 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 webserver 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.

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
# http://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

By default, bind-address is set to your local host. This is basically a whitelist that allows changes only from the domains or IP addresses specified. You can go ahead and add the address of the external domain you'd like to grant access to here.

Commenting out the line completely opens up MySQL to everybody. So there's that.

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, 'newuser' 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 ‘newuser’@‘%' IDENTIFIED BY ‘password123’;

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

mysql> GRANT ALL ON *.* to [email protected]'%' IDENTIFIED BY 'password123';
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.

service mysql restart

Assuming this was done correctly, your DB should now be able to receive read/write queries from an external source, provided the correct username and password are used.

Author image
New York City Website
Product manager turned engineer with an ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.

Product manager turned engineer with an ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.