MySql or MariaDB: Lost Root Password Resetting

Here’s my personal notes on resetting the root password for a MySql or MariaDB database running on Ubuntu Linux. Maybe it’s useful to you too.

I assume the machine is running a gnome desktop manager.

Open a terminal with ctrl-alt-t or do whatever you need to open a terminal.

Stop mysql by issuing the following command at the command prompt:

sudo service mysql stop

(Note: To stop MariaDB you can enter exactly the same command. You do not need to change “mysql” into “mariadb”)

Now we need to start the safe version of the database as so:

sudo mysqld_safe –skip-grant-tables &

( the ampersand (&) at the end runs mysql in the background so you do not lock this terminal and you can continue access on this terminal)

Now we need to login as root to the safe version. This has no password!

sudo mysql -u root

Now we are logged in (without a password) we need to tell it what we are using. Again if you are using MariaDB using the line below is still perfectly fine. Remember we are now running the database command interpreter so we need to end all commands with a semi colon.

use mysql;

Then we need to issue the sql command to do the root password resetting so that later when we are not in safe mode we can log in with a password.

Type the following (all on one line – obviously replace mynewpassword with your chosen new password):

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

To finally get it all sorted out there’s one more command:

flush privileges;

Then finally we log out of the safe version of MySql/MariaDB

quit;

As we are now back in a linux shell we do not add semi colons to the end of commands!

Now we need to stop the safe version of MySql and restart the normal version. To do this we simply stop and start the sql service.

sudo service mysql stop
sudo service mysql start

So now when you login into (say) phpmyadmin as root – just use the new password you chose above. (and don’t forget it again!)