Forgot MySQL Root Password – How To Retrieve It Back?

Have you forgotten your MySQL root password? How can you retrieve it back? What’s should you do now? Is this recoverable or not? What happens with my Databases? These are some common question which comes to our mind whenever we lose our MySQL root password. Don’t panic; it can be recovered, there is a quick and easy way to get back up and running. I’d like to share it with you a quick, simple, well written and easy method with will explains how you can reset the root password. Follows the steps below.

Steps to reset your MySQL root password

If you want to reset or change MySQL password or have forgotten the password for accessing phpMyAdmin then just follow the below step to reset the password or change the password. This process may vary according to the operating system.

Stop the MySQL service

sudo /etc/init.d/mysql stop

Start up safe mode

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL

Leave the current terminal as it is, and open a new terminal tab and login into root password.

mysql -u root

Update new MySQL root password

Execute the following command to update the root password:

update user set password=PASSWORD('') where User='root'; update user set plugin='' where User='root'; update user set authentication_string='' where User='root'; flush privileges; exit

Stop the MySQL service

sudo /etc/init.d/mysql stop

Login with Blank a password

Start the MySQL server and login with blank password

mysql -u root -p

Create New User

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123'; GRANT ALL PRIVILEGES ON * . * TO 'admin'@'localhost'; FLUSH PRIVILEGES;

Conclusion:

Using above example we can reset root password easily and provide a way to manage user account on MySQL. We have cover one of the best solution which you can use to reset the password. You can also join the conversation if you have any other solution or idea which we can use to make it more attractive and easy. That’s all folks and feel free to contact us in case if you have any query.

Reference:

Backup and Restore a Large MySQL database
Easy and scalable solution for mysql database backup to AWS S3 bucket