How to recover a lost MySQL root password

How to recover a lost MySQL root password

0

Read Time:2 Minute, 56 Second

In this article we are going step by step on how to recover a lost MySQL root password procedure. This happen (to me ) a lot because usually MySQL is one of those thing that you configure and then leave there for a while until a problem arises or like in my case..I created for a tutorial and now I need to access.

Please note that this should be an emergency procedure not something that you do everyday.

Let’s start with creating a backup of the MySSQL sock folder. This step is needed because when the mysql service is stopped this folder is being deleted and we will not be able to connect to MySQL again for changing the password.

brewed@brilliance:~$ sudo mv /var/run/mysqld/ /var/run/mysqldb

Then we can stop the the service

brewed@brilliance:~/$ sudo /etc/init.d/mysql stop
[sudo] password for brilliance:
brewed@brilliance:~/$ sudo service mysql status
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Thu 2021-07-22 06:10:00 UTC; 7s ago
    Process: 33714 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 33714 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"

Jul 07 08:10:27 brilliance systemd[1]: Starting MySQL Community Server...
Jul 07 08:10:28 brilliance systemd[1]: Started MySQL Community Server.
Jul 22 06:09:59 brilliance systemd[1]: Stopping MySQL Community Server...
Jul 22 06:10:00 brilliance systemd[1]: mysql.service: Succeeded.
Jul 22 06:10:00 brilliance systemd[1]: Stopped MySQL Community Server.

Now we need to start the MySQL instance without the password and in the background (the & at the end of the command) but before that we need to copy back the sock folder

brewed@brilliance:~$ sudo mv /var/run/mysqldb /var/run/mysqld
brewed@brilliance:~$ sudo mysqld_safe --skip-grant-tables --skip-networking &

And then we can safely connect to the mysql shell without the root password

brewed@brilliance:~$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

This is the step where we change the password

use mysql;
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123';
exit;

Now we can stop and start MySQL

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Let’s verify that the password reset emergency procedure worked as expected

mysql -uroot -ppass123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

Job done!

I hope this one will make your life way easier in emergency circumstances

If you liked or found this helpful please share!