Management and access to remote databases is a daily requirement for developers, data analysts, and operations teams. However, if you directly expose the public network to database ports such as MySQL 3306 and PostgreSQL 5432 in use, this will cause your server to face serious security threats. SSH protocol is the use of encrypted tunnel technology to remote database access to establish an indestructible security line. So what is the core principle of SSH connecting to a database? How should the whole process work?

First, the core principle of SSH database connection

The core principle of SSH database connection is that the SSH tunnel uses port forwarding to encapsulate database traffic in an encrypted channel for transmission, avoiding direct plaintext data exposure to the public network. The working principles are as follows: Local Port Forwarding. Create a listening port on the local computer and forward all requests to the port to the specified port on the remote server through the SSH tunnel.

Command structure:

ssh L [local port]:localhost:[remote database port] [User name]@[server IP address] N

Example:

ssh L 3306:localhost:3306 user@example.com N

In this case, local access to localhost:3306 is equivalent to connecting to the MySQL service on the remote server.

Dynamic Port Forwarding is a SOCKS proxy created to allow traffic on any port to be transmitted through the SSH tunnel. It is suitable for the scenario with multiple services or uncertain ports.

Command structure:

ssh D [local port] [User name]@[server IP address] N

Application scenario: Work with a database client tool (such as DBeaver) that supports the SOCKS proxy to achieve flexible connection.

Second, the mainstream database SSH connection practice

MySQL/MariaDB connection. Establish an SSH tunnel first

ssh L 3307:localhost:3306 user@example.com N

(Map remote port 3306 to local port 3307)

Connect to the database locally on the connection. Using a MySQL client or tool (such as MySQL Workbench) :

Host: 127.0.0.1

Port: 3307

Username/Password: specifies the authentication information of the database

Configuration file optimization (~/.ssh/config) :

Host mysqltunnel
HostName example.com
User user
LocalForward 3307 localhost:3306
IdentityFile ~/.ssh/id_rsa

Run the ssh mysqltunnel N command to start the tunnel.

PostgreSQL connection. Creating an SSH Tunnel

ssh L 5433:localhost:5432 user@example.com N

Connect using the psql client

psql h 127.0.11p 5433 U postgres

For graphical tool configuration (such as pgAdmin), enter SSH host information in the SSH Tunnel TAB. The General TAB sets the database address to localhost and the port to the remote database port.

The MongoDB connection starts the tunnel first

ssh L 27018:localhost:27017 user@example.com N

Use the mongo shell to connect

mongosh host 127.0.0.1 port 27018

GUI tool configuration (such as MongoDB Compass). Connection string:

mongodb://localhost:27018

Enable the SSH tunnel and fill in the server credentials

Third, automation and security hardening strategy

Key authentication replaces password login. Generate a key pair:

sshkeygen t ed25519 C "databaseaccess"

Upload public key to server:

sshcopyid i ~/.ssh/id_ed25519.pub user@example.com

Disable password login (server side). Modify /etc/ssh/sshd_config:

PasswordAuthentication no

Persistent SSH tunnel to prevent interruption, using autossh automatic reconnection:

autossh M 0 o "ServerAliveInterval 30" L 3306:localhost:3306 user@example.com N

Configure the system service (for example, systemd). Create the/etc/systemd/system/dbtunnel. Service:

ini
[Unit]
Description=MySQL SSH Tunnel
After=network.target
[Service]
User=your_local_user
ExecStart=/usr/bin/autossh M 0 N L 3306:localhost:3306 user@example.com
Restart=always
RestartSec=10
[Install]
WantedBy=multiuser.target

Enable the service:

sudo systemctl enable now dbtunnel

Firewalls and access control. Restrict SSH access to IP addresses. Edit /etc/ssh/sshd_config on the server:

AllowUsers user@192.168.1.0/24

Database whitelist. MySQL example:

sql
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON db. TO 'app_user'@'localhost';

Four. Troubleshooting and performance optimization

Connection timeout Check SSH service status:

systemctl status sshd

Verify port open:

nc zv example.com 22

Permission denied, confirm private key permission:

chmod 600 ~/.ssh/id_ed25519

Check database user permissions:

SHOW GRANTS FOR 'user'@'localhost';

If a tunnel has been established but cannot connect to the database, verify whether the database is listening to the local:

netstat tuln | grep 3306

Check firewall rules:

sudo ufw status

Performance tuning. Compress the transmitted data:

ssh C L 3306:localhost:3306 user@example.com N

To multiplex SSH connections, change ~/.ssh/config:

Host
ControlMaster auto
ControlPath ~/.ssh/ssh_mux_%h_%p_%r
ControlPersist 1h

SSH is a remote login tool and the basis of secure data channel, the above is about the SSH remote login basic tunnel construction and security architecture of the correct method, suitable for development environment debugging, production environment operation and maintenance.