MySQL Root Password Reset in 3 Simple Steps

MySQL Root Password Reset in 3 Simple Steps

Have you forgotten your MySQL server’s root password? No worries. Here’s how to quickly and easily reset it.

If you have forgotten the password for your MySQL database, fear not! Resetting the root password is a simple process that can be accomplished in three easy steps. So, whether you are a beginner or an experienced MySQL user, I’ll show you how to regain control of your database server.

This guide shows two different techniques for resetting the MySQL root user password. Whichever of the two you choose will give you the desired result.

Furthermore, the instruction applies entirely to the MariaDB database, a popular MySQL alternative, and, in some cases, provides additional features not available in MySQL. You can read our in-depth guide on the subject here.

Reset a MySQL root Password: The Fastest Way

To complete the steps below, you must have a local user account on the server where MySQL runs that has sudo permissions to execute privileged commands.

Step 1: Stop and Start MySQL Server without a Password

Stop the MySQL service.

sudo systemctl stop mysqld

Then, as shown below, run a temporary MySQL server instance.

sudo -u mysql mysqld --skip-grant-tables &

Let me explain. When the server is started with the --skip-grant-tables option, it does not read the grant tables or apply any access control. In other words, users can access it without a password.

You can now log in to the MySQL server as the root user, with no password required.

mysql -u root
Start MySQL Server without a password

Step 2: Reset a MySQL root Password

Reload the current privileges first.

FLUSH PRIVILEGES;

Then, reset MySQL’s root password using the following query. First, of course, replace “MyNewPassword” with the required one.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';

Finally, reload the privileges one more time and exit.

FLUSH PRIVILEGES;
quit
Reset the MySQL root password
Reset the MySQL root password

If you want to learn more about adding users to MySQL and providing them with privileges, here’s our guide.

Step 3: Restart and Connect to the MySQL Server

Now that we’ve changed the password, you need to stop the temporarily running MySQL server by finding its process number and terminating it. You can easily accomplish this with the following command:

ps auxw | grep '\-\-skip-grant-tables' | tail -n 1 | awk '{ print $2 }' | sudo xargs kill

Then restart the MySQL server and log in with the new password.

sudo systemctl start mysqld
mysql -u root -p
Restart and connect to the MySQL server
Restart and connect to the MySQL server

Congratulations! The MySQL server is now back in your control.

Reset the MySQL’s Root Password by an Initialization File

To complete the steps below, you must have a local user account on the server where MySQL runs that has sudo permissions to execute privileged commands.

Step 1: Switch to the mysql System User and Stop the MySQL Server

To complete the steps below successfully, you must switch to the system user account that runs your MySQL server. This is typically the system account with the username mysql. To switch to it, run the following:

sudo -u mysql /bin/bash

You can use the whoami command to ensure that you work with the correct user.

Switch to the mysql system user
Switch to the mysql system user

Then, stop the MySQL server passing the kill command the full path to the PID file holding the process number with which it started. If you’re not sure where this file is, you can quickly locate it by running the following:

mysqld --print-defaults | tr " " "\n" | grep pid | tail -n 1
Find MySQL server PID file
Find MySQL server PID file

Finally, stop the MySQL server.

kill `cat /run/mysqld/mysqld.pid`

Step 2: Create a MySQL Password Reset File

Using your preferred text editor in the /tmp directory, create a text file named “mysql-init.txt,” with a single line containing the password-assignment query.

vim /tmp/mysql-init.txt

Insert the following line, being sure to change the password to the one you want to use.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';

Save the file and exit. 

Step 3: Start MySQL Server

Start the MySQL server as shown below, with the --init-file option set to the full path to the file you just created.

mysqld --init-file=/tmp/mysql-init.txt &

That’s all. The MySQL root user’s password has been changed, and you can now log in as root with the password you specified in the file above.

mysql -u root -p
Starting and logging as root in the MySQL server
Starting and logging as root in the MySQL server

You can now safely delete the “/tmp/mysql-init.txt” file. You don’t need it anymore.

Conclusion

As you can see, resetting the MySQL root password is a relatively simple process that can be accomplished in three steps. However, it is essential to note that resetting the root password should only be done as a last resort, as it can potentially expose your database to security risks.

Therefore, it is always a good idea to carefully manage your password and keep it secure to avoid resetting it in the future. You can find additional information in the official documentation.

If you have any questions or feedback, feel free to comment.

Bobby Borisov
Bobby Borisov

Bobby is an Editor-in-Chief at Linuxiac. He is a Linux professional with over 20 years of experience. With a strong focus on Linux and open-source software, Bobby has worked as a Linux System Administrator, Software Developer, and DevOps Engineer for small and large multinational companies.

Leave a Reply

Your email address will not be published. Required fields are marked *