Introduction
It is common security practise to allow access to your MariaDB/mySQL DB server only from localhost or, which is somehow the same, via ssh tunnel.
Problem statement
For database configuration I usually use HeidiSQL on Windows as a admin front-end because it has all the required features and a build in Putty (Plink.exe) SSH connection manager. This makes it easy to configure the remote SSH access and the database authentication in one step.
From time to time I code some scripts running on remote machine that require access to the database server (port: 3306). In order to integrate the SSH access to the database in some shell scripts I wanted to have a working plink shell statement.
Here it is:
Code
plink.exe -ssh -l <username> 192.168.0.x -P 22 -N -L 127.0.0.1:3307:127.0.0.1:3306 -noagent -batch -v -pw <password>
This statement „maps“ the local TCP Port 3306 to the local TCP Port 3307 on the remote machine. So when the script want’s to connect to the database just connect to the localport 3007 (127.0.0.1:3007)
Please pay attention to the -batch command here. This comand switches to the non-interactive mode so there’s no need to manually confirm the connection.
With the -v switch for verbose output, you can see the following output.
Connecting to 192.168.0.x port 22 We claim version: SSH-2.0-PuTTY_Release_0.70 Server version: SSH-2.0-OpenSSH_7.4p1 Debian-10+deb9u6 Using SSH protocol version 2 Doing ECDH key exchange with curve Curve25519 and hash SHA-256 Server also has ecdsa-sha2-nistp256/ssh-rsa host keys, but we don't know any of them Host key fingerprint is: ssh-ed25519 256 aa:ff:ff:ee:aa:ff:ff:ee:aa:ff:ff:ee:aa:ff:ff:ee Initialised AES-256 SDCTR client->server encryption Initialised HMAC-SHA-256 client->server MAC algorithm Initialised AES-256 SDCTR server->client encryption Initialised HMAC-SHA-256 server->client MAC algorithm Using username "<username>". Sent password Access granted Local port 127.0.0.1:3307 forwarding to 127.0.0.1:3306 Opening connection to 127.0.0.1:3306 for forwarding from 127.0.0.1:55784
Attention: Of course you wanna have some key authentication for you server. So it is recommendeted to switch the authentication from username/password to private key.