Forwarding a local MySQL socket to a remote MySQL server

3 May 2013 — 4 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.

4 responses to Forwarding a local MySQL socket to a remote MySQL server

  1. 

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

    how about security?

  2. 

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

  3. 

    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).

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s