Remote MySQL at localhost

Posted on by Philip Eklöf

It’s pretty common to install different server applications on different machines, be it physical or virtual ones. There’s several reasons for doing such; performance, uptime, security by isolation, etc.

Doing this with database servers in shared web hosting environments might be a bit of a hassle though…

Imagine a web hosting company. Some of them run things like cPanel or other commercial web hosting panels. Basically LAMP installations running on a single Linux server.

If you for any reason would like to set up a dedicated MySQL server, you’ll probably have to reconfigure the database details in your web applications. Ordinary CMS projects like WordPress, Joomla, Drupal etc. defaults to localhost as the server address.

The address to your dedicated MySQL server is obviously not localhost.

What if you have hundreds of customers and perhaps thousands of web sites like this in your LAMP setup? I wouldn’t change all those configuration files.

So let’s trick the system into believing that the db server is still running on localhost.

MySQL listens for connections in two separate ways:

  • Port TCP/3306, usually bound to the loopback interface.
  • A unix domain socket usually located at /var/lib/mysql/mysql.sock.

Forwarding a TCP port to another host is easy using an ssh tunnel. But what about the unix socket? The answer is something like socat. Lets begin.

Set up the MySQL server

Install a clean MySQL server on your new machine (lets call it sqlsrv). You should know how to do that.

A quick and easy way of migrating all your database content from your LAMP machine (websrv) is to export all of it into an ordinary file:
mysqldump --all-databases > dump.sql

Copy the dumped data to sqlsrv using e.g. scp and then import the data into the MySQL server:
mysql < dump.sql

This should have copied all databases, including user grants etc.

If your MySQL server contains a lot of data you might speed things up by executing something like this instead:
mysqldump --all-databases | ssh sqlsrv.entropux.net mysql

Set up the SSH tunnel

The SSH tunnel should be set up between the machines. There is no reason doing that as root, so lets set up new user accounts on both ends.

  • Begin with websrv.
    root@websrv [~]# groupadd tunnel
    root@websrv [~]# useradd -s /bin/bash -g tunnel -m tunnel
    root@websrv [~]# su - tunnel
    tunnel@websrv [~]$ mkdir .ssh
    tunnel@websrv [~]$ ssh-keygen -b 2048 -N '' -P '' -f .ssh/id_rsa
    tunnel@websrv [~]$ cat .ssh/id_rsa.pub

    Take a look at your new shiny key pair. Copy the public key into your clipboard.

  • Continue with sqlsrv.
    root@sqlsrv [~]# groupadd tunnel
    root@sqlsrv [~]# useradd -s /bin/bash -g tunnel -m tunnel
    root@sqlsrv [~]# su - tunnel
    tunnel@sqlsrv [~]$ mkdir .ssh
    tunnel@sqlsrv [~]$ echo 'PUBKEY_FROM_CLIPBOARD' > .ssh/authorized_keys

  • Log on to sqlsrv from websrv using the new user accounts.

    tunnel@websrv [~]$ ssh tunnel@sqlsrv.entropux.net
    ...
    Linux sqlsrv 2.6.32-5-xen-amd64
    tunnel@sqlsrv [~]$ logout
    Connection to sqlsrv.entropux.net closed.

  • The SSH session needs some tweaking. Create configuration file for ssh (~/.ssh/config) containing something like this:

    Host sqlsrv
      HostName sqlsrv.entropux.net
      Port 22
      User tunnel
      BatchMode yes
      ExitOnForwardFailure yes
      IdentitiesOnly yes
      LocalForward 127.0.0.1:3306 127.0.0.1:3306
      StrictHostKeyChecking yes

    Wondering what something does? Hit man ssh_config then. Most importantly is none the less the LocalForward directive which forwards the TCP port 3306 between the hosts.

  • Launch another shell for websrv and make sure that you've stopped any MySQL daemon processes (they should be running at sqlsrv now!).

    root@websrv [~]# ps aux | grep mysq[l]
    root@websrv [~]# netstat -lptn | grep 3306
    root@websrv [~]#
    root@websrv [~]# mysqladmin ping
    mysqladmin: connect to server at 'localhost' failed
    error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
    root@websrv [~]# mysqladmin -h 127.0.0.1 ping
    mysqladmin: connect to server at '127.0.0.1' failed
    error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'

    There's obviously no MySQL server running on websrv.

    Btw, it should be noted that not specifying the -h argument with mysqladmin means that it will try to connect through the unix socket.

  • So lets try launching the SSH tunnel and the configured alias.

    tunnel@websrv [~]$ ssh sqlsrv
    ...
    Linux sqlsrv 2.6.32-5-xen-amd64
    tunnel@sqlsrv [~]$

    Good. Keep it logged on like that.

  • Head back to your root terminal at websrv and repeat the previous commands...

    root@websrv [~]# netstat -lptn | grep 3306
    tcp  0  0  127.0.0.1:3306  0.0.0.0:*  LISTEN  1599/ssh
    root@websrv [~]# mysqladmin ping
    mysqladmin: connect to server at 'localhost' failed
    error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
    root@websrv [~]# mysqladmin -h 127.0.0.1 ping
    mysqld is alive
    root@websrv [~]# mysqladmin -h 127.0.0.1 variables | grep hostname
    | hostname | sqlsrv.entropux.net

    Yay. The database connection was successful. Only by using TCP though, not through the Unix domain socket.

