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.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

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

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Marin Todorov

I am a bachelor in computer science and a Linux Foundation Certified System Administrator. Currently working as a Senior Technical support in the hosting industry. In my free time I like testing new software and inline skating.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

1 Response

  1. Arun Chandel says:

    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.

Got something to say? Join the discussion.

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.