How to Reset Root Password of MySQL 8 in Ubuntu 20.04 ?

Sometimes we forget or mistakenly type wrong password during the installation of mysql, So in this article i will help you to Reset Root Password of MySQL in Ubuntu 20.04. Mysql 8 has several new features and also it has change the default password algorithm for saving the password. Mysql 8 now using the caching_sha2_password algorithm to save the password in the database.

In the recent version of mysql we can easily change the password using the PASSWORD function of mysql but now PASSWORD function is deprecated in mysql 8 so we need a different technique to reset the password.

So today i come with a simple solution to reset the root password of mysql 8 in Ubuntu. Let’s start with step by step

Step 1 : Login in to the ubuntu server

As we all know first we need to login into the server or machine to access the mysql so ssh using below command

ssh username@domain_ip

Enter your password and you are into the server.

Step 2 : Update configuration of mysql

Next, we need to open my.cnf configuration file of mysql which is most probably located in /etc/mysql/my.cnf so open the file using vim or any your favorite editor

sudo vim /etc/mysql/my.cnf

Add below skip-grant-tables in [mysqld] block as below

[mysqld]

skip-grant-tables

Step 3 : Restart MySQL

We have changed the configuration files so we need to reload the mysql service

systemctl restart mysql

Check status again

systemctl status mysql

Step 4 : Login into Mysql and Change Password

Now, we can login without password as we have enabled the safe mode using skip-grant-tables

mysql -u root

Now update password to null

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

And again remove the skip-grant-tables from my.cnf file

[mysqld]

#skip-grant-tables

Step 5 : Restart and Login again without password

systemctl restart mysql

Next, we can login without password

mysql -u root -p

Step 6 : Update Root Password

Final step is to update the password of root so replace yourpasswd with your password

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';