Mysql localhost != 127.0.0.1?

$ mysql -u root -h 127.0.0.1 -e 'show tables' created_from_host;
+-----------------------------+
| Tables_in_created_from_host |
+-----------------------------+
| test                        |
+-----------------------------+

$ mysql -u root -h localhost -e 'show tables' created_from_host;
ERROR 1049 (42000): Unknown database 'created_from_host'

$ cat /etc/hosts
127.0.0.1       localhost.localdomain localhost
127.0.0.1       localhost
::1     localhost6.localdomain6 localhost6

How could it be? And main question - how to grant ALL privileges on ALL databases from ALL hosts for root?

UPD:

$ mysql -u root -h 127.0.0.1 -pzenoss -e "show grants";
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3715D7F2B0C1D26D72357829DF94B81731174B8C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
$ mysql -u root -h localhost -pzenoss -e "show grants";
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*3715D7F2B0C1D26D72357829DF94B81731174B8C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

UPD2:

zends> SHOW GLOBAL VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

zends> SELECT user,host FROM mysql.user WHERE user='root'; 
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| root | ::1                   |
| root | localhost             |
| root | localhost.localdomain |
+------+-----------------------+
4 rows in set (0.00 sec)

Answers


As you can see here, a UNIX mysqld uses sockets if used without a host name or with the host name localhost.

So it makes a difference, and in the GRANT system this difference becomes evident.


I know this tread is old, however was most probably not correctly answered.

By default, mysql does a name resolve so 127.0.0.1 and localhost will resolve in the same. However, you can switch the name resolution off in my.cnf:

skip-name-resolve = 1

Then localhost and 127.0.0.1 will NOT be the same anymore. So you either keep the name resolution, or restrict yourself to only using localhost in your grants or only 127.0.0.1 BUT: If you do the latter one, you will have to access our database with those credentials as well.


Log in through 127.0.0.1 and execute this statement:

SHOW GRANTS

You will probably see something like

GRANT ALL ... 'root'@'127.0.0.1'

I just confirmed on my local install and it seems MySQL won't auto-resolve the host name. You can either add another grant for localhost or just use 127.0.0.1


This caused issues in setting up a local dev environment on MacOS.

Several script functions, say mysqli_connect() in php might be using localhost to connect to MySQL: You can't (shouldn't have to) go about replacing localhost with 127.0.0.1 in every script.

Tried fixing this by adding bind-address=localhost, socket=/var/mysql/mysql.sock, socket=/tmp/mysql.sock etc. to the my.cnf file, but that didn't work.

To resolve the issue (on MacOS), note that the mysql.default_socket setting in /etc/php.ini file points to /var/mysql/mysql.sock, while MySQL's default_socket is /tmp/mysql.sock.

So, either edit your php.ini file, or create a softlink to MySQL's default_socket:

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

P.S - 127.0.0.1 is treated as a TCP connection and localhost as unix socket connection by MySQL: details here.


Need Your Help

Archive the artifacts in Jenkins

continuous-integration hudson archive jenkins artifacts

Could someone please explain to me the idea of artifacts in the build process?

Newline in PHP command line

php newline

I'm running a PHP script via the command line and trying to get output printed on new lines. I've tried all of the usual suspects (\n,\r,\l) but nothing is working. I'm accessing my Ubuntu server u...