Introduction

Have you forgotten the root password for MySQL database? Worry not, it happens, and there is a solution!

This tutorial will help you reset the root password for your MySQL/MariaDB database in case you’ve forgotten it. It is a simple guide that works with any modern Linux distribution like CentOS 7 and Ubuntu 18.04.

Prerequisites

To successfully reset the root password for your database you should:

Be logged into Ubuntu 18.04 or CentOS 7 as a user with root privileges.

Step 1 Identifying Your Database Server version

The first step when resetting your MySQL/MariaDB root password is confirming the version of your database server. This is because different server versions require different commands to reset the root password.

Execute the command below to identify the database server version:

$ mysql --version

The command will give you different outputs depending on the database server you have installed. If you are using MySQL database server, you will the output below:

mysql  Ver 14.14Distrib 5.7.22, forLinux (x86_64) using  EditLinewrapper

On the other hand, if you are using MariaDB database server you will get an output similar to the one below:

mysql  Ver 15.1Distrib 10.1.34-MariaDB, fordebian-linux-gnu (x86_64) usingreadline 5.2

Take note of the version of your MySQL/MariaDB database server you are using; you will use the information later in this tutorial.

Step 2 Resetting The Root Password

Once you identify the version of the database server you are using, the next step is resetting the root password. To achieve this, follow the steps below.

Stopping MySQL/MariaDB service

When resetting the root password for your database server, it highly recommended you stop the service first. Run the command below to stop your database server:

$ sudo systemctl stop mysql

Starting The Database Server Without Loading Grant Tables

Next, restart your MySQL/MariaDB database server and ensure the grant tables are not loaded. By enabling the –skip-grant-tables option, anyone can log in to MySQL or MariaDB server without the password and get all the privileges. Execute the command below to start your database server and enable the –skip-grant-tables option.

$ sudo mysqld_safe --skip-grant-tables &

The & ampersand that closes the command above allows the program to operate in the background while granting us access to the database server shell.

Logging Into The Database Server Shell

Now, we can easily connect to MySQL or MariaDB server as the root user, and we don’t need a password to achieve this. Run the command below to connect to your MySQL or MariaDB server:

$ mysql -u root

Creating A New Password

By now you should be logged into the database server shell. Besides, you are aware of the database server version you’re using. We are going to apply this knowledge to reset the root password.

If you are using MariaDB version 10.1.20 and later or MySQL version 5.7.6 and later, execute the command below in the database server shell.

ALTERUSER'root'@'localhost'IDENTIFIEDBY'MY_NEW_PASSWORD';
FLUSHPRIVILEGES;

If the above statement (ALTER USER) fails to deliver desired results, you can use the one below to modify your user table directly.

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHEREUser = 'root'AND Host = 'localhost';
FLUSHPRIVILEGES;

On the other hand, if you are using MariaDB version 10.1.20 and earlier or MySQL version 5.7.5 and earlier, execute the command

SETPASSWORDFOR'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSHPRIVILEGES;

In both cases, you should get an output like the one below if everything was successful.

QueryOK, 0 rowsaffected (0.00sec)

Stopping And Starting MariaDB/MySQL Server Normally

The root password for your database server is reset, now, stop and start the server normally. First, run the command below to stop MariaDB/MySQL database server

$ mysqladmin -u root -p shutdown

Next, run the command below to start your database server normally:

If you are using MySQL run the command:

$ sudo systemctl start mysql

On the other hand, if you are using MariaDB run the command

$ sudo systemctl start mariadb

Verifying The Password

Run the command below to login to your database server using the new password:

$ mysql -u root -p

You will be requested to enter the new password. Type the password and press ENTER to log in to your MySQL/MariaDB database server.

Conclusion

Congratulations! You have successfully reset your MySQL/MariaDB root password. You can always use this tutorial to reset the root password when you forget it. However, it’s advisable you pick a password that is easy to remember but strong enough to secure your database server.