13

My /etc/hosts file looks like this:

127.0.0.1 localhost

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

I found while building the server, that although i could ping localhost successfully, in certain configuration files for services like Postfix and Apache and Dovecot, I needed to specify 127.0.0.1 instead of localhost in order to make them work.

I also needed to change the configuration files of certain database driven websites, which used localhost to connect to MySQL.

I've tried various modifications to the hosts file, but nothing I've tried so far has helped.

The server is running Ubuntu 12.04.02 LTS. It does not have selinux installed and the above situation remains even after setting IPtables default policies to accept and flushing them.

Peter White
  • 586
  • 1
  • 7
  • 17

7 Answers7

16

One thing you might check is (which requires you to login to the MySQL console) - check to make sure that you have permissions to login to root via localhost.

mysql -h 127.0.0.1 -u root -p

-- Once you have successfully logged in --

mysql> select user,host from mysql.user;
+------+--------------------------------+
| user | host                           |
+------+--------------------------------+
| root | 127.0.0.1                      | 
| root | ::1                            |
| root | localhost                      | <-- Make sure you have a localhost entry for root
+------+--------------------------------+
3 rows in set (0.00 sec)

Just throwing it out there, just in case this is what the issue is.

drewrockshard
  • 1,763
  • 4
  • 20
  • 27
  • I've a feeling you're on the right track. I've done that and I can see that although root and couple of other users were created for localhost, those created by the hosting control panel for users are all for `127.0.0.1` (except for one which is `%`). I think this probably reveals enough for me to fix it, so I will probably accept your answer in short while. – Peter White Oct 07 '13 at 03:54
  • thanks for the help! i'd like to add that a mysql server restart is required after updating the mysql.user table for the change to take affect. – lackovic10 Jan 11 '19 at 09:27
  • Although accepted, this is not usually (ever?) the issue, for mysql. See [EEAA's answer](https://serverfault.com/a/544188/237895) instead (mysql interprets `localhost` as `socket` instead of `TCP`). – ToolmakerSteve Mar 31 '19 at 15:21
8

Most MySQL clients are odd in the fact that if you specify the host as localhost, they alias that to a socket connection instead of a TCP connection. Your options are to either stick with 127.0.0.1 or, if the client supports it (like the mysql CLI binary does with the --protocol flag), force it to use TCP instead of a unix socket.

EEAA
  • 109,363
  • 18
  • 175
  • 245
  • Thanks EEAA. The problem is that things like the Joomla installer fail. If the you specify `127.0.0.1` instead of `localhost` in the hostname field during installation, it causes installation paths to get messed up giving me other errors, but specifying localhost fails to connect the database! What's caused this situation? Why do I have it? – Peter White Oct 07 '13 at 01:37
  • 3
    @PeterSnow Set up a Unix domain socket in your MySQL configuration. – Michael Hampton Oct 07 '13 at 01:38
  • Thanks @MichaelHampton I've searched for the last half hour and can't find how to set up this. Can you point me at an article or give me some idea please? I do have `socket = /var/run/mysqld/mysqld.sock` in `my.cnf` and the file is existing, owned by mysql with permissions 777. Thanks. – Peter White Oct 07 '13 at 02:51
  • @PeterSnow Did you restart mysqld after making that change? – EEAA Oct 07 '13 at 03:13
  • Currently I'm trying to use it via the Joomla install script. The http page asks for the hostname. If I choose `127.0.0.1` it works (but the script uses the entry later and fails because it expects resolvable name like `localhost`), but if I use `localhost`, connecting, to create the database, fails. – Peter White Oct 07 '13 at 03:19
  • @PeterSnow Are you reading what we are writing? You configured `socket` in your mysql configuration. Have you restarted `mysqld` since adding that? Also, does the socket file actually exist? – EEAA Oct 07 '13 at 03:23
  • I didn't configure a socket in mysql. It looks like it is already existing. I posted information about that (above) and asked for more details on what needs to be configured. As it happens though, I have restarted both apache and mysqld several times in the course of attempting other tweaks (none of which have worked yet). – Peter White Oct 07 '13 at 03:36
1

One thing to note if the grants are correct is if your using MariaDB there was a bug which caused this exact behaviour in version 10.0.1 and 5.5.30 https://mariadb.atlassian.net/browse/MDEV-4356

Scott Mcintyre
  • 269
  • 1
  • 2
1

You can check permission like this :

[learner@localhost ~]$ sudo netstat -npa | grep mysqld  
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      6284/mysqld         
unix  2      [ ACC ]     STREAM     LISTENING     233040 6284/mysqld         /var/lib/mysql/mysql.sock

If this is listening on 0 0.0.0.0:* it means that every one can access that mysql.

you can edit binding for this in my.conf file. In linux default path for this is :

[learner@localhost ~]$ sudo vi /etc/my.cnf

Add these lines in my.conf file for allowing ip that you want.

bind-address = 127.0.0.1   
bind-address = 0.0.0.0
Giacomo1968
  • 3,542
  • 27
  • 38
a_learner
  • 11
  • 2
0

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
joelschmid
  • 177
  • 2
  • 11
0

I solved this problem by creating symlink, sudo ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock. But first I have to create folder /var/run/mysqld/ because the folder didn't exist initially.

chicks
  • 3,793
  • 10
  • 27
  • 36
saharul
  • 9
  • 1
-1

You need a mysql.sock in /tmp

ln -s /var/lib/mysql/mysql.sock mysql.sock

should work fine after that.

William
  • 1