Connecting to a Remote MySQL Server Securely

There are times when you need to connect to your MySQL or MariaDB remotely but don’t want to enable remote connections on the server. The solution is to use SSH tunneling with OpenSSH. SSH tunneling works by forwarding requests from a client port to the SSH server. The SSH server will then forward the request to the destination which can be itself or any other host the SSH server can access.

There are some downsides to using SSH tunneling and using ssh tunneling is not always the best option. First off, SSH is slow and there could be a noticeable delay if the connection is over the internet. The speed won’t play a large role if you are performing maintenance on the server or using the connection for non-production services such as a local website administration panel. If you have a production service that needs access to a remote MySQL server, it’s better to setup another MySQL master server on the same private network, or at the very least a slave server to speed up read queries.

Configuring the Server

There are a few different server layouts you may have that will change how the servers should be configured. If you have a MySQL server that also has an OpenSSH server installed, it’s unlikely you have to make any changes. Connections through the SSH tunnel will be treated as local requests and a local MySQL user will be used if the OpenSSH and MySQL server are on the same server.

In the case where your OpenSSH server has access to a MySQL server but the MySQL server itself doesn’t have OpenSSH (which is uncommon but not unheard of), you most likely have to perform some additional setup. A MySQL user has to be created that accepts remote connections from the same host as the SSH server. The MySQL server also has listen to a network interface that is accessible by the SSH server. At this point, additional security should be added to the MySQL server such as connection encryption, but that is outside of the scope of this tutorial. If you have this server setup, you most likely already have remote connections configured properly.

Connecting to the Remote MySQL Server

Now that the servers are configured, you can finally establish a connection to the remote database server. You will need to find the following IP addresses, ports, and in some cases Unix socket for the following commands. When you have a different SSH server and MySQL server, you want to use the IP address of the MySQL server in relation of the SSH server.

• Hostname or IP address of the SSH server.
• Hostname, IP address, or Unix socket of the MySQL server. In the case that the MySQL server and SSH server are the same server, the hostname localhost can be used.
• The port MySQL is listening on if you’re not using a Unix socket. The default MySQL port is 3306.

On the client, you now need to run the following command. The example below assumes your SSH server is “ssh.example.com” and you are signing in with the user “user”. We are forwarding all traffic from port 3333 on the client machine to the SSH server, which then forwards the traffic to “localhost” on port 3306.

$ ssh user@ssh.example.com -L 3333:localhost:3306 -N

This example shows us forwarding traffic from the local port 3333 to the MySQL Unix socket located on the OpenSSH server.

$ ssh user@ssh.example.com -L 3333:/var/run/mysqld/mysqld.sock -N

This final example forwards traffic from port 3333 to the OpenSSH server, which then sends the traffic to the host 10.0.0.5:3306.

$ ssh user@ssh.example.com -L 3333:10.0.0.5:3306 -N

The -L option specifies that connections to the given TCP port on the client host are to be forwarded to the given host and port or Unix socket. You can read about the -L option in the SSH documentation.

As for actually connecting to the remote MySQL server, you use the mysql client like you would any other time, but you have to specify the --host and --port options. The host will be the client machine you are currently on (i.e. localhost). The port is the first parameter of the -L option. In the above commands, we use the port 3333.

# mysql --host=localhost --port=3333 -u mysql-user -p

Keeping the Connection Alive

Your firewall, SSH, or a connection issue could disconnect you from the remote SSH server and you won’t have access to the remote MySQL server until you connect again. This section is only necessary if you want to keep the connection open for extended periods of time.

There are a few tools you can use such as autossh that will automatically restart SSH sessions and tunnels. This method is strong against connections that disconnect unexpectedly and a new connection will be established automatically.

Another option is to configure your SSH client to send an alive message after periods of time where no data is sent by using the SSH setting ServerAliveInterval on the client machine along with ServerAliveCountMax. The problem with this method is that if the connection is closed, a new connection will not automatically be reestablished.

A simple bash script can be used to continue reconnecting to the server once it has been disconnected, but if the ssh client were to hang, this solution would fail.

#!/bin/bash

while true; do
	SECONDS=0

	# SSH Connection
	# Requires Public Key Authentication
	ssh user@ssh.example.com -L 3333:localhost:3306 -N

	# If the connection closed too quickly, we sleep to prevent
	# high CPU usage.
	if [ $SECONDS -lt 5 ]; then
		sleep 5
	fi
done

If you are using the above bash script or autossh, you should configure Public Key Authentication without a passphrase to automatically connect to SSH. Configuring Public Key Authentication uses a private key stored on your device along with a public key stored on the server. These keys are used instead of passwords to securely authenticate users.