localhost vs. 127.0.0.1 in MySQL: Why the Difference and How to Grant All Privileges?

Understanding the Distinction: localhost vs. 127.0.0.1 in MySQL

When attempting to connect to a MySQL database using the command-line interface, users often encounter a discrepancy between using localhost and 127.0.0.1 as the hostname. This article delves into the underlying cause of this difference and provides a solution for granting all database privileges from all hosts.

Socket Connections and Hostnames

In UNIX systems, MySQL uses sockets for connections made without a hostname or with the hostname localhost. This means that there is a difference between these two forms of connection.

Impact on GRANT System

The GRANT system in MySQL distinguishes between these different connection types, resulting in the observed discrepancy.

Granting ALL Privileges from ALL Hosts

To grant all database privileges to the root user from all hosts, execute the following command:

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

This command ensures that the root user can access all databases from any host.

Troubleshooting

If the granted privileges are not working as expected, it is essential to verify the following:

• Check the skip_networking variable to ensure it is set to OFF:
  SET GLOBAL skip_networking=OFF;
• Confirm that the root user has the appropriate hostnames listed in the mysql.user table:
  SELECT user, host FROM mysql.user WHERE user='root';

The above is the detailed content of localhost vs. 127.0.0.1 in MySQL: Why the Difference and How to Grant All Privileges?. For more information, please follow other related articles on the PHP Chinese website!