Saturday, 28 May 2011

So you can't run GUI tools on your MySQL server?

GUI tools bring in a lot of dependent packages which is usually undesirable on a tight linux server. MySQL server is usually configured  to listen to a local UNIX domain socket and the MySQL root user is usually only allowed by default to connect from this socket. If you have your security right, this socket should have restricted permissions and not allow everyone to connect.

So when you want to run a GUI such as MySQL Administrator as root on your server, how do you manage this?

Fortunately, the answer comes via socat which is a more modern version of netcat along with our old friend, SSH tunnels. socat and openSSH are core packages in Debian and Ubuntu, although socat may need sourcing from a third party repository for some linux distros.

Here's the magic:

# On the MySQL server, as whichever linux user can access the MySQL socket:
socat tcp-listen:13306,reuseaddr,fork,bind=127.0.0.1 unix:/var/run/mysqld/mysqld.sock
# On your PC
ssh -L3306:localhost:13306 mysql.example.com

The socat command will need the path adjusted for the location of mysqld.sock (check /etc/mysql/my.cnf - it may be in /var/lib, /var/run or /tmp). socat creates a tcp server on port 13306 accessible from 127.0.0.1 only.

The ssh command needs the host to be your MySQL server and you may login with any account that is permitted. What happens now, is ssh creates a tcp server on your PC on port 3306 bound to 127.0.0.1 only and wired through to socat on the MySQL server which is wired to the MySQL unix domain socket.

So your PC's 127.0.0.1:3306 tcp listener in now effectively wired to the heart of your remote MySQL server - clever eh?

Now run MySQL Administrator or whatever tool from the comfort of your machine! Remember to connect to 127.0.0.1, standard port of 3306. Don't try to use "localhost" as, for some reason, probably due to bad mushrooms, the MySQL developers decided that "localhost" meant "unix domain socket". Sigh...

Security implications

On the MySQL server, be aware that any other local user may now connect to the 13306 port, thus gain root access to your databases, depending on whether root has a password configured. The same applies to your PC on port 3306 - so if your "PC" happens to be a *nix multiuser server with loads of other people logged in, this would classify as a Bad Idea (TM).

Close down your ssh tunnel and socat as soon as you have finished.

No comments:

Post a Comment