How Can I Restore Root Access in MySQL When Access is Denied?

Access Denied for User 'root'@'localhost' - No Privileges: Root Access Restoration

This issue arises due to a lack of privileges for the root user in MySQL. The root schema privileges are essential to perform administrative tasks and grant privileges to other users.

Causes:

• Privileges have been revoked or accidentally modified.
• Authentication method for root user has been changed to socket connection in MySQL 5.7 and above.

Solution:

For MySQL 5.6 and below:

1. Start MySQL with the -u root flag to gain root access without a password.
2. Run GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' to grant all privileges to the root user.
3. Flush privileges using FLUSH PRIVILEGES.

For MySQL 5.7 and above:

1. Check if the authentication plugin for root user is set to auth_socket. If so, update it to mysql_native_password using ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password'.
2. Flush privileges.

For MariaDB:

1. Use SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager').
2. Flush privileges.

Other Tips:

• If the error persists, check for any other security constraints or configuration issues.
• Use the SHOW GRANTS command to verify the privileges granted to the root user.
• Resetting the root password using the mariadb-secure-installation script may resolve the issue.

The above is the detailed content of How Can I Restore Root Access in MySQL When Access is Denied?. For more information, please follow other related articles on the PHP Chinese website!