Connect to MySQL Remotely

This is an advanced tutorial.

Careful! We cannot provide any assistance for this process nor can we help with any problems caused by it. Be sure to take a full-server backup before proceeding!

It can sometimes be useful to enable remote connections to MySQL. This allows you to directly connect to MySQL on one of your servers from an application or MySQL client running on a different system.

There are two different ways to access MySQL remotely:

Using an SSH tunnel.
Enabling direct access to MySQL from remote systems.

Connecting to MySQL Using an SSH Tunnel

Using an SSH tunnel is the easiest and safest option for remote MySQL access for development purposes. An SSH tunnel will make it look like MySQL is running on your local system. SSH does this by opening a local port and seamlessly transferring all of the communication over that port through to MySQL running on your server.

To create an SSH tunnel from Mac or Linux, you can run the command line SSH command with the -L to enable local port forwarding.

ssh -L localhost:2000:localhost:3306 serverpilot@SERVER_IP_ADDRESS

In the example above, SSH would open port 2000 on your local system, and you can then communicate with MySQL by connecting to this local port. For example, you can connect using the MySQL command:

mysql --protocol=tcp --host=localhost --port=2000 --user=DB_USER_NAME -p

Don't leave off --protocol=tcp or MySQL may silently ignore the --host and --port options.

You can also create SSH tunnels on Windows using PuTTy, the popular Windows SSH client.

Enabling Direct Access to MySQL from Remote Systems

Warning! Allowing direct access to MySQL increases the risk of your server being compromised by attackers.

Warning! MySQL does not use secure connections by default. The MySQL manual has more info on SSL with MySQL.

Step 1: Configure MySQL to listen on all interfaces.

By default, MySQL only listens for connections on localhost, so the first step is to reconfigure MySQL on your database server to listen on all interfaces. To do this, log in to your server as root.

If you are running an Ubuntu 16.04 or 18.04 server, edit this file:

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

For 14.04 or 12.04 servers, edit this file:

/etc/mysql/my.cnf

Find the following line in either file:

bind-address = 127.0.0.1

Change the bind-address to 0.0.0.0:

bind-address = 0.0.0.0

Save this file and then run the following command as root:

sudo service mysql restart

Step 2: Open TCP port 3306 in your server's firewall.

The firewall configured by ServerPilot blocks all access to MySQL from outside of your server. So, you will need to customize your firewall to allow access to MySQL.

To open up access to MySQL from outside of your server, see our article on customizing your firewall.

Additional Step for EC2: Allow TCP port 3306 in your security groups.

If this is an EC2 server, you must also allow port 3306 in your server's security groups.

Configure Your App(s) to Use the Remote Host.

Once you’ve set up the remote MySQL server, you’ll want to configure your app(s) to use that host for database transactions. This is typically set up in the app’s configuration file, and we’ll use WordPress as our example.

Open the app’s wp-config.php file.

On this line:

define('DB_HOST', 'localhost');

Change ‘localhost’ to your remote MySQL server’s IP address or full hostname. The IP address will typically be a faster and more reliable choice, as it does not rely upon DNS resolution.

Save the file and exit, and you’re all set! You can now test your app to make sure it is working correctly.

Last updated: March 25, 2019