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:  19564 [email protected]:~$ 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!