How to Resolve the MySQL Error 'Access Denied for User 'root'@'localhost' (using password: YES)'?

Unveiling the Mystery: Resolving 'Access Denied for User 'root'@'localhost' (using password: YES)'

The error message "Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?" can be encountered when attempting to connect to a MySQL database with the root user. This frustrating issue arises when the root user's schema privileges are null, effectively revoking all access privileges.

Tracing the Root of the Problem

Delving into the MySQL Workbench, it is discovered that the root user's schema privileges are indeed missing. This explains why standard commands like FLUSH HOSTS and FLUSH PRIVILEGES yield no results.

Reclaiming Root Access: A Step-by-Step Guide

1. Check User and Authentication Settings:

Run the query "SELECT user, authentication_string, plugin, host FROM mysql.user;" to determine the authentication method. If it shows 'auth_socket' for the root user, this indicates that socket authentication is being used, which requires root access to the server.

2. Modify User Authentication:

To allow connection with a password, update the authentication_string value for the root user at 'localhost' to 'mysql_native_password', which requires a password to authenticate.

3. Set the Root Password:

Use the command "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';" to set the root password.

4. Grant Privileges:

If necessary, grant the root user the appropriate privileges using the GRANT command.

5. Flush Privileges:

Execute "FLUSH PRIVILEGES;" to ensure the changes take effect.

6. Confirm Changes:

Rerun the user information query to verify that the root user now has 'mysql_native_password' authentication and the desired privileges.

By following these steps, root access can be restored, allowing for full control over the MySQL database.

The above is the detailed content of How to Resolve the MySQL Error 'Access Denied for User 'root'@'localhost' (using password: YES)'?. For more information, please follow other related articles on the PHP Chinese website!