Software & AppsOperating SystemLinux

How To Reset the Root Password of MySQL on Ubuntu?

Ubuntu 9

In this guide, we will walk you through the process of resetting the root password of MySQL on Ubuntu. This can be particularly useful if you have forgotten your MySQL root password and need to regain access.

Prerequisites

Before we begin, ensure that you have:

  • Ubuntu 18.04 or 20.04 installed on your system.
  • MySQL installed on your Ubuntu system.
  • Access to a user account with sudo or root privileges.

Overview

Resetting the root password of MySQL involves stopping the MySQL service, starting it in safe mode, and then running commands to update the password. We will cover two solutions that you can use to reset your MySQL root password.

Solution 1: Resetting MySQL Root Password

Step 1: Stop the MySQL Service

First, we need to stop the MySQL service. Open your terminal and type the following command:

sudo service mysql stop

This command stops the MySQL service on your system.

Step 2: Start MySQL in Safe Mode

Next, we will start MySQL in safe mode, skipping the grant tables and networking:

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

The --skip-grant-tables option allows us to log in without a password and with all privileges, and --skip-networking ensures MySQL does not listen for network connections.

Step 3: Log in to MySQL as Root User

Open a new terminal window and log in to MySQL as the root user:

mysql -u root

Step 4: Update the Root Password

In the MySQL console, execute the following commands to update the root password:

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

Replace 'new_password' with your desired password. The UPDATE statement changes the password for the root user, and FLUSH PRIVILEGES reloads the grant tables to apply the changes.

Step 5: Exit MySQL and Restart the Service

Exit the MySQL console with the exit command, then stop the MySQL process you started in safe mode:

sudo killall mysqld_safe

Finally, restart the MySQL service:

sudo service mysql start

Now, you should be able to log in to MySQL with the new root password:

mysql -u root -p

Solution 2: Alternative Method for Resetting MySQL Root Password

If the first solution does not work, you can try an alternative method. The steps are similar, but there is a slight difference in the command used to update the root password.

Step 1 to Step 3

Follow the same steps as in Solution 1.

Step 4: Update the Root Password

In the MySQL console, execute the following commands to update the root password:

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

This command is slightly different from the one used in Solution 1, as it directly updates the Password field in the mysql.user table.

Step 5 to Step 7

Follow the same steps as in Solution 1.

Conclusion

This guide has shown you how to reset the root password of MySQL on Ubuntu. Remember to replace 'new_password' with your desired password in the commands provided.

If you encounter any issues, ensure that you have correctly followed all the steps and check the MySQL documentation for further assistance.

What should I do if I forget my MySQL root password?

If you forget your MySQL root password, you can reset it by following the steps outlined in this guide. Solution 1 involves stopping the MySQL service, starting it in safe mode, and updating the password. Solution 2 is an alternative method that you can try if the first solution does not work.

Can I reset the root password of MySQL without stopping the MySQL service?

No, in order to reset the root password of MySQL, you will need to stop the MySQL service temporarily. This is necessary to ensure that the changes to the password are applied correctly.

Can I use these methods to reset passwords for other MySQL users?

These methods are specifically for resetting the root password of MySQL. If you need to reset the password for a different user, you can modify the UPDATE statement in Step 4 of either solution to target the specific user you want to update.

What if I encounter issues while resetting the root password?

If you encounter any issues while resetting the root password, make sure that you have followed all the steps correctly. Double-check the commands you entered and ensure that you have the necessary permissions to make changes to the MySQL configuration. If you need further assistance, refer to the MySQL documentation or seek help from the MySQL community.

Leave a Comment

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