How to Reset the MySQL root Password

MySQL is a popular open source relational database management system perfect for web application software. It is popular for its easy to use interface and simple setup.  Like many web services, MySQL has a root-password. Substantially, the root password allows user to do all high-level functions in the database.

If you�ve never modify a root password before on your MySQL database, that means you should connect the database it withoutbe a password. As a result, it is not a secure database practice.

Whereas, If your MySQL database has a root password, but somehow you lost it, then you will get help from here to reset a MySQL root password on Linux and Windows.

Reset the MySQL User Root Password 

First & foremost thing you need to know before resetting the root password for MySQL is, you must have root access on the server to reset the MySQL root password.

How to reset MySQL User Root Password in Linux

Step 1: Log in as the MySQL User

When you start for the Linux installation, just ensure you�re logged in as the same username that normally runs MySQL. Although you can log in as root, once you initiate the MySQL server, make certain you initiate it with the 

--user=mysql option.

Otherwise, the system perhaps create files owned by the root user, which can cause problems.

Step 2: How to find the .pid File for the MySQL Service

Next we move to find the .pid file for the MySQL service.

Mainly system store .pid file in these paths -

/var/lib/mysql/, /var/run/mysqld/, or /usr/local/mysql/data/ 

Usually, the name of the file starts with mysqld or the hostname of your system and ends with the .pid extension.

Step 3: Kill the mysqld Process

Before creating a new root password, you need to stop the MySQL server. After that, To kill the mysqld process, open a command line, and run the following syntax:

kill `cat /mysql-data-directory/host_name.pid`

You can change mysql-data-directory/host_name.pid with the filename that you found in the former step. Make sure to specify the complete file path. Also, make sure to use the back-tick key (usually above the tab key) and not a single-quote mark at the beginning of the command.

Step 4: Create the Password File

1. Open your favorite text editor. In this example, we use vim:

sudo vim

2. Further, add the following line in the file:

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

Don�t forget to include the single-quote marks and the semicolon. Change New Password with the password you want to use. In the end, be sure to use a strong secure password.

This command will work on the currently using machines. In case,if you�re connecting to a different system, replace localhost with the appropriate hostname.

3. Save the file to home/me/mysql-init.

Step 5: Reboot the MySQL Server and Enter the New Password

To apply the modifications to the password, you need to restart the MySQL server by using the following command in the terminal:

mysqld --init-file=/home/me/mysql-init &

This starts MySQL, and make the text-file password change. Depending on how you initiate your server, you may need to add other options (such as --defaults-file before the init command.)

Step 6: Cleaning Up

At the end, log into your MySQL server using the root account, and verify the new password works. Then, remove the file you created in Step 4.

#upstart

$ sudo service mysql start

# SysV init

/etc/init.d/mysql-server stop

How to Reset MySQL Root Password in Windows

After understand the resetting of MySQL root password in Linux not its turn to get the knowledge about the resetting the same in Windows environment. 

To demoinstrate it clearly, just create a virtual machine (VM) as we do here. We are created here a VM and installed in it Windows 10 and MySQL Server by using the MySQL installation wizard. 

The MySQL configuration is as follows:

• MySQL Version: 8.0.23 (MySQL Community Server)

• MySQL Home directory: C:\MySQL_Home\

• Data directory location : C:\MySQL_Data_Directory\

• File initialization location: C:\MySQL_Home\my.ini

To see the above details, check out the MySQL workbench. Open the MySQL Workbench and connect it to the MySQL Server. Then, Go to the Navigator pane and click on the Server Status. Just like the following image:

However, to view the MySQL server details you can also use the mysqladmin command. 

The mysqladmin command is used to perform several administrative activities. For example creating and removing the database, configuration details, viewing the server status, and more. You can also refer a MySQL Server Administration Program to understand more about the mysqladmin command.

To populate the status of the MySQL server, run the following query.

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

Enter password: **************

Steps to change the root user password:

• Create an file of initialization by using ALTER USER command.

• Stop the services of MySQL.

• Initiate the services using mysqld and specify the file that has an ALTER USER command.

• Connect to MySQL workbench and change the root password.

• Reboot the service.

Now, check it out the resetting of the root password.

Step 1: Create a MySQL Initialization File.

First, we need to create a text file with a query that is used to change the password. To change the password, we will use the ALTER USER command. Like this - 

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

Then, Save the file in the appropriate location.

Step 2: Stop the MySQL Services.

To reset the password, first stop the MySQL services, its mandatory. 

To stop the MySQL Services, follow the following path:

Open the Control Panel and then move towards the Administrative tools and click it. Then, click on the Open Services, and then go to the Locate MySQL Service from the list. Then finally, Right-click MySQL and select Stop.

Alternatively, you can also run the below mention PowerShell command to stop the services of the MySQL.

PS C:\> net stop MySQL;

Step 3: Initiate the Services by using the Initialization File.

To start the MySQL Service, we are using the mysqld command. The mysqld is a single-threaded program. It has several configuration options that can be specified at the start-up of the MySQL Server. You can seen the options list by running the below command.

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

After that, use the � -init-file in the mysqld command. This � -init-file option reads the command from the file specified in the option. If you have installed the MySQL Server using MySQL installation wizard, you must specify the � -defaults-files. 

Here, we created a file named ResetRootPassword.txt in the D:\PasswordFile directory. Use the mysqld command as follows:

C:\>C:\MySQL_Home\bin\mysqld  --defaults-file = "C:\MySQL_Home\my.ini" 

--init-file="D:\PasswordFile\ResetRootPassword.txt"

Step 4: Get MySQL connect and change the Password.

Now, let us connect to MySQL by using MySQL workbench. Open MySQL workbench and connect by using root user.

Specify the new password and connect to MySQL.

Once the connection set up completes, run the following command to reset the password.

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

After resetting the password, close the MySQL workbench, and kill the mysqld service by clicking the Ctrl+C command.

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

Step 5: Restart the MySQL Service.

Now, reboot the MySQL service from services.msc. 

Open the Control Panel, then go to the Administrative tools and select it. After that go to the open Services and then locate the MySQL service. After that, Right-click the MySQL service and select Start.

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

As you can see, the MySQL Server connection has been completed. 

Was this answer helpful? 7 5