Home MySQL How To Reset MySQL Or MariaDB Root Password

How To Reset MySQL Or MariaDB Root Password

By sk
Published: Last Updated on 7K views

A few months ago, I had setup LAMP stack in Ubuntu 18.04 server. Today, I tried to login as root user in my database server, but I completely forgot the password. After couple web searches and going through MySQL forum, I successfully reset MySQL root user password. For those wondering how to do this, this brief tutorial explains the steps to reset MySQL or MariaDB Root password in Linux and Unix-like operating systems.

Reset MySQL or MariaDB Root password

First, stop the database server.

If you use MySQL, type the following command and hit ENTER key.

$ sudo systemctl stop mysql

For MariaDB:

$ sudo systemctl stop mariadb

Next, restart the database server without permission-checking using the following command:

$ sudo mysqld_safe --skip-grant-tables &

Here, the --skip-grant-tablesoption allows you to connect without a password and with all privileges.

If you start your server with this option, it also enables --skip-networkingoption which is used to prevent the other clients from connecting to the database server.

And, the ampersand (&) symbol is used to run the command in background, so you could type the other commands in the following steps.

Please be mindful that the above command is dangerous and your database server becomes insecure. You should run this command only for a brief period of time to reset the password.

Next, login to your MySQL/MariaDB server as root user:

$ mysql

At the mysql> or MariaDB [(none)]> prompt, run the following command to reset the database root user password:

UPDATE mysql.user SET Password=PASSWORD('ostechnix') WHERE User='root';

Replace ostechnix in the above command with your own password.

Then, type following commands to exit from the mysql console.

FLUSH PRIVILEGES;
exit

Finally, shutdown the running database server that you started earlier with --skip-grant-tables option. To do so, run:

$ sudo mysqladmin -u root -p shutdown

You will be asked to enter your mysql/mariadb root user password that you set in the previous step.

Enter password: 
[2]- Done sudo mysqld_safe --skip-grant-tables

Now, start mysql/mariadb service normally using command:

$ sudo systemctl start mysql

For MariaDB:

$ sudo systemctl start mariadb

Verify if the password has really been changed using the following command:

$ sudo mysql -u root -p

And, that's all for now. Hope you find this useful.

You May Also Like

3 comments

matthew April 25, 2019 - 1:48 pm

By mariadb 10 (10.0.38-0ubuntu0.16.04.1) this no longer works as expected. Password is set in the mysql.user database but (1) as root it pays no attention to the password, you can type anything or nothing at the password prompt and you can log in and (2) as any other user ‘mysql -u root -p’ rejects the password.

Reply
Nissaar May 12, 2019 - 12:18 am

Run ‘ sudo mysql -u root -p ‘ instead of ‘ mysql -u root -p ‘
It should work

Reply
Jim April 21, 2020 - 8:59 am

The changes about the root user authentication are explained at:
https://mariadb.com/kb/en/authentication-from-mariadb-104/

in particular: “Using unix_socket means that if you are the system root user, you can login as root@locahost without a password. ”

— that is, if you run the login as the root user, which is why “sudo mysql -u root ” is necessary on Ubuntu/Debian style systems, etc.

Reply

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

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

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More