How to connect to a remote MySQL Database with SSH

MySQL databases are the backbone of the internet. But developing with them can be a pain if you can’t afford to run them locally. This solution allows you to connect to your remote MySQL database without any extra setup.

I recently had to expand my knowledge and build my own API, rather than using a freely available one. But the hardest part wasn’t making the API itself. It was connecting with my Database. But why?

A default MySQL installation is likely to block any connection requests happening from outside your server. And there’s a good reason for this. Allowing anyone to connect to your server would be a huge security flaw.

Unfortunately this also means that if you intend to work with Node.js and develop locally, you will probably have a hard time retrieving your data.

How to connect to a remote mySQL database

My solution is actually amazingly simple, and unlike with many guides online, it doesn’t require you to mess with MySQL’s settings.

Simply open up your terminal interface – if you’re on Windows I highly suggest you set up WSL if you haven’t done it already – and simply launch the command:

ssh username@server -L 3306:127.0.0.1:3306 -N

You’ll be prompted to enter the password you set up for your user, after entering you’ll be all ready to go.

Here’s what’s happening here:

  • SSH is the command we use to connect to a remote machine. You would usually be able to use this to access your server’s command line interface.
  • username@server are identifying the address of our server and the username we use to access it. The address of the server can be an IP, or even a domain that’s been connected to the IP.
  • -L 3306:127.0.0.1:3306 -N This means we’re binding our -L local port 3306 (the default port Mysql runs on) to the remote IP 127.0.0.1 and -N forwarding (or opening) port 3306

In Layman’s terms this means that our PC is now going to act as if it had a MySQL instance running locally.

And there you have it. Now you can just start any local server and connect to our database by using localhost and the usual username/password combination we would usually use! Neat, huh?

0