Why Does MySQL Treat `localhost` and `127.0.0.1` Differently When Granting Privileges?

Localhost vs. 127.0.0.1: The Curious Case of MySQL Privileges

When working with MySQL, it's common to encounter a seemingly confusing disparity between the terms 'localhost' and '127.0.0.1'. This difference manifests in the context of granting privileges to users within the database system.

To illustrate, consider the following example:

$ 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'

This discrepancy arises because MySQL differentiates between connections made using a host name (e.g., 'localhost') and connections made via a socket (e.g., '127.0.0.1'). The socket method, used when no host name is specified or when connecting with 'localhost', utilizes a local socket to communicate with the database server.

This distinction becomes significant in the realm of user privileges. Here's a simple guide on granting ALL privileges on ALL databases from ALL hosts for 'root':

1. Connect to MySQL using the root account:

   $ mysql -u root -p

2. Execute the following command to grant the privileges:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

3. Flush the privileges to ensure the changes take effect:

   FLUSH PRIVILEGES;

By following these steps, you can ensure that 'root' will have unlimited access to your database system from any host, regardless of whether it's connected via 'localhost' or '127.0.0.1'.

The above is the detailed content of Why Does MySQL Treat `localhost` and `127.0.0.1` Differently When Granting Privileges?. For more information, please follow other related articles on the PHP Chinese website!