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? Hitman 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.