How to Reset Root Password in MySQL 8.0

In an unfortunate event of forgetting or losing your MySQL root password, you will surely need a way to recover it somehow. What we need to know is that the password is stored in the users table. This means that we need to figure out a way to bypass the MySQL authentication, so we can update the password record.

Luckily there is an easy to achieve and this tutorial will guide you through the process of recovering or resetting root password in MySQL 8.0 version.

As per MySQL documentation there are two ways to reset the root MySQL password. We will review both.

Reset MySQL Root Password Using –init-file

One of the ways to reset the root password is to create a local file and then start the MySQL service using --init-file option as shown.

# vim /home/user/init-file.txt

It is important that you make sure that file is readable by the mysql user. Within that file paste the following:

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

In the above change “new_password” with the password that you wish to use.

Create File with New MySQL Root Password
Create File with New MySQL Root Password

Now make sure that the MySQL service is stopped. You can do the following:

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

Then run the following:

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

This will start the MySQL service and during the process it will execute the init-file that you have created and thus the password for the root user will be updated. Make sure to delete the file once the password has been reset.

Reset MySQL Root Password
Reset MySQL Root Password

Make sure to stop the server and start it normally after that.

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

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

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

# mysql -u root -p
Connect to MySQL with New Root Password
Connect to MySQL with New Root Password

Reset MySQL Root Password Using –skip-grant-tables

The second option we have is to start the MySQL service with the --skip-grant-tables option. This is less secure as while the service is started that way, all users can connect without password.

If the server is started --skip-grant-tables, the option for --skip-networking is automatically activated so remote connections will not be available.

First make sure that the MySQL service is stopped.

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

Then start the service with the following option.

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

Then, you can connect to the mysql server by simply running.

# mysql

Since account-management is disabled when the service is started with --skip-grant-tables option, we will have to reload the grants. That way we will be able to change the password later:

# FLUSH PRIVILEGES;

Now you can run the following query to update the password. Make sure to change “new_password” with the actual password you wish to use.

# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_passowrd';
Reset Root Password in MySQL 8
Reset Root Password in MySQL 8

Now stop the MySQL server and start it normally.

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

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

You should be able to connect with your new password.

# mysql -u root -p
Login to MySQL with New Root Password
Login to MySQL with New Root Password

You might also like to read these useful following MySQL related articles.

  1. How to Install MySQL 8 in CentOS, RHEL and Fedora
  2. 15 Useful MySQL Performance Tuning and Optimization Tips
  3. 12 MySQL Security Practices for Linux
  4. 4 Useful Commandline Tools to Monitor MySQL Performance
  5. MySQL Database Administartion Commands
Conclusion

In this article you learned how to reset lost root password for the MySQL 8.0 server. I hope the process was easy.

Tutorial Feedback...
Was this article helpful? If you don't find this article helpful or found some outdated info, issue or a typo, do post your valuable feedback or suggestions in the comments to help improve this article...

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

6 thoughts on “How to Reset Root Password in MySQL 8.0”

  1. The first method, using an init file, totally failed for me. I couldn’t restart mysql after that. I was able to get the --skip-grant-table method to work, however, I had to manually kill the mysqld process. systemctl stop mysqld would not stop it.

    Reply
  2. I followed both the methods.

    1. reset password through init file.
    2. --skip grant table option.

    But I stuck with both the methods. These methods did not work for me. As I tried to login to mysql database, showing error again.

    If you required snapshots, will share.

    Reply

Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.