Recently, I changed my website MySQL root user password. Then I thought what will happen if I forget the MySQL root password?

Is there an easy way to reset the MySQL or MariaDB root password?

I looked through some online tutorials, but none of them seem to be complete enough to differentiate between changing the password and resetting the password. They seemed lacking the details about the MySQL table where user passwords are stored and in which columns.

In this tutorial, we will learn the following topics.

  1. How to Change MySQL/MariaDB root Password
  2. How to Reset MySQL/MariaDB root Password

I will try to make it as complete as possible, and hopefully after reading this, you will get through this task easily without the need of any further help.

What is the difference between changing and resetting the password?

If you know the root password, you can connect to the database as root user and then change the password very easily. You can change the root password as well as any other user password.

If you have forgotten the root password, means you can’t connect to the MySQL server as root user. The root user has the highest privileges and you can’t change its password through other accounts. We have to perform some additional steps in this case to reset the MySQL root password.

Steps for MySQL and MariaDB are Same?

MariaDB is built on top of MySQL. It’s very popular for web hosting requirements. In fact, JournalDev and all my websites are using MariaDB database. Any command that works for MySQL will work for MariaDB as well.

The only adjustment in the commands you might have to make is to stop and start of MySQL server. I am using Ubuntu for this tutorial and I use systemctl to start/stop services. You can also use /etc/init.d/mysql to perform the same operations.

If you are on Windows OS, then please use mysqld or mysqladmin from the command prompt to start or stop the MySQL server. They are located in the MySQL installation bin folder.

How to Change MySQL or MariaDB root Password

I am using MariaDB database, we can use –version option to find out its version.

1. Connect to MySQL as root user

2. Change the password and authentication_string value in mysql.user table

MySQL user passwords are stored in mysql.user table password and authentication_string columns in the encrypted form. We can use the PASSWORD() function to convert a plain-text string to the encrypted value and set these columns.

Let’s understand what’s happening in the above queries.

  • First of all, we are changing the database to ‘mysql’
  • Then, we are setting ‘authentication_string’ and ‘password’ column values for ‘root’@’localhost’ user with the new password.
  • Then we are reloading the grant tables using the FLUSH PRIVILEGES command.
  • Then quit the MySQL session. The root password has been changed successfully.

NOTE: I tried to use ALTER USER command to change the root password, but it didn’t work.

3. Verify the root user login using new password

That’s it. We have successfully changed the MySQL/MariaDB user password.

How to Reset the MySQL/MariaDB root Password

If you have forgotten the root password, then we need to perform one extra step so that we can login to the MySQL terminal without providing the password.

1. Stop the MySQL Server

You can also run systemctl stop mariadb, the effect will be the same.

2. Starting the MySQL Server without permission checking setting

The idea is to start the MySQL server without loading the grant tables information, so that we can login as root user without providing password.

It’s a security risk to run MySQL server like this, so it must be done briefly and shut down immediately after resetting the root password.

We can start the MySQL server in safe mode and pass –skip-grant-tables option to skip loading grant tables that store the user privileges settings.

It is important to run the command ending with & so that it runs in the background. I am also passing –skip-networking option to skip networking that prevents other clients from connecting to the MySQL server.

3. Connect to MySQL Server as root without passing password

Notice that we are not providing root password, but still we are able to connect to MySQL server.

4. Reset the root password in mysql.user table

Mysql Reset Root Password

5. Stop and Start the MySQL server

First of all, we will kill the running MySQL server. The PID is present in the /var/run/mysqld/ file.

Now, start the MySQL server in the normal mode.

6. Verify by logging as root user with new password

If you will try to login as root without password, it will throw “Access denied” error.


MySQL root user is just like any other user. However, changing or resetting its password is a bit tricky because it’s the super user and we can’t change root password from another user login.

By admin

Leave a Reply