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)
Solution 1:
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.
Solution 2:
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.
Solution 3:
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