MySQL cannot connect via "localhost", only 127.0.0.1

Question

this is somewhat of a mystery to me. The only way I can connect to MySQL is if I call it via "127.0.0.1" ... for example, my PHP connect script will NOT work with localhost

I'm running Mac OS X Lion, built-in apache2, MySQL, PHP, phpMyAdmin

mysqladmin:

count                             0
debug-check                       FALSE
debug-info                        TRUE
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
no-beep                           FALSE
port                              0
relative                          FALSE
socket                            (No default value)
sleep                             0
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
user                              (No default value)
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)

This is probably more for Server fault, but make sure your hosts file has the localhost alias. — Matt, Jul 28 '11 at 22:13
Open command prompt and type `ping localhost` and see what it says. — Steve Robbins, Jul 28 '11 at 22:20
64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.100 ms 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.102 ms 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.096 ms — dcolumbus, Jul 28 '11 at 22:35

Raffael Luthiger · Accepted Answer · 2011-08-03T21:48:41.563

30

MySQL will try to connect to the unix socket if you tell it to connect to "localhost". If you tell it to connect to 127.0.0.1 you are forcing it to connect to the network socket. So probably you have MySQL configured to only listen to the network socket and not to the file system socket.

What exactly is wrong with your unix socket is hard to tell. But I recommend you to read this page on the MySQL reference guide. This should help you.

UPDATE: Based on the updated question: The parameter "socket" should be something like this: "/var/lib/mysql/mysql.sock". This page in the Reference Manual has some more information.

Here you have the beginning of my /etc/my.cnf file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Your file should be similar. Then your problem should be solved. Don't forget to restart the MySQL server before you test it.

edited Aug 03 '11 at 21:48

answered Jul 28 '11 at 22:43

Raffael Luthiger

2,011
2
17
26

1

Wow: "A Unix socket file is used if you don't specify a host name or if you specify the special host name localhost." That seems...unintuitive. Hence all the other answers. – Mark Wagner Jul 28 '11 at 23:41
Thats good to know +1 – Matt Jul 29 '11 at 01:03
I appreciate the theory... just don't know what the answer is. – dcolumbus Jul 30 '11 at 04:49
@dcolumbus: As I already wrote it is hard to tell. What I can recommend you is to open a console and just write the command "mysql". Maybe you get more information then. Another thing is to look into the log file. Mine is located in the directory /var/log/ and is called mysql.log. Yours could have another name or another location. If you can provide us with more information then we can probably help you better. – Raffael Luthiger Jul 30 '11 at 10:59
Raffael, take a look at my post. I've pasted the mysqladmin information. – dcolumbus Aug 03 '11 at 20:13
@dcolumbus: I have updated the answer. Now you should have everything you need in order to solve the problem. – Raffael Luthiger Aug 03 '11 at 21:49
2

php.ini has references to "/var/mysql/mysql.sock" (3 places to be exact) which need to be changed to "/tmp/mysql.sock" ... Thank you for your help! – dcolumbus Aug 03 '11 at 22:00
Thanks @dcolumbus. You solved a long-running mystery for me. – sehummel Sep 05 '14 at 23:18

score 8 · Answer 2 · answered Jul 28 '11 at 22:37

8

You may have IPv6 enabled, its very possible localhost resolves to the ipv6 localhost, that is not defined in your msql config.

ive also had a problem where i had to add 'localhost' in place of '127.0.0.1' to the allowed subnets for that user, dont understand why (i was using ipv4 and it was a while ago) but its worth a try.

answered Jul 28 '11 at 22:37

Silverfire

780
4
14

You can check this by seeing if 'host localhost' at the command line returns ::1 as well as 127.0.0.1. If so, you can either remove the ::1 mapping from /private/etc/hosts or reconfigure MySQL to listen on the IPv6 ::1 address as well as 127.0.0.1 – David North Jul 28 '11 at 22:42
I think I remember reading somewhere that IPv6 is enabled by default on Mac OS X ... is that the case? – dcolumbus Jul 28 '11 at 23:07
ipv6 is enabled for pretty much all modern OS's these days, im on osx10.6 and its enabled by default. – Silverfire Jul 29 '11 at 02:13
wrong. Localhost has special meaning for mysql clients — check out https://serverfault.com/a/295300/67675 – poige Jan 29 '19 at 03:39

score 5 · Answer 3 · answered Aug 15 '15 at 23:28

For me, OSX's builtin php is configured to use a different unix-socket than homebrew's mysql. Thus it can't connect via localhost which utilizes that socket.

I fixed it with a quick hack by symlinking php's configured socket-path to point to the one mysql actually uses.

sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

The following diagnostic commands were very helpful.

Check the default socket-paths used by php and mysql:

php -i | fgrep 'mysql.default_socket'
mysql -e 'show variables where variable_name = "socket"'

Connect using a specified socket:

php -r 'var_dump(mysql_connect("localhost:/tmp/mysql.sock", "user", "pass"));'
mysql --socket=/tmp/mysql.sock

Determine what kind of socket mysql client is using to connect:

lsof | egrep '^mysql .*(IPv|unix)'

score 2 · Answer 4 · answered Jul 28 '11 at 22:38

2

Could you check mysql/conf/my.conf (the directory structure should pretty much be the same on OSx) to see if skip-networking is uncommented? If so, add a # in-front of the line and restart the mysql-server.

