How can i recover mysql password?

When we install MySQL on Windows, we usually specify the password of the root user. This helps keep the MySQL Server secured. Suppose you joined a new organization, and the management of that organization plan to migrate some MySQL databases to SQL Server. To perform this, you need the credential of the root user, but the organization did not keep track of the password. In this scenario, we must reset the password of the root user.

In this article, we are going to learn how we can reset the root password in MySQL Server on Windows 10. I have prepared a demo environment on my workstation for demonstration, and the details are explained in the next section.

Setup Windows Environment

We are going to learn the methods to reset root passwords in a Windows environment. For the demonstration, I created a virtual machine and installed Windows 10 and MySQL Server on it. The installation of the MySQL server was performed by using the MySQL installation wizard. The configuration of MySQL is as follows:

1. MySQL Version: 8.0.23 (MySQL Community Server)
2. MySQL Home directory: C:\MySQL_Home\
3. Location of data directory: C:\MySQL_Data_Directory\
4. Location of initialization file: C:\MySQL_Home\my.ini

You can view the above details from MySQL workbench. Open MySQL Workbench and connect to MySQL Server ? Click Server Status from the Navigator pane. See the following image:

Also, you can use the mysqladmin command to view the details of the MySQL Server. The mysqladmin command is used to perform various administrative activities, such as creating and dropping the database, viewing the server status, configuration details, and more. You can read 4.5.2 mysqladmin — A MySQL Server Administration Program to learn more about the mysqladmin command.

Run the following query to populate the status of the MySQL server.

C:\MySQL_Home\bin>mysqladmin -u root -p version
Enter password: **************

Steps to reset the password of the root user:

1. Create an initialization file that has ALTER USER command.
2. Stop the MySQL Services.
3. Start the services using mysqld and specify the file that has an ALTER USER command.
4. Connect to MySQL workbench and reset the root password.
5. Restart the service.

Now, let us see how we can reset the root password.

Step 1: Create a MySQL Initialization File.

First, let us create a text file with a query that is used to change the password. To change the password, we will use the following ALTER USER command.

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

Save the file in the appropriate location.

Step 2: Stop the MySQL Services.

To change the password, we must stop the MySQL services. To stop the MySQL Services, Open Control Panel ? Open Administrative tools ? Open Services ? Locate MySQL Service from the list ? Right-click MySQL and select Stop.

Alternatively, you can run the following PowerShell command to stop the MySQL service.

PS C:\> net stop MySQL;

Step 3: Start the Services Using the Initialization File.

We are using the mysqld command to start the MySQL Service. The mysqld is a single-threaded program. It has various configuration options that can be specified at the start-up of the MySQL Server. You can view the list of the options by running the following command.

C:\>C:\MySQL_Home\bin\mysqld --verbose --help

We are going to use the – -init-file option in the mysqld command. The – -init-file option reads the command from the file specified in the option. If you have installed the MySQL Server using MySQL installation wizard, as I have, you must specify the – -defaults-files. In the – – defaults-file option and the location of the my.ini configuration file. I have created a file named ResetRootPassword.txt in the D:\PasswordFile directory. The mysqld command with an option is as follows:

C:\>C:\MySQL_Home\bin\mysqld  --defaults-file = "C:\MySQL_Home\my.ini" 
--init-file="D:\PasswordFile\ResetRootPassword.txt"

Step 4: Connect to MySQL and Reset the Password.

Now, let us connect to MySQL using MySQL workbench. Open MySQL workbench ? Connect using root user.

Specify the new password and connect to MySQL.

Once connected, run the following command to reset the password.

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

Once the password is reset, close the MySQL workbench, kill the mysqld service by clicking the Ctrl+C command.

Once services are stopped, delete the ResetRootPassword.txt file.

Step 5: Restart the MySQL Service.

Now, restart the MySQL service from services.msc. Open Control Panel ? Open Administrative tools ? Open Services ? Locate the MySQL service ? Right-click the MySQL service and select Start.

Now, connect to the MySQL server using the root user as explained in the previous section.

As you can see in the following screenshot, we are connected to the MySQL Server.

Summary

As database administrators, it is our responsibility to keep track of important passwords, especially superuser passwords. If the password is lost and unrecoverable, it can be very stressful. In this article, I covered the step-by-step process of resetting the password of the root user. This article might be helpful to recover the root password of MySQL on Windows.

Tags: mysql function, reset password Last modified: September 16, 2021

How do I find my MySQL username and password?

Where is MySQL password stored?