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.
Ionel Roiban explains how to make more secure connects with your mysql databases.
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' );
