MySQL · SSH · VPS · GUI

Connect a MySQL GUI to a VPS without opening 3306

pimvdmolen.nl · · ~6 min read

A VPS that runs MySQL should not accept database connections from the public internet. SSH on port 22 is already the admin path. Your desktop GUI can use that session and talk to MySQL on 127.0.0.1, so port 3306 never leaves the machine. In this how-to we cover that single-server case. If MySQL sits on a private host behind a separate jump box, use the bastion walkthrough in the MySQL GUI with SSH tunnel guide instead.

Leave 3306 off the public firewall

Opening 3306 so a GUI can connect is how scanners find MySQL. Brute-force tools try common accounts against any host that answers on that port. A cloud firewall rule and a host firewall rule are two different locks, and both should stay closed for 3306.

On Ubuntu or Debian, check UFW with sudo ufw status. Allow OpenSSH, or your custom SSH port, and do not add 3306/tcp. If an old allow rule is still there, remove it with sudo ufw delete allow 3306/tcp.

In the provider panel, inbound TCP 22 from your IP is enough. Add no inbound rule for 3306 or for the X Plugin port 33060. A process that listens on 0.0.0.0:3306 is still exposed when the cloud firewall allows it. A cloud rule that allows 3306 is still a problem if you later disable UFW.

Bind mysqld to loopback

Firewall rules fail open when someone flushes them. bind-address keeps MySQL off every public interface even then.

On Oracle MySQL packages the file is often /etc/mysql/mysql.conf.d/mysqld.cnf. On MariaDB it is often /etc/mysql/mariadb.conf.d/50-server.cnf. Set one line:

bind-address = 127.0.0.1

Leave skip-networking commented out. An SSH local forward connects with TCP to 127.0.0.1:3306. skip-networking removes that TCP listener, and the GUI tunnel has nothing to attach to.

Restart with systemctl restart mysql or systemctl restart mariadb, then confirm the listen address:

ss -ltnp | grep 3306

You want 127.0.0.1:3306. A line that shows 0.0.0.0:3306 or [::]:3306 means another file is winning. Debian-style packages load every snippet under /etc/mysql. Search with sudo grep -R bind-address /etc/mysql and fix the extra copy.

Match the account to 127.0.0.1

Through the tunnel, mysqld sees a TCP client at 127.0.0.1. It does not see your laptop IP. MySQL treats localhost and 127.0.0.1 as different accounts. localhost is the Unix socket. 127.0.0.1 is TCP. An account created as 'app_ro'@'localhost' will not accept the tunnel, and you get error 1045 with the correct password.

Create a TCP loopback account from the mysql CLI on the VPS:

CREATE USER 'app_ro'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-long-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_ro'@'127.0.0.1';
FLUSH PRIVILEGES;

Skip '%' unless some other host must connect, and on this setup none should. A GUI user manager can set the same host and grants. The create a MySQL user guide covers that screen. Use it after you are connected over the tunnel, or stay in the VPS shell for the first account.

MySQL 8 defaults to caching_sha2_password. A current desktop client handles that plugin over the tunnel. If an older client still fails after the host matches, that is the auth-plugin case in Fix MySQL 8 caching_sha2_password in a GUI, not a firewall miss.

Fill the GUI: public SSH host, local MySQL host

The fields people swap are Host and SSH host. With the tunnel enabled, Host is where MySQL listens as seen from the VPS, which is 127.0.0.1. SSH host is the name you already use in ssh [email protected].

TabulaSQL is one free desktop client that stores this on the saved connection. Workbench, DBeaver, and TablePlus use the same split. The steps below use TabulaSQL labels:

  1. Open New Connection and set the driver to MySQL / MariaDB. Name the profile after the VPS, not after the word localhost, or you will confuse it with a database on your laptop.
  2. Set Host to 127.0.0.1 and Port to 3306. Username and Password are the database account, here app_ro, not the Linux user.
  3. Enable Connect through SSH tunnel. Set SSH host to the VPS public DNS name or IP, SSH port to 22 (or your custom port), and SSH user to the Linux account.
  4. Choose private key authentication and point at the OpenSSH private key, usually ~/.ssh/id_ed25519. Do not select the .pub file. Save, then connect.

HeidiSQL on Windows often starts PuTTY's plink.exe for the same destination. If the in-app tunnel is awkward there, the OpenSSH and PuTTY forward uses 127.0.0.1:3306 on the far side and a local port such as 3307.

Test the Linux login before you blame the GUI: ssh -i ~/.ssh/id_ed25519 [email protected]. A failure in that command is an SSH problem. The MySQL form cannot fix it.

You can download TabulaSQL from https://tabulasql.com/download if you want the tunnel stored beside the MySQL login instead of a second terminal window.

Prove the internet cannot see 3306

From your laptop, not from the VPS, probe both ports:

nc -vz vps.example.com 22
nc -vz -w 5 vps.example.com 3306

Port 22 should connect. Port 3306 should time out or refuse. A success on 3306 means a firewall rule or bind-address change did not stick. Close that before you browse production tables.

Inside the GUI, run SELECT USER(), CURRENT_USER();. You want [email protected]. If CURRENT_USER() shows a % host, you logged in with a wider account than the one you meant to create. Drop or lock that wider account once the loopback user works.

Tunnel up, query still fails

SSH can succeed while MySQL refuses. Read the error code before you change the firewall again.

Error 1045 is host or password. On the server, SELECT user, host FROM mysql.user WHERE user = 'app_ro'; should list 127.0.0.1. A row for localhost only will not match this TCP path. Fix the account. Do not open 3306 to "test from outside."

Error 2003, or a timeout after SSH already authenticated, means mysqld is not listening on 127.0.0.1:3306, or the MySQL host field still contains the public IP. Loopback binding does not accept connections aimed at the public address, and your laptop should not be able to aim at 3306 either.

Permission denied (publickey) is the key path, a missing passphrase prompt, or a public key that is not in ~/.ssh/authorized_keys for that Linux user. chmod 600 on the private key if the client or ssh complains about permissions.

When SELECT 1 returns a row, leave 3306 closed and use this connection for queries and schema browsing. If the database later moves to a private IP on another machine, change only the MySQL host field and follow the bastion guide. The inbound rule for 3306 stays absent.

Frequently asked questions

No. Leave 3306 closed in the cloud firewall and on the host. Bind mysqld to 127.0.0.1 and connect the GUI through an SSH tunnel on port 22.

The tunnel arrives as TCP from 127.0.0.1. An account created as user@localhost uses the Unix socket and does not match. Create [email protected] with the grants you need.

Host is 127.0.0.1 and the MySQL port is 3306, because that is where MySQL listens on the VPS. SSH host is the public VPS name or IP, with your Linux user and private key.