Do you want to reset the MySQL root password? Sometimes we forget the root password and not able to log in on MySQL, and we want to reset the password. It is happening with all of us most of the time.

In this article, we will explain the process to reset the MySQL root password from the command line utility.

Identify the Server version

To execute any command on MySQL or MariaDB, first, we should know which version of the database system is running on the system. The different commands to recover root password depends on the MySQL or MariaDB version.

You can find the version of your database system using the following command:

$ mysql --version

If you are using MySQL you will get the output similar to below:

Output:
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

Or you will get the output like below if using MariaDB:

Output:
mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

How to reset MySQL or MariaDB root password

To reset the MySQL or MariaDB root password, you need to follow the below steps:

Step 1 – Stop the MySQL/MariaDB service

In the first step, you need to stop the MySQL server, by using the following command:

$ sudo systemctl stop mysql

Step 2 – Start MySQL/MariaDB server without loading the grant tables.

To start the database server without loading the grant tables using the following command:

$ sudo mysqld_safe --skip-grant-tables &

Using –skip-grant-tables option to ignore grant table loading and it allows anyone to access your database server without a password and with all privileges.

The ampersand & at the end of the command is use to execute or run the program in the background, which allows you to continue to use the shell.

Step 3 – Log in to MySQL/MariaDB shell.

Now you can connect to the database server as the root user without a password as shown below:

$ mysql -u root

Step 4 – Set a new root password.

To set the new password for MySQL 5.7.6 and later or MariaDB 10.1.20 and later, run the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
mysql> FLUSH PRIVILEGES;

If ALTER USER command doesn’t work for you, you can modify the user table directly by using below commands:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
 WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

Run the following command if you are using MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;

In both of cases if command executes successfully, you will see the following output:

Output
Query OK, 0 rows affected (0.00 sec)

Step 5 – Stop and start MySQL/MariaDB server normally.

Now, the new root password is set, stop the database by running the following command:

$ mysqladmin -u root -p shutdown

Your screen will prompt to enter the new root password for confirmation to stop the service.

Start the database server in normal mode, as shown below:

For MySQL database server, run:

$ sudo systemctl start mysql

For MariaDB database server, run:

$ sudo systemctl start mariadb

Step 6 – Verify the newly created password

You can verify the newly created password is applied correctly try to log in on database server, as shown below:

$ mysql -u root -p

The output of the above command will prompt to enter the new root password. Put your password and hit enter, and you should log in to your database server.

Conclusion

You have learned how to reset the MySQL/MariaDB root password. The given instruction here will work with all Linux distribution including Ubuntu 18.04, 20.04 CentOS 7, 8 and Debian 9, 10 etc.

If you have any doubt or feedback, please comment below.