How to reset MySQL root password

How to reset MySQL root password

by

It’s hard to memorize all the passwords now a days, especially when you set a very Strong Password with a combination of letters, special chars and numbers. My blog is hosted on WordPress. I lost all passwords stored in Google Chrome and so I lost it for the my WordPress admin account too. Since it’s a hosted WordPress on a Ubuntu Virtual Machine with MySQL, I didn’t have access to cPanel to reset the password. I had the only option to connect to the MySQL database and reset the password. But guess what! I forgot the password for MySQL as well. ?

Struggle of two hours on internet

I’m not an Ubuntu/Linux expert so I googled “How to reset MySQL root password on Ubuntu 18.04” and I found lots and lots of solutions. None of them worked for two hours!

My current MySQL version is 5.7.29. From the google search I found lots of the solutions which didn’t work for me and I later realized that they’re applicable on previous or other different versions MySQL.

I tried various combinations from the articles and was able to reset the MySQL root password successfully after a long hiccup.

How to reset MySQL root password on Ubuntu 18.04

You can verify the MySQL version using “mysql -V” command on the terminal. Here’s my output:
mysql -V : mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

Steps to reset the root password

Note: you can skip the “sudo ” from the commands if you’ve logged in as a super user.

Step 1: Stop the MySQL Service

sudo /etc/init.d/mysql stop

Step 2: Confirm if the directory /var/run/mysqld exists and the current user has ownership

sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld

Step 3: Start MySQL Daemon service with the –skip-grant-tables option. Notice the ampersand ‘&’ in the end, it’s required. After the command runs – press ENTER key to get back to the prompt.

sudo mysqld_safe --skip-grant-tables &

# You should see the following output:

[1] 19564
admin@blogserver:~$ 2020-03-18T21:15:59.872516Z mysqld_safe Logging to syslog.
2020-03-18T21:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2020-03-18T21:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Step 4: Log into MySQL without a password

sudo mysql --user=root mysql

# if you see the MySQL prompt("mysql>"), Voila!! You're into it now and can change the password for any user, including root.

Step 5: Run the following command on mysql prompt to update the password for the root user, flush privileges, and exit from the prompt.

mysql> UPDATE user SET authentication_string=PASSWORD('new-strong-password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Step 6: For safer side, kill all the running mysql processes and start it again, and try connecting to MySQL instance with new password.

# Kill all running MySQL processes
sudo killall -u mysql

# Start MySQL service
sudo /etc/init.d/mysql start

# Connect to the database using new password
sudo mysql -u root -p

# Enter new password and you should be able to see the welcome message from MySQL service.

You’re done! Your MySQL root password is successfully reset.

I really spent hours finding the right solution and hence I compiled this article to help people save time wandering on the various different articles on google. Hope it helps someone!

Happy Learning. ? Explore more article from DevsDaily!

Leave a Comment