$ 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)
Enter 127.0. 0.1 for the host. The default username for a new MySQL installation is root, with a blank password. You can leave the port field blank unless your server uses a different port than 3306.
normally means that there is no MySQL server running on the system or that you are using an incorrect Unix socket file name or TCP/IP port number when trying to connect to the server. You should also check that the TCP/IP port you are using has not been blocked by a firewall or port blocking service.
Connection Refused (IP Address) If you are getting this error: Check the IP address in the connection string or use the database's hostname. We recommend using the hostname over the IP address because the IP address may change, but the hostname will always remain the same.
'%' mean you can login into database from any host connected to those database. You also define your localhost as host if you want to access database from localhost. to change your password: SET PASSWORD FOR 'me'@'%' = PASSWORD('letmein');
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With