Forgot the MySQL server root password?

After forgetting or losing the MySQL root password, there is no need to panic. In this tutorial I will show you how to reset the MySQL root password quickly and easily.

Resetting the root password is a simple process. This tutorial shows the simplest method of several for resetting the MySQL root password.

The instructions apply only to the open source database MariaDB, the popular fork of the MySQL database server. Additional shell commands are used that are not available in MySQL.

MySQL Root Password Reset

The following few steps require shell access with a local user account on the system running MariaDB/MySQL, the user must provide sudo rights to execute commands in the terminal shell with elevated privileges.

Stop MySQL server

First stop the MariaDB/MySQL server and terminate the service.

$ sudo systemctl stop mysqld

Start MySQL server without password

Now start a temporary SQL Server instance as follows.

$ sudo -u mysql mysqld --skip-grant-tables &

When the MySQL server is started with the --skip-grant-tables option, the grant tables aren’t loaded, so no access control is applied. Users are able to access the MariaDB/MySQL server now without a password.

You can now log in to the MariaDB/MySQL server as root in a terminal shell without requiring a password.

$ mysql -u root

MySQL Root Password Reset

Next run the command to reset the current permissions.

FLUSH PRIVILEGES;

Now reset the MySQL root password with the following command.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

Replace the placeholder “NewPassword” with the desired password.

Finally, re-read the permissions and exit MySQL.

FLUSH PRIVILEGES;
quit

The MySQL root password has now been changed, now the previously started temporary MySQL server is stopped. This can be achieved with the following shell command.

$ ps auxw | grep '\-\-skip-grant-tables' | tail -n 1 | awk '{ print $2 }' | sudo xargs kill

Restart MySQL server

Now restart the MariaDB server and log in with the new password.

$ sudo systemctl start mysqld
$ mysql -u root -p

The MariaDB/MySQL server is under full control again.

sudo -u mysql mysqld --skip-grant-tables & after hit the command sometimes you have to press Enter or hit Ctrl+C

About MariaDB

MariaDB is a free, open source relational database management system that was created by a fork of MySQL. The project was initiated by MySQL’s former lead developer Michael Widenius, who also developed the storage engine Aria. The structure that MariaDB originally had includes the software layer, which contains the basic functionality of the database, i.e. creating, reading, modifying, deleting data.

Addition

Alternative command to change the MySQL root password.

UPDATE USER set password=PASSWORD('NewPassword') where User='root';
FLUSH PRIVILEGES;
quit

Conclusion

This tutorial shows how to reset the forgotten or losing MySQL server root password. The steps were performed in the GNU bash on an Ubuntu 22.04.3 LTS in WSL and on a virtual Debian 12 (Bookworm) GNU/Linux running MariaDB 10.6.18 database server.