Connecting to your server's DB safely from your PC (SSH tunnel)

When you want to look at your server's DB with a program like HeidiSQL, DBeaver or MySQL Workbench, don't open the DB port (3306) to the internet. Automated login attempts from all over the world start coming in right away. Instead, an SSH tunnel lets you connect to the DB safely through the SSH access that's already open.

For installing a DB and creating an account just for your app, see Installing a database first.

Method 1. Your DB program's SSH tunnel feature (easiest)

Most DB programs have an SSH tunnel settings section.

HeidiSQL

1. New session → Network type: MariaDB or MySQL (SSH tunnel)
2. Settings tab: host 127.0.0.1, user and password are your DB account, port 3306
3. SSH tunnel tab: server IP and 22 for SSH host + port, username and password are your server SSH account, and the local port is an unused number such as 3307

DBeaver

1. New connection → MariaDB (or MySQL)
2. Main tab: Host 127.0.0.1, Port 3306, DB account and password
3. SSH tab: check "Use SSH Tunnel", server IP for Host, Port 22, and your server SSH account and password (or key file)

The two accounts are easy to mix up. Put your server login account in the SSH fields and your DB account in the DB fields.

Method 2. Open a tunnel with a command

Run this in a terminal (PowerShell) on your PC, and leave this window open.

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

• This means connections arriving at port 3307 on your PC are passed on to 127.0.0.1:3306 (the DB) inside the server.
• After you enter your password, it's normal for nothing to be printed and for it to just sit there.

Now connect from your DB program with host 127.0.0.1, port 3307 and your DB account. When you're done, close the tunnel with Ctrl + C in its window.

If you can't connect

• Access denied for user — Check that the DB account was created as 'user'@'localhost'. With a tunnel, the connection looks to the DB as if it comes from inside the server.
• SSH connection fails — First check that you can connect from a terminal by following the SSH connection guide.
• Port 3307 is already in use — If MySQL is installed on your PC, use a different number, such as 3308.

If your DB runs in Docker

If you bound the container port only inside the server, as in -p 127.0.0.1:3306:3306, you can use the methods above as they are. If you opened it with -p 3306:3306, it's open to the internet, ignoring ufw, so fix it right away. (See the firewall warning in the Docker guide)