Overview

This How-To is intended to provide a secure means of accessing a MySQL database (on another server) over an insecure channel. Eventually MySQL will release a binary with SSL encryption enabled for replication. Until then, you can use SSH easily. Please note that what I am describing applies equally well to any TCP/IP port-based service. Basically, it's called port forwarding, and it allows a remote port to appear locally (or the reverse) over an encrypted channel. (Last updated 2004-06-13)

Secure MySQL Scenario

Some people feel that a database server is more secure inside a firewall. However, they often want their web server outside (or at least in a demilitarized zone (DMZ)). Most web applications require access to the a database to do anything useful.

As shown in this diagram, with the firewall in place, the web server can not establish a direct connection to the database server. (You could set up network address translation (NAT), but it's trickier). However, the database server can "see" the web server as the web machine is externally accessible on the Internet.

SSH Port Forwarding

In this instance, one could port forward db_server:3306 to web_server:3306. Then it would appear as if there were a MySQL database running locally on the web server listening on port 3306. However, localhost:3306 on the web server is really being securely forwarded to localhost:3306 on the database server.

To set this up, you'll want a password-less key pair to allow the SSH tunnel to be started automagically. Do the following:

db_serv$ ssh-keygen -t rsa
db_serv$ scp .ssh/id_rsa.pub webserver:
web_serv$ cd ~; mkdir .ssh
web_serv$ cat id_rsa.pub >> .ssh/authorized_keys2
web_serv$ chmod -R go-rwx .ssh; rm id_rsa.pub
db_serv$ ssh webserver

The last command should let you SSH from the database server without providing a password. The keypair does the authentication.

The command to open an SSH tunnel is:

db_server$ ssh -f -q -N -R3306:db_server:3306 webserver

You can then test out local database access on the webserver. You'll need to have the permissions set correctly in the MySQL databse for the user and password you're using.

web_serv$mysql -h 127.0.0.1 -P 3306 -u user -p db_name

You'll probably want to add the 'ssh' line above to /etc/rc.d/rc.local (on Red Hat) so that the tunnel gets opened on reboots. Remember if the tunnel goes down, your web app can't access the database.

Other Applications

Currently I use this method mostly for running MySQL replication to slave servers at other sites over the general Internet. This works pretty well. I wrote a simple cron job script to check to ensure the slave server is replicating in case the connection goes down. With an SSH tunnel, the slave can open an SSH connection to the master and then appear to replicate locally off a different port.

SLAVE: shell> ssh -f -q -N -L3307:MASTER_IP_ADDR:3306 -l root MASTER_IP_ADDR
MASTER: mysql> GRANT FILE ON *.* TO repl@master identified by 'password';
SLAVE: Put these lines in the /etc/my.cnf file:
      [mysqld]
      master-host	= 127.0.0.1
      master-user	= repl
      master-password = PASSWORD
      master-port	= 3307
      server-id	= 2
MASTER: Put these lines in /etc/my.cnf file:
      [mysqld]
      log-bin  
      server-id = 1

You'll need to get an exact copy of the master database over to the slave, then start the master up, then start the slave. See the MySQL docs for more information on setting up replication.

Feel free to contact me with questions.