How to reset the mysql root password if you forgot it

Recently I forgot root password of the MySQL server which runs on my Digital Ocean Droplet (Ubuntu 16.04). Since I have not set up any other accounts, I was stuck without being able to log into the database. I had to figure out a way to reset the password. Below I am sharing with you how I did it.

Connect to the server via SSH with your SSH client program.

Before proceeding check your mysql version with below command:

$ mysql -V

Make a note of Distrib number (here it is 5.7.13) of the response

mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

Check whether the mysql service is running.

$ systemctl is-active mysql.service

If it is ‘inactive‘ we are good to proceed. If it is ‘active‘ you got to stop it with below command:

$ sudo systemctl stop mysql.service

Ok. Now we have to manually restart mysql service with –skip-grant-tables option.

$ sudo mysqld_safe --skip-grant-tables &

(Note: This enables anyone to connect to the mysql server without a password and with all privileges. So keep in mind that this is insecure.)

Next, login to the mysql server with ‘mysql’ client. You don’t have to give any username or a password as the server has started with –skip-grant-tables which bypasses authentication.

$ mysql

You should see the familiar mysql> prompt.

Now we have to reload the grant tables in the server with below command:

mysql> flush privileges;

If successful, it will show

Query OK, 0 rows affected...etc.

Finally, to set a new password for the account you need (in this case its ‘root’ account) execute below command at the mysql> prompt:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yournewpassword';

If it shows ‘Query OK, …’ the password has been updated successfully.

Now you can exit the mysql client by typing exit;

mysql> exit;

Before trying to login with the new password, first you have to close down manually started mysql server. Since we’ve started the mysql server manually, we have to find the running mysql process and close it.

Enter below command to see the running processes:

$ ps -ax

Check the resulting process list to see if you can find processes similar to the screenshot below:

Note the left most number(PID) of each process that is referring to mysql*

Now kill each process with below command:

$ sudo kill PID

If the server successfully ended, you should get a message saying:

'... .mysqld from pid file /var/run/mysqld/mysqld.pid ended'

Finally start the mysql service properly with systemctl:

$ sudo systemctl start mysql

Check the status:

$ systemctl is-active mysql.service

If it says ‘active‘ server is running properly. Try logging into the root account as shown below:

$ mysql -u root -p

Enter the new password when prompted. Ff everything has gone well, you should get the mysql> prompt as usual.

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar



Do NOT follow this link or you will be banned from the site!