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 mysqldStart 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 rootMySQL 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;
quitThe 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 killRestart MySQL server
Now restart the MariaDB server and log in with the new password.
sudo systemctl start mysqld
mysql -u root -pThe 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;
quitConclusion
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.
