How to force MySQL to connect by TCP instead of a Unix socket?
Solution 1
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
Solution 2
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.
Solution 3
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.
Solution 4
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
Solution 5
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.
Related videos on Youtube
Max
Analytics consultant available for hire. More info: https://maxcorbeau.com
Updated on September 18, 2022Comments
-
Max over 1 year
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 themy.cnf
anddebian.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
onubuntu 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 withbind-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
, changinglocalhost
to127.0.0.1
fixed the issue (i.e. Drupal is now making connections to the network socket). -
Max over 12 yearsit 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 over 12 yearswell by saying
no control
I was being dramatic. I could modify the.ht_config.php
file and fix the issue. -
user9517 over 12 yearsA tad dramatic perhaps but it is a client issue and it is easily solved. See my edit.
-
Max over 12 yearsSorry, I updated my question with the answer after I added my comment. For some reason we are using
.ht_config.php
instead ofsettings.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. -
Pacerier over 8 yearsThis should be the accepted answer.
-
Pacerier over 8 yearsJust use
--protocol
.. see Jonathan's answer. -
Kris over 7 yearsNote: you can't do
--protocol=socket
if you have ahost
entry in the[client]
section of.my.cnf
it giveswrong or unknown protocol
error. (mysql 5.7.13) -
Stephane over 6 yearsDoes 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 ?
-
reinierpost over 3 yearsThis does not answer the question.