How To Reset MySQL Or MariaDB Root Password

Long ago, I setup LAMP stack in my Ubuntu Mate. Only recent did I try to login as root user in my database server, but I completely forgot the password (dami na kasing password combination sa utak ko). After a couple of Google-fu and going through some blog posts, I successfully reset the password. For those wondering how to do this, I’ll make it as short and easy as humanely possible and explains how to reset a MySQL or MariaDB Root Password in Unix-like operating systems.

1. Open your Terminal  or any command line-like interface you have.

 In my case, I use Terminator.

2. Stop database server. Type the following command and hit Enter.

For MySQL: $ sudo systemctl stop mysql
For MariaDB: $ sudo systemctl stop mariadb

3. Restart the database server without permission checking using the following command:

$ sudo mysqld_safe –skip-grant-tables &

The –skip-grant-tables option allows you to connect without a password and with all privileges. If you start your server with this option, it also enables –skip-networking option 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 to reset the password.

4. Login to your MySQL/MariaDB server as root user:

$ mysql

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

UPDATE mysql.user SET Password=PASSWORD(‘NEW-PASSWORD’) WHERE User=’root’;

Replace NEW-PASSWORD in the above command with your own password.

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

FLUSH PRIVILEGES;

exit

5. 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.

6. Start database server normally using command:

For MySQL: $ sudo systemctl start mysql

For MariaDB: $ sudo systemctl start mariadb

7. Test the new password by logging in:

$ mysql -u root -p

And, that’s all for today. Only good things will happen!

Cheers!