Forwarding a local MySQL socket to a remote MySQL server

— 6,925 views — 6 Comments

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.

Comments

6 comments, archived as they were. New comments are closed.

Anthony

how about the performance, have you tested it? does using socat reduce mysql performance?

how about security?

Ahmad Fazli Ismail

Hi Anthony, socat may gave some overhead since it is using TCP instead of MySQL socket.

There is socat SSL that encrypts connection between the server and clients. However using TLS should be better than SSL.

Seth Williams

Hi Remi, I had never heard about Socat. Seems like a really useful tool. Thanks for sharing.

Remi Bergsma

You’re welcome, great to hear it was useful to you as well!

Areeb Soo Yasir

I have used this method before and it works fantastically. It is essentially a requirement if you are using PHP and your scripts connect via “localhost”, in PHP this is a non configurable option that forces socket instead of TCP (very unfortunate design IMHO).

Ryan Ziegler

Over 10 years later, and this was super helpful - solved exactly the problem I had, thank you!