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 &  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 + 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
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
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: