This article shows how to setup a permanent SHH tunnel between a webserver and a database server running MySQL. First of all we will create two new users, one on each server. Of course you may also use existing users, but I prefer to have users dedicated just for the tunneling job. So we will start with the database server, where we create a new user and enable him to login via Publickey Authentication by editing “sshd_config”-file:

adduser ssh-tunnel
nano /etc/ssh/sshd_config

Now add the user to “AllowUsers” and set “PubkeyAuthentication” to “yes”:

AllowUsers ssh-tunnel
PubkeyAuthentication yes

Then restart SSH:

/etc/init.d/ssh restart

Now lets switch to the webserver and create a new user as well (also install autossh if not done already):

aptitude install autossh
adduser ssh-tunnel-mysql

Now add the user to “AllowUsers” just as done before with the new user on the database server:

AllowUsers ssh-tunnel-mysql

Also restart SSH here:

/etc/init.d/ssh restart

Now login to the webserver with the newly created user “ssh-tunnel-mysql”. Next you can already try to open a tunnel. But make sure to replace SSH port, MySQL port and IP according to your configuration:

/usr/bin/autossh -M 20042 -N -L 3308:127.0.0.1:3306 -p 22 ssh-tunnel@1.2.3.4

You might get the following error:

Warning: remote port forwarding failed for listen port 20042

It means your port is in use. In this case just change “20042” to an unused port. Once you have established the tunnel it’s time to test it. Do this in a new console window and make sure to use the correct password for the “ssh-tunnel” user:

mysql -h 127.0.0.1 -P 3308 -ussh-tunnel -pPASSWORD

Now you should be connected and able to work with the MySQL database on the database server. Try the following, it should list all your databases that exist on the database server:

SHOW DATABASES;

Since we have confirmed that the tunnel is working we can now create a public key to enable logon without a password. Make sure to execute the following commands on the webserver as the tunnel user (leave the passphrase empty when asked for it):

cd ~
mkdir .ssh
ssh-keygen -t dsa -b 1024 -f ~/.ssh/ssh-tunnel-key

Now you created a public/private key pair on the webserver. Next the public key has to be enabled inside the authorized_keys file on the database server. For this you need to login to the database server as the tunnel user and execute the following commands:

cd ~
mkdir .ssh
chmod 700 .ssh
cd .ssh/
touch authorized_keys
chmod 600 authorized_keys

Now everything is setup on the database server. So we can go back to the webserver and copy the key. Again make sure to be logged in as the tunnel user and change SSH port and IP to yours:

cat ~/.ssh/*.pub | ssh ssh-tunnel@1.2.3.4 -p 22 'umask 077; cat >>.ssh/authorized_keys'

Now test your connection again, this time by using the key:

/usr/bin/autossh -M 20042 -N -L 3308:127.0.0.1:3306 -p 22 
-i /home/ssh-tunnel-mysql/.ssh/ssh-tunnel-key ssh-tunnel@1.2.3.4

Everything working without a password now? Then you can put the tunnel in the background by using the parameter “-f”. This way your tunnel will remain active even when you close your console window:

/usr/bin/autossh -M 20042 -f -N -L 3308:127.0.0.1:3306 -p 22 
-i /home/ssh-tunnel-mysql/.ssh/ssh-tunnel-key ssh-tunnel@1.2.3.4

If you want to close the tunnel you can use the “kill”-command with the PID of the tunnel or if you are using the tunnel user just for the case of tunneling by simply executing “killall”:

killall -u ssh-tunnel-mysql

Thats it. If anything is missing, wrong or any problems occur please let me know via my contact form.