some notes

Tunnelling MySQL Over SSH

Like many people, you store sensitive information in your database. And like some, your database server is on a different host from one or more of your webservers. How do you secure the necessary network connections?

The obvious answer is SSL/TLS. But that requires you to a) use a custom MySQL binary, and b) manage *yet another* set of certificates. And it requires setting extra parameters when connecting to the database in your code.

A less obvious answer is to use the ssh's built-in port forwarding to tunnel the connection. Here's how.

Special thanks to Jon Baer for this post to NYPHP Talk!

Note that the commands and scripts in this document assume that you are connecting to a MySQL database named "db" on remotehost.com, port 3306, using username "dbuser" and password "PASS". You will need to change these values to match your own setup.

Contents

  1. The Basic Setup
  2. Making It Work
  3. Making It Automatic at Startup
  4. Making It A Daemon

The Basic Setup

To set up a tunneled connection to a MySQL server at remotehost.com, you can issue the following two commands on any client:
ssh -fNg -L 3307:127.0.0.1:3306 myuser@remotehost.com
mysql -h 127.0.0.1 -P 3307 -u dbuser -p db
The first command tells ssh to log in to remotehost.com as myuser, go into the background (-f) and not execute any remote command (-N), and set up port-forwarding (-L localport:localhost:remoteport ). In this case, we forward port 3307 on localhost to port 3306 on remotehost.com.

The second command tells the local MySQL client to connect to localhost port 3307 (which is forwarded via ssh to remotehost.com:3306). The exchange of data between client and server is now sent over the encrypted ssh connection.

To make the same connection using PHP's built-in MySQL client:
<?php
$smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "PASS" );
mysql_select_db( "db", $smysql );
?>

Server Configuration

Obviously, this approach requires that you have a shell account on the remote (database) server that you can log into in order to set up the forwarded port. And it requires a MySQL login that is allowed to connect to the database in question from host 127.0.0.1

On the database server, as root, create a user to handle the server side of all tunnels. This user will have no valid shell (might not work on all operating systems?)
useradd -s /bin/false myuser
mkdir /home/myuser/.ssh
touch /home/myuser/.ssh/authorized_keys
chown -R myuser:myuser /home/myuser/.ssh
chmod 755 /home/myuser/.ssh
chmod 600 /home/myuser/.ssh/authorized_keys
Connections via the tunnel will look like they are coming from 127.0.0.1, so you need to update the GRANT tables in the database:
USE mysql;
GRANT ALL ON db.* TO dbuser@127.0.0.1 IDENTIFIED BY 'PASS';
FLUSH PRIVILEGES;
Congrats, your server is ready to accept tunnels.

Client Configuration

Because you won't be around to type a password, you'll need to set up an RSA key pair for ssh on each client ( more details here ).

The first step is to set up an RSA key pair as root on each client, leaving all questions blank:
root@local# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/root/.ssh/id_rsa.
Your public key has been saved in /var/root/.ssh/id_rsa.pub.
The key fingerprint is:
23:02:1b:f7:55:2b:35:cf:0c:5d:ad:21:c7:e1:78:ca root@myhost.local
root@local#
The next step is to append the rsa public key file to myuser's .ssh/authorized_keys on the server. First we copy the key, then we shell in and append it to the authoized list:
root@local# scp /var/root/.ssh/id_rsa.pub root@remotehost.com:/tmp/myhost.local_rsa.pub
root@local# ssh remotehost.com
...
root@remote# cat /tmp/myhost.local_rsa.pub >> /home/myuser/.ssh/authorized_keys
root@remote# exit

Now you can set up the connection on the client, without typing myuser's password:
root@local# ssh -fNg -L 3307:127.0.0.1:3306 myuser@remotehost.com


Making It A Daemon

A quick and dirty way to make sure the connection runs on startup and respawns on failure is to add it to /etc/inittab and have the init process (the, uh, kernel) keep it going.

Add the following to /etc/inittab on each client:
sm:345:respawn:/usr/bin/ssh -Ng -L 3307:127.0.0.1:3306 myuser@remotehost.com
And that should be all you need to do. Send init the HUP signal ( kill -HUP 1 ) to make it reload the configuration. To turn it off, comment out the line and HUP init again.

By Chris Snyder on May 11, 2004 at 2:50pm

jump to top