How to reset/modify your MariaDB or MySQL root password from SSH

Introduction

It happens to the best of us. If you forget or lose the root password to your MySQL or MariaDB database, you may still obtain access and change the password if you have access to the server and a sudo-enabled user account.

This guide will walk you through the process of resetting the root password in both earlier and later versions of MySQL and MariaDB.

Requirements

You will need the following to recover your root MySQL/MariaDB password:

• Sudo access to the Linux server running MySQL or MariaDB.

Step 1: Checking the Database Version

The majority of recent Linux distributions include MySQL or MariaDB, a popular drop-in alternative that is completely compatible with MySQL. To recover the root password, you’ll need to use different commands depending on the database and its version.

You may verify your version by running the following command:

$ mysql --version

MySQL output

mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

MariaDB output

mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make a note of the database and version you’re using, since you’ll need them later. Next, you must stop the database so that you may manually access it.

Step 2: Stop the Database Server

To update the root password, you must first shut down the database server.

You can accomplish the same with MySQL by using:

$ sudo systemctl stop mysql

And for MariaDB, we have:

$ sudo systemctl stop mariadb

After stopping the database server, you’ll need to access it manually to reset the root password.

Step 3: Restart the Database Server Without Checking Permissions

If you launch MySQL and MariaDB without loading user privilege information, you will be able to enter the database command line with root rights without giving a password. This will allow you to obtain unauthorized access to the database.

To do this, you must prevent the database from loading the grant tables, which include user privilege information. Because this poses a security concern, you should also forego networking to prevent other clients from joining.

Start the database without allowing networking or loading the grant tables:

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand at the end of this command causes this process to operate in the background, allowing you to continue using your terminal.

You may now connect to the database as the root user, which will not prompt you for a password.

$ mysql -u root

Instead, you’ll be greeted with a database shell prompt.

MySQL output

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

MariDB output

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

You can update the root password now that you have root access.

Step 4: Changing the Root Password

The ALTER USER command is a straightforward way to alter the root password in newer versions of MySQL. This command, however, will not operate right now since the grant tables have not been loaded.

By using the FLUSH PRIVILEGES command, we may instruct the database server to refresh the grant tables.

mysql> FLUSH PRIVILEGES;

The root password can now be changed.

Use the following command for MySQL 5.7.6 and newer, as well as MariaDB 10.1.20 and newer.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Make sure to update new password with your preferred new password.

Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

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

Remember to reload the grant tables after this.

You should get confirmation that the command was properly performed in either situation.

Output
Query OK, 0 rows affected (0.00 sec)

Because the password has been updated, you may now terminate the manual instance of the database server and restart it as previously.

Step 5: Normally Restart the Database Server

First, shutdown the database server instance that you manually established in Step 3. This command looks for the PID, or process ID, of the MySQL or MariaDB process and sends SIGTERM to instruct it to depart gracefully after cleaning up. More information may be found in this Linux process management lesson.

Use the following for MySQL:

$ sudo kill 'cat /var/run/mysqld/mysqld.pid'

Use the following for MariaDB:

$ sudo kill 'cat /var/run/mariadb/mariadb.pid'

Then, using systemctl, restart the service.

Use the following for MySQL:

$ sudo systemctl start mysql

Use the following for MariaDB:

$ sudo systemctl start mariadb

You can now verify that the new password was properly applied by running:

$ mysql -u root -p

The program should now prompt you for the password you just created. When you enter it, you should be able to access the database prompt as intended.

Conclusion

You now have restored administrative access to the MySQL or MariaDB server. Make sure your new root password is strong and secure, and save it somewhere safe.