How to force MySQL to connect by TCP instead of a Unix socket?

Question

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

score 117 · Accepted Answer · answered Dec 05 '11 at 22:29

117

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

answered Dec 05 '11 at 22:29

Jonathan Amend

1,366
1
8
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

score 62 · Answer 2 · edited Oct 19 '15 at 21:35

62

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.

edited Oct 19 '15 at 21:35

the

468
8
23

answered Dec 05 '11 at 19:00

Nils

7,657
3
31
71

11

Just use `--protocol`.. see Jonathan's answer. – Pacerier Feb 08 '16 at 06:13

user9517 · Answer 3 · 2011-12-06T11:19:38.273

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.

edited Dec 06 '11 at 11:19

answered Dec 05 '11 at 18:16

user9517

114,104
20
206
289

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

score 1 · Answer 4 · answered Dec 05 '11 at 18:14

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.

score 1 · Answer 5 · answered Dec 05 '11 at 22:42

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.

answered Dec 05 '11 at 22:42

Chris

1,155
2
9
18

score 1 · Answer 6 · answered Oct 12 '12 at 04:15

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

score -1 · Answer 7 · answered Oct 29 '16 at 14:46

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

answered Oct 29 '16 at 14:46

robrecord

101
1

This does not answer the question. – reinierpost Dec 15 '20 at 12:23

How to force MySQL to connect by TCP instead of a Unix socket?

7 Answers7

Linked