How Can I Recover Access to MySQL When I've Forgotten the Root Password?

Regaining Access to MySQL with a Forgotten Root Password

Encountering the dreaded "Access denied for user 'root'" error can be a major roadblock when attempting to administer your MySQL database. While the technique mentioned in the original question, involving an UPDATE statement, might not have solved the issue, there is a straightforward solution that can restore access to MySQL with a reset root password.

To proceed, follow these steps:

1. Stop MySQL: Use the command sudo stop mysql to halt the MySQL service.
2. Start MySQL with Special Options: Start MySQL using sudo mysqld --skip-grant-tables --skip-networking. These options disable authentication and network access for this instance.
3. Connect to MySQL: Execute mysql to establish a connection to MySQL without authentication.
4. Reset Root Password: Issue the statement update mysql.user set password = password('your_new_password') where user = 'root'; to reset the root password to your desired value.
5. Flush Privileges: Run flush privileges; to update the user privilege cache.
6. Exit MySQL: Use exit; to close the MySQL session.
7. Shut Down MySQL Administration: Execute sudo mysqladmin shutdown to stop the MySQL service administration.
8. Start MySQL normally: Finally, use sudo start mysql to restart MySQL normally with the modified root password.

Now, you can regain access to MySQL using your newly set root password. This approach is particularly useful when the original password is lost or forgotten, allowing you to regain administrative control of your database system.

The above is the detailed content of How Can I Recover Access to MySQL When I've Forgotten the Root Password?. For more information, please follow other related articles on the PHP Chinese website!