If your MySQL server is behind a firewall or not listening on remote networks, we can still securely connect to it from a local computer!

Here we learn how to use SSH tunnels to communicate to a remote MySQL instance.I log into this server using ssh -o "IdentitiesOnly yes" -i ~/.ssh/some-key user@hostname. As with most videos where I'm using SSH keys, I define both the identity file and say IdentitesOnly "yes" to ensure SSH uses the one SSH key I have setup.

We can use a very similar SSH-based connection to connect to a remote MySQL server securely!

The Situation

We want to connect to MySQL remotely. However, we cannot as the server is right now. What's preventing us from connecting:

1. Firewall rules dropping port 3306 connections (Also possibly AWS's security in this case)
2. MySQL only listening on localhost (127.0.0.1) network
3. No MySQL's user is defined which allows connections from remote hosts

We can, however, still connect MySQL from our local computer. We can securely get around these limitations by creating an SSH tunnel into the remote server!

SSH Tunnel (Local connection)

Lets say you want to connect to a remote MySQL server from a local MySQL management application. We can securely do this using an SSH tunnel.

Sequel Pro and similar applications do this for you when you select to connect over SSH. We'll do this "manually" here so you can see how it works.

This method lets you keep your MySQL server running securely behind firewalls and only listening for localhost connections. What we do is "tunnel" into the server, communicating over the SSH connection.

This is also covered in the ssh tricks article.

The following will forward local port 3333 to the remote server's localhost:3306 socket, where MySQL is listening!

ssh -L 3333:localhost:3306 username@hostname

Once that's running, you can connect to MySQL locally using localhost as the host and 3333 as the port! If you have a MySQL cli client installed, the command would be mysql -h localhost -P 3333 -u some_user -p.

When I created the tunnel, I still defined the IdentitiesOnly option and said which Identify file to use, like so:

ssh -o "IdentitiesOnly yes" -i ~/.ssh/fideloperllc.pem \
    -L 3333:localhost:3306 username@hostname

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.