Ressting MariaDB Root Password on Ubuntu 17.04 / 17.10

Sometimes we forget stuff… and when we do, we tend to go back in time to remember what was forgotten. The same is true with passwords. We humans do forgot when it comes to passwords… and that’s why there are password managers to help us store our passwords.

The day you forget your MariaDB database root password, the steps below will help you regain access to your system easily… This tutorial is going to show students and new users how to quickly regain access to MariaDB databases by resetting the root password.

When you’re ready to reset the root password for MariaDB, continue with the steps below

Setting MariaDB Root Password

To reset MariaDB root password, logon to the server and run the commands below to stop MariaDB database service

sudo systemctl stop mariadb.service

Then run the commands below to create a new mysqld directory if you don’t already have one… by default it should be created automatically when you first install MariaDB.

If the folder is already there and you run the commands, you’ll see an error that the folder already exist.. if so, continue with the next step.

sudo mkdir /var/run/mysqld/

and give mysql user access to it… this should be good if the folder already exist.. but run it anyway. Won’t hurt anything.

sudo chown mysql /var/run/mysqld/

After that, run the commands below to start MariaDB in safe mode by bypassing the standard authentication process..

sudo mysqld_safe --skip-grant-tables &

You should see something like this… you may have to press the Enter key

richard@ubuntu1710:/var/run$ sudo mysqld_safe --skip-grant-tables &
[1] 3055
richard@ubuntu1710:/var/run$ 171228 07:51:42 mysqld_safe Logging to syslog.
171228 07:51:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

Next, run the commands below to logon to the database server with the root account without typing a password

sudo mysql -u root

Then change to the mysql database by running the SQL command below

use mysql;

Finally, run the SQL statement below to change the root password

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

Save the change by running the commands below

flush privileges;
exit;

Finally, stop MySQL safe_mode and start MySQL default service

sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service

If you did everything as described above, you should be able to log back onto MariaDB database using the root new password.

sudo mysql -u root -p

Enjoy!

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.25-MariaDB-1 Ubuntu 17.10

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

This is how one resets MySQL root user.

You may also like the post below:

Install osCommerce on Ubuntu 17.04 / 17.10 with Apache2, MariaDB and PHP Support

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.