At times it can be very handy to be able to use a local MySQL socket, while the real MySQL server is in fact a remote one. I once used this technique to split a dozen LAMP servers into dedicated (clustered) web servers and MySQL servers. Without having all clients to update scripts, that is.
Today I ran into another use-case: while migrating an old 32bit MySQL Windows server to a modern Linux VM. We wanted to get some statistics from it. The famous ‘mysqltuner.pl‘ script is designed to run from localhost. Well, let’s fake MySQL is on localhost then 😉
For these tricks to work you need a tool called ‘socat‘, which can be installed like this:
apt-get install socat
This is how to create a socket in ‘/var/lib/mysql/mysql.sock’, owned by both user and group ‘mysql’ and forward the connections to ‘mysql-server’ on port 3306:
socat UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,\ reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 \ TCP:mysql-server:3306 &
You can now connect:
mysql -u user -p
No need to supply a hostname, MySQL connects to the local socket by default and socat takes care of forwarding the connection to the real MySQL server.
It is important to note this works as long as socat is running. You could use a tool called runit to keep socat running at all times. In another post I’ll explain more about runit.
This works with other protocols as well.