Reset MariaDB Root Password on Ubuntu 17.04 | 17.10

Lost your MariaDB root password? Well, you’re in luck because this brief tutorial is going to show students and new users how to reset MariaDB root user password. This tutorial should apply to Ubuntu and other Linux distributions.

When you forget MariaDB root password you will be denied access to the database. No matter what you do, you won’t be able to access to the database server without the correct password. To log back into the database you must gain access to the server and create a new password for the root user.

To regain access to the database server after forgetting the password is pretty easy. There’s a trick that allows one to access the server through a backdoor and reset the password.. and the steps below show you how to do that.

So, when don’t remember MariaDB root user  password and can’t logon to the database server, follow the steps below;

Step 1 Stop the MariaDB Server

To reset the root password using this method you must first stop the database server. To do that, run the commands below

sudo systemctl stop mariadb.service

Step 2: Gain access to the database server

After stopping the database server, the next step is to gain access to the server through a backdoor by starting the database server and skipping networking and permission tables. This can be done by running the commands below.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Step 3: Reset MariaDB root Password

Now that the database server is started in safe mode, run the commands below to logon as root without password prompt. To do that, run the commands below

sudo mysql -u root

Then run the commands below to use the mysql database.

use mysql;

Finally, run the commands below to reset the root password.

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

Replace new_password _here with the new password you want to create for the root account, then press Enter.

After that, run the commands below to update the permissions and save your changes to disk.

flush privileges;

Exit and you’re done.

Next start MariaDB normally and test the new password you just created.

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

Logon to the database by running the commands below.

sudo mysql -u root -p

Then type new password you created to logon.

reset mariadb password

That’s it!

Summary:

This post shows students and new users how to reset forgotten MariaDB root password on Ubuntu systems. When you find yourself in a situation where you don’t remember the root password for MariaDB, apply the steps above to regain access to the system and reset the password.

Enjoy!

You may also like the post below:

Configure Remote Access to MySQL / MariaDB Databases

6 Replies to “Reset MariaDB Root Password on Ubuntu 17.04 | 17.10

  1. For MariaDB on Ubuntu 18.04, nothing was working until I used this comand to reset the password. The plugin bit is important, I guess.

    UPDATE mysql.user set password=password(‘newpassword’), plugin=’mysql_native_password’ where user=’root’;

  2. after `mysqld_safe ‐‐skip-grant-tables &`
    I get no command prompt (hitting return just brings me back to my terminal prompt):
    and entering
    mysql -u root mysql
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
    Any ideas?

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.

%d bloggers like this: