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
3306(the default port Mysql runs on) to the remote IP
-Nforwarding (or opening) port
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?