I actually had a similar issue a while back (although that wasn't in OSx), so I thought it might be worth a shot.

answered Jul 28 '11 at 22:38

karllindmark

129
3

1

Why did this get downvoted? An explanation would be helpful. – karllindmark Jul 31 '11 at 18:44
I didn't down-vote you, but your answer is actually the opposite of what the OP is asking. But I upvoted you because I struggled with the opposite condition — would connect via a socket, but NOT via network. By the way, I COULD NOT fix this via my.conf; I had to put it on the command line: "mysqlf --skip_networking=0 ..." – Jan Steinman Feb 14 '15 at 13:00

score 2 · Answer 5 · answered Feb 17 '17 at 10:29

PHP is still trying to use the default socket location. This problem can appear if you have moved the MariaDB/MySQL folder from /var/lib/mysql to another location. In order to solve the problem you have to define the new socket's location in the /etc/php.ini file.

mysqli.default_socket =/newDBLocation/mysql/mysql.sock

Watch out, depending on which driver you use, you may have to specify the pdo_mysql.default_socket=!

In order to check your current directory run the following command in mysql:

select @@datadir;

score 2 · Answer 6 · answered Jan 29 '19 at 02:12

2

For people who are using CageFS with CloudLinux:

I recreated /var/lib/mysql because I was rebuilding MySQL server from scratch...

which unmounted the path from cagefs. I know it's unrelated, but I was using cPanel and CloudLinux. I couldn't verify why socket connection won't work, and finally, I realized.

adding /var/lib/mysql to /etc/cagefs/cagefs.mp (if already there proceed to next step) and running

cagefsctl --remount-all

fixed the problem

answered Jan 29 '19 at 02:12

Luka

375
5
21

1

OMG! This is what fixed it for me! It was already in `/etc/cagefs/cagefs.mp` but running `cagefsctl --remount-all` fixed it. Thanks man! – Alvaro Flaño Larrondo Jul 08 '19 at 14:14

score 1 · Answer 7 · answered Jul 28 '11 at 22:13

1

Is localhost defined in your /private/etc/hosts file?

answered Jul 28 '11 at 22:13

Justin ᚅᚔᚈᚄᚒᚔ

426
4
13

127.0.0.1 localhost is in my hosts file. – dcolumbus Jul 28 '11 at 22:16
4

`/private`? Never seen that before – TheLQ Jul 29 '11 at 11:04
@TheLQ: It's a Mac thing. While there __is__ a symlink from /etc to /private/etc, for some reason Apple generally advises using the "true" path: http://support.apple.com/kb/TA27291 – Justin ᚅᚔᚈᚄᚒᚔ Aug 04 '11 at 21:42

score 1 · Answer 8 · answered Aug 03 '11 at 21:06

I was able to recreate your same symptoms on my test box, hopefully this will help.

In MySQL, users are defined by two parts (name and host). By default, MySQL will have 3 root users:

mysql> SELECT host,user,password FROM mysql.user WHERE user='root';
+-----------------------+------+-------------------------------------------+
| host                  | user | password                                  |
+-----------------------+------+-------------------------------------------+
| localhost             | root |                                           |
| localhost.localdomain | root |                                           |
| 127.0.0.1             | root | *PASSWORD_HASH_GOES_HERE                  |
+-----------------------+------+-------------------------------------------+

The password field will either be blank (no password) or have a hash stored. If you set the password for one specific user, it doesn't automatically update all, since MySQL sees them as different users.

For example:

mysql> set password for 'root'@'127.0.0.1' = password('Password');

will update the password for 'root'@'127.0.0.1', but not 'root'@'localhost' or 'root'@'localhost.localdomain'

Take a look at the skip_name_resolve variable:

mysql> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

By Default skip_name_resolve is OFF, and will attempt to resolve all IP Addresses to hostnames. For example, if you connect as 'root'@'127.0.0.1', MySQL will change connect you as 'root'@'localhost'.

If it is ON, MySQL will see and connect 'root'@'127.0.0.1' and 'root'@'localhost' as seperate users. And they may or may not have different passwords, depending on how they were set.

So first, I would check to see any password differences: mysql> SELECT host,user,password FROM mysql.user WHERE user='root';

If there are, you can fix them, or you can continue investigating.

Then I would check skip_name_resolve: mysql> show variables like 'skip_name_resolve';

If it is ON, I would find out where it's being set (for example /etc/my.cnf) and remove it, unless there is a need for it.

Hopefully this helps you out!

score 1 · Answer 9 · answered Apr 06 '13 at 19:51

I was having this issue and I could not figure it out. I tried everything I could fine to no avail.

I found that I had a .netrc in /root/ that had info in it.

I deleted it and problem gone.

Able to log into mysql using mysql -uroot -p without issue now.

I know this is an old post but hope this helps someone.

score 1 · Answer 10 · answered Jan 12 '14 at 08:41

1

For me, changing the permissions to be publically readable on the parent directory of the mysql.sock fixed the issue:

chmod 755 /var/lib/mysql

answered Jan 12 '14 at 08:41

zmonteca

111
2

score 0 · Answer 11 · answered Jul 28 '11 at 22:14

0

you have to define it in private/etc/hosts I think... or just use 127.0.0.1 because it is the same thing anyhow, just an alias.

answered Jul 28 '11 at 22:14

Shackrock

198
5
16

127.0.0.1 localhost is in my hosts file. – dcolumbus Jul 28 '11 at 22:16

MySQL cannot connect via "localhost", only 127.0.0.1

11 Answers11

Linked

Related