Tunneling MySQL Over SSH

How to Make MySQL Traffic Secure

BY 

Ionel Roiban

Introduction

The standard configuration of MySQL is not secured and it is set like that for performance reasons.
In most of the cases you connect to the SQL on the same machine or within a secure local network. Things change dramatically when working with the MySQL server remotely.
When connecting to the server the password is encrypted but the encryption algorithm is not very strong (MySQL Reference Manual - Connection Verification) . After the connection is established everything else is transmitted in clear text or, if enabled, compressed. Everybody on the network can sniff the data you're reading from or sending to the server.
The first solution to this problem, use SSL ( MySQL Reference Manual - Using Secure Connections) .
If the data requires stronger protection keep it encrypted in the storage ( MySQL Reference Manual - Encryption Functions) .
With SSH all data, including passwords, will be transferred in encrypted format using a strong authentication protocol and we will feel much safer when working with a MySQL database through insecure channels, like the Internet.

Making an Automatic SSH Tunnel

One of the most useful feature of SSH is tunneling (SSH Manual) . To set up a connection with a remote host using tunneling you start with these commands:

ssh -fNg -L 3307:127.0.0.1:3306 username@remotehost
mysql -h 127.0.0.1 -P 3307 -u user -p

-f: Requests ssh to go to background just before command execution. This is used when ssh is going to ask for passwords or passphrases, but the user wants it in the background. This command implies -n.

-N: Do not execute a remote command. This is used for for- warding ports.

-g: Allows the remote hosts to connect to local forwarded ports.

-L port:host:hostport: Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side. This works by allocating a socket to listen to port on the local side, and whenever a connection is made to this port, the connection is forwarded over the secure channel, and a connection is made to host port hostport from the remote machine.

On this SSH tunnel we can connect to the remote database from PHP:

$smysql = mysql_connect( '127.0.0.1:3307', 'user', 'password' );