Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I would like to analyze mysql traffic. Right now, all mysql requests are sent to the MySQL unix socket:

unix  2      [ ACC ]     STREAM     LISTENING     3734388  15304/mysqld        /var/run/mysqld/mysqld.sock

I'm trying to disable that socket to force MySQL to use the network socket instead on the loopback. I tried commenting out all the socket directives in the my.cnf and debian.cnf files and restarted MySQL but it made no difference.

How can I disable the MySQL unix socket to force MySQL over the network?

additional info: I'm running MySQL 5.1 on ubuntu 10.04.

Precisions on the question
Since plenty of people suggested enabling the network socket I would like to clarify my question by pointing out that the bind address was already enabled with bind-address = 127.0.0.1 and that a listening connection is available:

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      15601/mysqld 

Still I see no connections attempt to 127.0.0.1:3306 coming from my webapp (Drupal website).

Updated with the answer

It appears indeed that the issue is coming from the mysqli connector that Drupal uses (in .ht_config.php for those who are interested). It was set: mysqli://drupal:***@localhost/drupal, changing localhost to 127.0.0.1 fixed the issue (i.e. Drupal is now making connections to the network socket).

share|improve this question

6 Answers 6

up vote 15 down vote accepted

Use an IP-binding to 127.0.0.1 that should activate a listening port on "localhost". On the client side do not use "localhost" - use "127.0.0.1" instead. Many clients have an internal alias that makes them connect to the socket if you specify "localhost" as target.

mySQL is strange.

share|improve this answer

php mysqli client will use unix socket file instead of tcp network when you pass in the NULL value or the string "localhost"(http://www.php.net/manual/en/mysqli.construct.php)

it seems that sqlyog client always use tcp network even when you fill "localhost" in it

share|improve this answer

Isn't this really a client issue ? If using the mysql program You can use the --protocol switch. From the man page

 --protocol={TCP|SOCKET|PIPE|MEMORY}

       The connection protocol to use for connecting to the server. It is
       useful when the other connection parameters normally would cause a
       protocol to be used other than the one you want. For details on the
       allowable values, see Section 4.2.2, “Connecting to the MySQL
       Server”.

I just tried

mysql --protocol=TCP -u root -p

whilst monitoring port 3306 with tcpdump -i lo tcp port 3306 and I can see traffic whereas if I just run

mysql  -u root -p

I (correctly) see no traffic on port 3306.

EDIT:

Now that you tell us you are using DRUPAL, the solution is relatively easy.

Go to sites/<sitename> or sites/default and edit the settings.php file

You will find a structure like this

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'databasename',
      'username' => 'databaseuser',
      'password' => 'databasepassword',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Change the 'localhost' to '127.0.0.1' and save the file.

share|improve this answer
    
it could indeed by a client issue but since the client is a web application (Drupal) and I have no control over it I was looking for a way to force it from a system standpoint. –  user64204 Dec 6 '11 at 10:46
    
well by saying no control I was being dramatic. I could modify the .ht_config.php file and fix the issue. –  user64204 Dec 6 '11 at 11:07
    
A tad dramatic perhaps but it is a client issue and it is easily solved. See my edit. –  Iain Dec 6 '11 at 11:22
    
Sorry, I updated my question with the answer after I added my comment. For some reason we are using .ht_config.php instead of settings.php. I don't know why (the dev team requested it should be that way). Now the issue is that Drupal seems to be reading the .ht_config.php file on every request (because if I change it, changes are immediately taken into account) which can't help with the performances. We will be looking for a way to cache those settings at application layer but that's a different problem. –  user64204 Dec 6 '11 at 11:31

Edit the my.cnf and add the directive

bind-address = 127.0.0.1

or your preferred IP to make it accessible over network. Restart mysql after to get it work.

share|improve this answer

In Linux and other *nixes, MySQL will assume you want to use a socket if you connect to the host "localhost" (which would be the default hostname).

You can override this in 3 ways: 1) Specify a different hostname like 127.0.0.1 (mysql -h 127.0.0.1) or your server's real hostname 2) Specify that you want to use TCP and not a socket (mysql --protocol tcp)

You can also easily make that the default my editing your my.cnf so it has this ([client] means any client:

[client]
protocol=tcp

You can see the full description of how MySQL decides how to connect here:

http://dev.mysql.com/doc/refman/5.5/en/connecting.html

share|improve this answer

This may sound a little crazy

Try setting the socket file to an absolute path whose path resides on another machine

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_socket

Otherwise, you cannot bypass this default behavior because a socket file must exist for mysqld to communicate with.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.