Why Am I Getting 'Access denied for user 'root'@'localhost'' in MySQL and How Can I Fix It?

Access Denied for User 'root'@'localhost' Due to Missing Privileges

Issue:

Users attempting to access MySQL as the root user encounter the "Access denied for user 'root'@'localhost' (using password: YES)" error, indicating that the root user lacks the necessary privileges to establish a connection.

Explanation:

By default, MySQL 5.7 and higher primarily rely on socket authentication for local connections. This means that attempting to connect via the command line as "sudo mysql" will not require a password. However, this authentication method does not grant the root user any specific privileges.

Solution:

To establish a password-based connection and grant the root user privileges:

1. Run the "SELECT user, authentication_string, plugin, host FROM mysql.user;" query to check the current user configuration. Verify that the authentication plugin for the root user is "auth_socket."
2. Execute the "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';" statement to modify the root user's authentication method from socket to native.
3. Run the "FLUSH PRIVILEGES;" command to ensure that the changes take effect.
4. Repeat step 1 to confirm that the root user's authentication plugin has been changed to "mysql_native_password."

Additional Considerations:

• The "Current-Root-Password" should be replaced with the existing root user's password.
• For MySQL versions prior to 5.7, the authentication method may be "caching_sha2_password." In this case, use the "ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Current-Root-Password';" statement instead.
• For MariaDB users, the following command can be used to set a password for the root user: "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');"

The above is the detailed content of Why Am I Getting 'Access denied for user 'root'@'localhost'' in MySQL and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!