Connect MySQL Workbench, HeidiSQL or DBeaver to your VPS over SSH

Desktop clients such as MySQL Workbench, HeidiSQL and DBeaver make it easy to browse tables, run queries and export data. The safe way to connect them to a VPS is through an SSH tunnel: the client logs in over SSH, then talks to MySQL on the server's own 127.0.0.1. Port 3306 stays closed to the internet, and all traffic is encrypted by SSH.

Before you start

• A Linux VPS running MySQL or MariaDB, listening on 127.0.0.1 (the default on most installs).
• SSH access to the VPS. Key-based login is strongly recommended; see Set up SSH keys.
• Your server's IP address, from your VPS provider's control panel. This guide uses 203.0.113.10 and the SSH user deploy.
• A MySQL account for the client. Do not use root for day-to-day work.

Why not open port 3306?

Opening 3306 lets anyone on the internet try passwords against your database. Bots scan for it all the time. With a tunnel, only people who can log in over SSH can reach MySQL at all. Check that MySQL is bound to localhost only:

sudo ss -tlnp | grep -E '3306|mysqld|mariadbd'

You want to see 127.0.0.1:3306. If it shows 0.0.0.0:3306 or *:3306, set bind-address = 127.0.0.1 in the server's config file and restart MySQL. Keep 3306 closed in your firewall either way.

Create a MySQL user for the client

Log in on the server with sudo mysql (or mysql -u root -p) and create an account that connects from the local machine. Choose your own strong password; the one below is only a placeholder:

CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY 'REPLACE_WITH_YOUR_OWN_PASSWORD';
CREATE USER 'dbadmin'@'127.0.0.1' IDENTIFIED BY 'REPLACE_WITH_YOUR_OWN_PASSWORD';
GRANT ALL PRIVILEGES ON shop.* TO 'dbadmin'@'localhost';
GRANT ALL PRIVILEGES ON shop.* TO 'dbadmin'@'127.0.0.1';

Both host entries are created because a tunnelled connection arrives over TCP at 127.0.0.1, and depending on server settings MySQL may match it as either name. Limit the grants to the databases you actually need. For more on permissions, see Create MySQL users and grant privileges.

MySQL Workbench

1. Open Workbench and click + next to MySQL Connections.
2. Set Connection Method to Standard TCP/IP over SSH.
3. Fill in the SSH fields:

• SSH Hostname: 203.0.113.10:22 (use your SSH port if you changed it)
• SSH Username: deploy
• SSH Key File: your private key, for example C:\Users\you\.ssh\id_ed25519

1. Fill in the MySQL fields:

• MySQL Hostname: 127.0.0.1
• MySQL Server Port: 3306
• Username: dbadmin

1. Click Test Connection, enter the MySQL password when asked, and save.

Workbench is built for MySQL. It can connect to MariaDB but may show a compatibility warning, and some features may not work.

HeidiSQL (Windows)

1. Click New in the Session manager.
2. Set Network type to MariaDB or MySQL (SSH tunnel).
3. On the Settings tab, set Hostname / IP to 127.0.0.1, User to dbadmin, the password, and Port 3306.
4. On the SSH tunnel tab:

• SSH executable: plink.exe (from PuTTY) or the Windows OpenSSH ssh.exe, depending on what your HeidiSQL version offers.
• SSH host + port: 203.0.113.10 and 22.
• Username: deploy.
• Private key file: your key. plink needs a .ppk key; ssh.exe uses a normal OpenSSH key.
• Local port: any free port, such as 3307.

1. Click Open.

DBeaver

1. Click New Database Connection and choose MySQL or MariaDB.
2. On the Main tab, set Server Host to 127.0.0.1, Port to 3306, and enter the username and password.
3. Open the SSH tab and tick Use SSH Tunnel.
4. Set Host/IP to 203.0.113.10, Port to 22, User Name to deploy, and Authentication to Public Key. Point it at your private key file.
5. Click Test Connection, then Finish. DBeaver may offer to download the database driver the first time.

Any client: a manual tunnel

If your tool has no SSH option, open the tunnel yourself from your computer, then point the client at 127.0.0.1:3307:

ssh -N -L 3307:127.0.0.1:3306 deploy@203.0.113.10

-L forwards local port 3307 to port 3306 on the server, and -N keeps the session open without running a shell. Leave the window open while you work and press Ctrl + C to close the tunnel.

Check it works

• The client's test button reports success and you can see your databases.
• On the server, SHOW PROCESSLIST; lists your client session with host localhost or 127.0.0.1.
• From your computer, port 3306 on the server's public IP does not answer:

  nc -vz -w 5 203.0.113.10 3306

A timeout or "refused" result is what you want.

Troubleshooting

• "Access denied for user": the account does not exist for the host MySQL sees. Create it for both localhost and 127.0.0.1, as shown above, and check the password.
• SSH step fails: test plain SSH first with ssh deploy@203.0.113.10. If that fails too, see Fix SSH connection refused or timed out.
• DBeaver says "Public Key Retrieval is not allowed": MySQL 8 accounts using caching_sha2_password need it. Open Driver properties and set allowPublicKeyRetrieval to true. The SSH tunnel already encrypts the traffic.
• HeidiSQL cannot read the key: plink only accepts .ppk files. Convert your key with PuTTYgen or switch the SSH executable to ssh.exe.
• "Can't connect to MySQL server on 127.0.0.1": MySQL is not running or listens on another port. Run sudo systemctl status mysql (or mariadb) on the server.

Next steps

• Harden the database server itself: Secure MariaDB and MySQL after install.
• Make SSH logins quicker with host aliases: Use an SSH config file for shortcuts.