Connect to your remote database using ssh tunnel

You have a database on a remote server and you want to access it from your local machine, get some insights, and maybe execute some commands directly on the database. You can ssh into the remote server, login to your database, and then run your commands. I find this approach very difficult and I can't have a general overview of my tables the way database clients (like TablePlus, Sequel Pro, and MySQL Workbench) provide for me. So how can we solve this issue and make it a lot easier to connect to remote databases from our local machine the same way we connect to our local databases? the solution is to use ssh tunnel that forwards connections from a specific port on your local machine to the port your database is listening on your database server.

Configuring ssh tunneling on your database client

Here I will use Tableplus as a database client, but this should work with other database clients (I know it works for MySQL Workbench and Sequel Pro).

This is a listing of the fields (along with the value set in the image above) and some explanation of each:

• Connection's Name (Title): this could be anything, it is just an identifier to make it easy for you to distinguish connections.
• Database (MYSQL): The type of database you want to connect to (MySQL, MariaDB, Postgresql, SQLite, etc.)
• DB Host (127.0.0.1): This should always be your localhost (127.0.0.1) the reason is that your database client (TablePlus in this case) will create an ssh connection and then forward a port you specify (next field) from your local machine to the remote database server via ssh. So you always want to set the database host to 127.0.0.1 .
• DB Port (3306): The port on the remote machine that accepts connections to the database (the default is 3306 for MySQL).
• DB username (db_username): the database user that has access to the remote database you want to connect to.
• DB password (db_password): the password of the previous user (db_username).
• DB name (db_name): the database you want to connect to.
• SSH Tunnel: this is where the ssh connection is set up, you want to toggle on this to be able to set the ssh connection details.
• Server address (db_server_host): the address of the server your database is hosted on (you can put the domain name or the ip address).
• SSH port (22): The port that accepts ssh connections on your database server (22 by default).
• Server username (db_server_username): the username you want to ssh to the server as.
• SSH private key: the private key that corresponds to the public key you added to your database server (usually on ~/.ssh/ ) make sure to select the private key (the one without a .pub extension).
• Passphrase: if you added a passphrase for your key, this is where you should put it. Leave it blank if you didn’t add a passphrase.

After filling these fields you should be able to connect to your remote database and see all the tables just as you would for a local database.

This is a secure way for connecting to your remote databases from your local machine. It uses ssh keys to encrypt traffic between your local machine and the remote server.