How to reset MariaDB root password

Why Access denied for user ‘root’@’localhost’ (using password: YES) occurred?

The error occurred because you’ve forgotten your mariadb password or the password given by you is not correct. But, don’t worry, forgetting passwords happens to every IT professional. It’s common in the IT industry. In this article we’ll see how to fix the ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) error by resetting your mariadb password in just 3 steps that is very simple to follow.

In order to recover your mariadb password, you will need an account in your system server with sudo permission so that you can perform the given suggested tasks without any hassle.

Step-1: Stop your mariadb server.

To stop the mariadb server, type the command given in the below image.

sudo systemctl stop mariadb.service

Step-2: Restart the server without permissions checking.

Running the mariadb server without permission checking allows us to access the database without providing the password. How amazing is that! Isn’t it?

To do this, you’ll need to stop the database from loading the grant tables, which stores the user privilege data.

Depending on which mariadb database version you’ve installed, the way of starting the database server without loading the grant tables differs. But, don’t worry, the probability of you’re using the same version as in the article shown is very very high.

Execute the following command given in the image, which sets the MYSQLD_OPTS environment variable used by the mariadb upon startup.

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

After setting the environment variable, start the mariadb server by following the command given in the below image.

sudo systemctl start mariadb.service

The above command will not show any output, but it will simply restart the mariadb server. You can check and ensure it is started by entering the following command in your terminal.

sudo systemctl status mariadb.service

After ensuring that the server is running properly, you should be able to connect to the mariadb server as a root user without providing the password.

To login without password, just run this command in your terminal. “ mariadb -u root ”

After successful login to your server with root user, you’ll get the prompt as shown in the image below.

Congratulations, now that you have the access to your mariadb server, you can change the password of the root user as shown in step-3.

Step-3: Change the root user password.

One simple method to change the root user password is by executing the ALTER USER command. However, as of now our database server is running in a limited mode because the grant tables are not loaded yet and there is networking support is disabled. This lets you login into the server without providing the password, but it will not let you execute the commands that alter the data. To reset the root password, you must load the grant tables and enable the networking support.

Now, let’s tell the database server to reload the grant tables by executing the following command shown in the image below.

FLUSH PRIVILEGES

After that, you can now change the password. Run the following command then set the new password for the root account.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'

Make sure that you replace the new_password with the strong password to secure your database from any unwanted threats.

Now, your root account password has been changed. We need to tell the database to use its default authentication mechanism for the new password you assigned to the root user.

For that, run the following commands one after.

UPDATE mysql.user SET authenticaation_string = '' WHERE user = 'root'

UPDATE mysql.user SET plugin = '' WHERE user = 'root'

Now, everything is set properly, now we’ve to revert the changes you have made in step-2 so that the grant tables will load properly and the root user has to provide a password to login into the database. Just imagine anyone can login to your database with root user without providing a password. How amazing and a great layer of security is that. Isn’t it?

To revert the database back to normal mode, we simply need to unset the MYSQLD_OPTS environment variable we have set earlier. To unset it, run the following command in your terminal.

sudo systemctl unset-environment MYSQLD_OPTS

After unsetting the environment variable, you need to restart your mariadb server.

Just run the command given in the image below.

sudo systemctl restart mariadb.service

The database will restart now and will get back to its normal state. Now, you can confirm the changes by login back to the server by using the new password you’ve created.

To login with the password, just execute the given command as shown in the image.

mariadb -u root -p

The above command will prompt you to enter the password. Just type the new password you have created and you will gain access to the database without any problem.

Conclusion

By now, after following the article, you have the administrative access of your mariadb server.

Once again I will remind you that, make sure the new password of your root user is strong, secure and don’t forget to remember that password.

Now, don’t forget to bookmark this page for future reference in case you forget the new password you’ve created.