Mysql: Force Localhost to Use TCP, Not a Unix Socket File

Apr 03
2010

So, recently I decided I was paying too much for my server because I was not maximizing performance across all the various daemons. So I decided to split my larger server into a handful of smaller servers to be able to fine tune each one to dedicated purposes. All went well, but I had some trouble for a few evenings figuring out how I could port forward localhost:3306 to the, now remote, database server. This should have been dirt simple with an iptables rule – but after digging in, I discovered MySQL treats localhost “special” by sending connections through the unix socket file, which is absolutely faster, but only works if the database daemon is on the same host as the connecting application.

After doing some research, I found it is possible to use a tool like socat and autossh to wrap an ssh tunnel to forward connections through the socket file to a remote IP over TCP. This however, was more complex and one off than I cared to explore for my simple problem. I finally resorted to using DNS and to stop using localhost as the host name. However, a few tid bits for the weary traveler:

  • The mysql client library is responsible for selecting the protocol.
  • PHP’s internal mysql libraries, unfortunately, as far as I could discover ( please correct me if I am wrong here ), do not allow you to select the protocol.
  • So if you’re using “localhost” as your host name in a PHP mysql_connect, you’re forced to go through the socket file, however, you can use 127.0.0.1 instead of localhost to force TCP.
  • The linux mysql-client package command line tool offers a –protocol=tcp flag if you want to force TCP. You can also set this as a default inside /etc/mysql/my.cnf under the [client] heading

my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
protocol        = TCP

Again, this appears to work fine if you’re not using PHP as your client.

I hope this lesson learned ( use DNS ) comes as a helping hand to others out there. If anybody has some other suggestions, please do leave a comment!

3 Responses to “Mysql: Force Localhost to Use TCP, Not a Unix Socket File”

  1. Leho Kraav (@lkraav) says:

    Good stuff. I have no idea how long it would’ve taken me to figure out that specifying either 127.0.0.1 and localhost for WordPress DB_HOST would result in different path getting chosen. I’m doing port forwarding through SSH and It took an annoyingly long time to figure out I should try –protocol with mysql client to get it connected to the remote server.

  2. Thomas says:

    You know that mysql_* is deprecated? mysqli_* is in many ways much better and I could imagine that it even lets you choose the protocol.

  3. Sameer says:

    We are actually using Socat along with HaProxy to forward MySQL socket to tcp port. Most of the time it works perfectly but once in a 3-4 months socat dies without logging any thing.

    So far couldn’t find any solution which is much more robust.

Leave a Reply

Visit Other Sites!

Find me on other sites...

Archives

All entries, chronologically...

Pages List

General info about this site...