How to reset MySQL root password on Ubuntu Server

MySQL root Password Reset

Just for some weird reason, you forget your root password, either you have changed it recently and just can not remember what you typed. Well do not give up just yet, there is a way out. To reset the password you would need physical access to the server, either via ssh or directory behind the server. A sudo account is required as well. Well, let’s get our hands dirty now.

There are two major ways to go around this:

Step One: Stopping and configuring MySQL

MySQL needs to be stopped before you proceed. Run the following command from your terminal

sudo /etc/init.d/mysql stop

sudo service mysql stop

Next we need to start MySQL in Safe Mode – That is to say, we will start MySQL but skip the User Privileges Table.

Again, note that you will need to have sudo access for these commands so you don’t need to worry about any user being able to reset the MySQL Root Password:

sudo mysqld_safe --skip-grant-tables &

The next thing to do is to log in from the terminal as root

mysql -uroot

mysql -u root

If the login is successful then change the database to MySQL with the following command

use mysql;

Reset the password by updating the user table with the MySQL command below:

update user set password=PASSWORD("mynewpassword") where user='root';

Note: Replace “mynewpassord” with the new password you want to reset to

Now you have to flush MySQL privileges so the changes are applied

flush privileges;

You now need to exit MySQL and stop and start the MySQL service with the following commands respectively

quit;

sudo /etc/init.d/mysql stop

sudo service mysql stopsudo /etc/init.d/mysql start

sudo service mysql start

Test the new password by logging in

mysql -u root -p

Note: This method is not regarded as the securest way of resetting the password. However, it works

Step Two: Reconfigure With dpkg-reconfigure

This method is much more secured and faster.

From the terminal, you find out the version of MySQL that is installed

apt-cache policy mysql-server

Check for the line which shows the installed version among other information. e.g. for my install, it’s:

Installed: 5.5.37-0ubuntu0.12.04.1

(From this I know that I have mysql-server-5.5 installed in my system.)

Now we use the dpkg command to reconfigure MySQL

sudo dpkg-reconfigure mysql-server-*.*

Note: Where mysql-server-*.* should be replaced by the version that you have. (for me it’d be mysql-server-5.5).

This will stop the database daemon.

A prompt will then appear where you’d have to enter your new password and confirm the reconfiguration. Type the new password in the window the shows up and repeat the password to confirm it

You can then log in with