Tunnel MySQL Over SSH

by · Published · Updated

Assuming

On a “remote.com” server that we will connect to from our LocalMachine

1) MySQL database named “database”
2) MySQL database is running on remote server “remote.com”
3) MySQL is running and listening on (standard) localhost:port “127.0.0.1:3306”
4) MySQL username “mysql_user_name”
5) MySQL password “mysql_password”

6) SSH must be listening on port 9999
7) SSH AllowUsers “ssh_user_name”
8) SSH password “ssh_password”

other ancillary considerations – just so you know

9) remote.com is running Netfilter (iptables)
10) iptables has an open port 9999 on remote.com
11) remote.com port 3306 is closed by iptables

Create our ssh tunnel on our LocalMachine; type at the command line:

ssh -f ssh_user_name@remote.com -p 9999 -L 3306:127.0.0.1:3306 -N

* you should have setup GPG keys, so you will not be prompted for the ssh_password for ssh_user_name

Check/login to remote.com MySQL; type at the command line:

mysql -h 127.0.0.1 -P 3306 -umysql_user_name -pmysql_password database

You should get the mysql prompt (check that you are on remote.com server and not your LocalMachine) or something is not right.

Create persistent SSH tunnel for MySQL automatically at startup using autossh
sudo vi /etc/rc.local<pre>

<pre class='brush: bash'>su user_name_LocalMachine -c 'autossh -f ssh_user_name@remote.com -p 9999 -L 3306:127.0.0.1:3306 -N' &

For a more verbose and nicer read How To MYSQL over SSH Guide

Note:

< ? p h p
 $mysql = mysql_connect( "localhost:3306", "mysql_user_name", "mysql_password" );
 mysql_select_database( "database", $mysql ); 
? >

Tags:

You may also like...