Remote MySQL at localhost
· Linux, Tutorials
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.pxy.se 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.pubTake 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.pxy.se ... Linux sqlsrv 2.6.32-5-xen-amd64 tunnel@sqlsrv [~]$ logout Connection to sqlsrv.entropux.pxy.se closed. -
The SSH session needs some tweaking. Create configuration file for ssh (~/.ssh/config) containing something like this:
Host sqlsrv HostName sqlsrv.entropux.pxy.se Port 22 User tunnel BatchMode yes ExitOnForwardFailure yes IdentitiesOnly yes LocalForward 127.0.0.1:3306 127.0.0.1:3306 StrictHostKeyChecking yesWondering what something does? Hit
man ssh_configthen. 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.pxy.seYay. 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 tunnelAdd some restrictions to the public key in the authorized_keys file (man sshd for details):root@sqlsrv [~]# echo 'from="websrv.entropux.pxy.se",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.rpmNasty 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 - mysqlNot 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:3306socat 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 aliveWoho. 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.