Hardening the security of SSH account

Now that when we've made sure that the SSH tunnel works, lets make sure that the tunnel user account at sqlsrv can't be used for anything nasty.

  • Change the user's shell to something useless:
    root@sqlsrv [~]# usermod -s /bin/false tunnel

    Add some restrictions to the public key in the authorized_keys file (man sshd for details):
    root@sqlsrv [~]# echo 'from="websrv.entropux.net",permitopen="127.0.0.1:3306",command="true",no-pty,no-user-rc' "$(</home/tunnel/.ssh/authorized_keys)" > /home/tunnel/.ssh/authorized_keys

  • Try logging on to the account again:

    tunnel@websrv [~]$ ssh sqlsrv
    PTY allocation request failed on channel 0
    Linux sqlsrv 2.6.32-5-xen-amd64
    Connection to sqlsrv closed.
    tunnel@websrv [~]$

    No shell spawned. Good, won't be needing that anyway!

    Try executing a specific remote command:

    tunnel@websrv [~]$ ssh sqlsrv bash
    tunnel@websrv [~]$ ssh sqlsrv id
    tunnel@websrv [~]$

    Nothing! That's all good, but what about the SSH tunnel if there's no persistent session? Use the -N flag for ssh to launch a session without trying to execute remote commands. And add the -f flag at the same time, putting ssh into background when run.

    tunnel@websrv [~]$ ssh -f -N sqlsrv
    tunnel@websrv [~]$ netstat -lptn | grep 3306
    tcp  0  0  127.0.0.1:3306  0.0.0.0:*  LISTEN  12041/ssh

Set up the socat proxy for the unix socket

TCP connection to MySQL is working. Lets get that unix socket connection working also.

  • Install socat. You might find it in your distribution's package repository. Or not. At least not with CentOS 6. So I found it at repoforge:

    root@websrv [~]# wget http://pkgs.repoforge.org/socat/socat-1.7.2.1-1.el6.rf.x86_64.rpm
    root@websrv [~]# rpm -Uvh socat-1.7.2.1-1.el6.rf.x86_64.rpm
    warning: socat-1.7.2.1-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
    Preparing... ########################################### [100%]
         1:socat ########################################### [100%]
    root@websrv [~]# rm socat-1.7.2.1-1.el6.rf.x86_64.rpm

    Nasty though. You'd probably want to install it from a trusted source instead, or at least check the rpm signature and contents before installing something just like that. =)

  • Since the unix socket needs to be created inside the /var/lib/mysql directory, which is owned by mysql:mysql in my system, lets simply use that very user to run socat:

    root@websrv [~]# su - mysql

    Not successful? Well perhaps your system's mysql user is named something else, isn't set up with an ordinary shell or something else. There's so many ways to do these things. Perhaps you could try using sudo to spawn a shell instead.

  • socat can do pretty much everything when it comes to networking. RTFM. Basically though, for this to work, it needs to be configured to listen on a unix socket (the MySQL one of course) and proxy the incoming connections to the ssh tunnel.

    mysql@websrv [~]$ socat unix-listen:/var/lib/mysql/mysql.sock,fork,unlink-early tcp:127.0.0.1:3306

    socat won’t daemonize or anything like that, so we’ll need to handle that ourselves (but lets ignore it for now).

  • While socat is still running, launch another shell at websrv and try connecting to the MySQL server over the unix socket and through the ssh tunnel (ssh was put in the background previously, remember):

    root@websrv [~]# mysqladmin ping
    mysqld is alive

    Woho. Success.

Also …

There’s probably some more things you want to set up though.

Some init scripts (or systemd services?) to easily control the ssh tunnel and socat proxy would be nice.

Here’s a basic init script for mysql-tunnel (ssh) + default file.
Same thing for mysql-unix (socat) + default file.

And here’s a very basic configuration file for Monit which will keep your proxying up and running at all times.

BTW…

Although this is setup for a MySQL server, it could definitely be used for other similar services as well. I believe it would be the same thing with e.g. a PostgreSQL server.

In some sort of load balanced system with several web servers talking to a single database server, you would be able to set up this on each web server. Database server details in the web application itself would never need to be modified.