A Comprehensive Guide to Resetting the Root Password in MySQL 8.0

In the unfortunate event of forgetting or misplacing your MySQL root password, it becomes crucial to regain access to your database. The root password is securely stored in the users table, necessitating a method to bypass MySQL authentication and update the password record.

Fortunately, there's a straightforward solution, and this tutorial will walk you through the process of recovering or resetting the root password in MySQL 8.0.

As per the official MySQL documentation, there are two primary methods to reset the root MySQL password, both of which we will cover in detail.

Method 1: Reset MySQL Root Password Using --init-file

One approach to reset the root password involves creating a local file and initiating the MySQL service with the --init-file option. Follow these steps:

1. Create a file, for example, /home/user/init-file.txt, and ensure it's readable by the MySQL user.

2. Within the file, insert the following command, replacing 'new_password' with your desired password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

3. Stop the MySQL service:

# systemctl stop mysqld.service     # for systems using systemd 
# /etc/init.d/mysqld stop           # for systems using init

4. Start the MySQL service with the following command:

# mysqld --user=mysql --init-file=/home/user/init-file.txt --console

5. The MySQL service will start, and the init-file you created will execute, updating the root user's password. Be sure to delete the file once the password has been reset.

6. Stop the server and restart it normally:

# systemctl stop mysqld.service        # for systems using systemd 
# systemctl restart mysqld.service     # for systems using systemd 

# /etc/init.d/mysqld stop              # for systems using init
# /etc/init.d/mysqld restart           # for systems using init

You should now be able to connect to the MySQL server as root using the new password:

# mysql -u root -p

Method 2: Reset MySQL Root Password Using --skip-grant-tables

The second method involves starting the MySQL service with the --skip-grant-tables option. This approach is less secure, as it allows all users to connect without a password while the service is running in this mode. However, it can be useful in certain situations. Follow these steps:

1. Ensure the MySQL service is stopped:

# systemctl stop mysqld.service     # for systems using systemd 
# /etc/init.d/mysqld stop           # for systems using init

2. Start the MySQL service with the --skip-grant-tables option:

# mysqld --skip-grant-tables --user=mysql &

3. Connect to the MySQL server:

# mysql

4. Since account management is disabled with --skip-grant-tables, you must reload the privileges:

# FLUSH PRIVILEGES;

5. Update the root password with the following command, replacing 'new_password' with your desired password:

# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

6. Stop the MySQL server and start it normally:

# systemctl stop mysqld.service        # for systems using systemd 
# systemctl restart mysqld.service     # for systems using systemd 

# /etc/init.d/mysqld stop              # for systems using init
# /etc/init.d/mysqld restart           # for systems using init

You should now be able to connect with your new password:

# mysql -u root -p

Conclusion

In this comprehensive guide, you've learned how to reset the root password for your MySQL 8.0 server. We hope this step-by-step process has made it easy for you to regain control of your database.