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. Recovering from a forgotten root password is a common task for database administrators.

This guide will walk you through the process of resetting the root password in both earlier and later versions of MySQL and MariaDB. This guide provides a practical solution when you need to how to reset/modify your MariaDB or MySQL root password from SSH.

Requirements

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

• A server running MySQL or MariaDB.
• Sudo or root privileges to execute commands.
• SSH access to the server.

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. This guide has shown you how to reset/modify your MariaDB or MySQL root password from SSH.

Alternative Solutions to Resetting the Root Password

While the method outlined above is a reliable way to reset the MySQL or MariaDB root password, there are alternative approaches you can take. Here are two different methods, each with its own advantages and disadvantages.

Alternative 1: Using the init-file Option

This method involves creating a temporary SQL file containing the password reset command and then using the --init-file option to execute it when starting the MySQL/MariaDB server. This is another way of how to reset/modify your MariaDB or MySQL root password from SSH.

Explanation:

The --init-file option tells the MySQL server to execute the SQL statements contained in the specified file immediately after starting. This allows you to perform actions like resetting the root password without needing to manually connect to the server.

Steps:

1. Create the SQL file: Create a file (e.g., reset_password.sql) with the following content, replacing new_password with your desired password:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;

   or for older versions:

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

   Secure the SQL file by setting the permissions to readable only by root.

2. Stop the MySQL/MariaDB server:

   sudo systemctl stop mysql  # For MySQL
   sudo systemctl stop mariadb # For MariaDB

3. Start the server with the --init-file option:

   sudo mysqld_safe --init-file=/path/to/reset_password.sql --user=mysql &

   Replace /path/to/reset_password.sql with the actual path to your SQL file. The --user=mysql option is important to ensure the server runs with the correct privileges.

4. Connect to the server and verify the password change: After the server starts, connect using the new password.

   mysql -u root -p

5. Remove the SQL file: Delete the reset_password.sql file to prevent unauthorized password resets.

   sudo rm /path/to/reset_password.sql

6. Restart the MySQL/MariaDB server normally:

   sudo systemctl start mysql  # For MySQL
   sudo systemctl start mariadb # For MariaDB

Code Example (SQL file – reset_password.sql):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongNewPassword123!';
FLUSH PRIVILEGES;

Advantages:

• Automated process.
• Doesn’t require interactive access during the password reset.

Disadvantages:

• Requires creating and managing a temporary file.
• Potential security risk if the SQL file is not properly secured.

Alternative 2: Using the skip-password Option (Less Recommended)

Warning: This method is strongly discouraged in production environments as it disables all password authentication, posing a significant security risk. Only use this in controlled testing environments.

This method involves starting the MySQL/MariaDB server with the --skip-password option, which disables password authentication completely. You can then connect as root without a password and set a new password.

Explanation:

The --skip-password option bypasses the normal authentication process, allowing anyone to connect to the server without providing a password. This allows for quick access to reset the root password but leaves the server vulnerable to unauthorized access.

Steps:

1. Stop the MySQL/MariaDB server:

   sudo systemctl stop mysql  # For MySQL
   sudo systemctl stop mariadb # For MariaDB

2. Start the server with the --skip-password option:

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

   The --skip-grant-tables and --skip-networking options are also included to minimize the risk during this vulnerable state.

3. Connect to the server without a password:

   mysql -u root

4. Set the new password:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;

   or for older versions:

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

5. Restart the MySQL/MariaDB server normally: Remove the --skip-password option and restart the server.

   sudo systemctl start mysql  # For MySQL
   sudo systemctl start mariadb # For MariaDB

Advantages:

• Simple and quick.

Disadvantages:

• Major security risk: Disables all password authentication.
• Should only be used in controlled testing environments.

Important Security Considerations:

• Never use the --skip-password option in a production environment.
• Minimize the time the server is running with disabled authentication.
• Ensure the server is isolated from the network during this process.

These alternative methods provide different approaches to how to reset/modify your MariaDB or MySQL root password from SSH. Choose the method that best suits your environment and security requirements, always prioritizing security when dealing with sensitive database credentials.