MySQL Workbench with Unix Socket only servers.

Brandon Johnson

Here at Mozilla we like to keep our database systems secure and no more open than they need to be.  Like many database systems, privileged users are locked down to socket only connections. This can be a problem if you want to use database admin tools, like the MySQL Workbench or maybe even some remote performance monitoring software.

One cool trick is forwarding that socket to a port temporarily while you do your work. Below I’ll show you how to forward a socket to a port for use with the MySQL Workbench.

First, establish an ssh connection to your database server using any regular shell connector, such as Terminal for Mac devices, ssh for Linux devices, or PuTTy for Windows.

Once connected, assuming you’re using a socket of “/var/lib/mysql/mysql.sock” (the default), we’ll use a linux tool called socat to forward that socket to an unused port. Note that socat has packages for a wide variety of distributions and is available through both yum and apt-get for major distributions. For more info on socat, see here.

The command for this is:

socat TCP-LISTEN:<port>,reuseaddr,fork,su=nobody UNIX-CLIENT:<socket_path>

An example, using the default socket path and port 3308 would be:

socat TCP-LISTEN:3308,reuseaddr,fork,su=nobody UNIX-CLIENT:/var/lib/mysql/mysql.sock

Tip: If you’re consistently connecting from the same IP address, consider adding the “range” option to the socat parameters for extra security.

What does this command do? It forwards all traffic being received from the port provided to the socket provided. It’s basically the socket equivalent to port forwarding.

Once you’ve created the socket forwarding, you can connect the MySQL Workbench to it by simply setting up a connection to the server using TCP/IP and your port, like the screenshot below (note that it will require your normal socket user’s password).

Screen Shot 2013-06-18 at 5.41.13 PM

 

Once you’re configured like the above screenshot, voila! You can now connect to your socket only server.