70

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

Max
  • 3,523
  • 16
  • 53
  • 71

7 Answers7

127

In Linux and other *nixes, MySQL will assume you want to use a socket (Unix domain 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

Community
  • 1
Jonathan Amend
  • 1,466
  • 1
  • 9
  • 6
  • 8
    This should be the accepted answer. – Pacerier Feb 08 '16 at 06:12
  • Does running the MySQL server inside a Docker container with docker-compose with the use of the container name as its host name forces the use of the network protocl over a socket protocol ? – Stephane Oct 31 '17 at 07:56
  • defintiely should be the accepted answer. way 3 (protocol=tcp in my.cnf) is the only way that works without any additional command line parameters so works without changes in any script. – Tuncay Göncüoğlu Feb 18 '19 at 16:33
63

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.

the
  • 468
  • 8
  • 23
Nils
  • 7,695
  • 3
  • 34
  • 73
17

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.

user9517
  • 115,471
  • 20
  • 215
  • 297
  • 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. – Max Dec 06 '11 at 10:46
  • 1
    well by saying `no control` I was being dramatic. I could modify the `.ht_config.php` file and fix the issue. – Max Dec 06 '11 at 11:07
  • A tad dramatic perhaps but it is a client issue and it is easily solved. See my edit. – user9517 Dec 06 '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. – Max Dec 06 '11 at 11:31
  • Note: you can't do `--protocol=socket` if you have a `host` entry in the `[client]` section of `.my.cnf` it gives `wrong or unknown protocol` error. (mysql 5.7.13) – Kris Aug 30 '16 at 12:21
1

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.

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
1

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.

Chris
  • 1,185
  • 2
  • 9
  • 18
1

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

diyism
  • 161
  • 5
-1

I had to delete /etc/my.cnf (after backing it up), then restarted the server. Then I could connect with a socket and the error disappeared.

robrecord
  • 101
  • 2