How to Reset MySQL Root Password?

MySQL is software that is both free and open source and is used to create and manage database operations.

MySQL comes with a root password that grants users the ability to perform all advanced database management capabilities. If in the first place, the root password has not been set up on your MySQL database, you should be able to connect to it. Please note, it is not advisable to leave the root password unset as it can be a security vulnerability, making it possible for anyone to gain access to your database.

In some cases, it may become necessary to reset the root password for your MySQL database. The following article will explore the process for resetting the MySQL Root Password.

How to Change MySQL User Root Password in Linux?

Step - 1: Sign in Using the MySQL User Credentials

To begin the process of resetting the MySQL root password, make sure you are signed in using the identical user who normally runs the MySQL database. It is recommended to avoid logging in as root to prevent any file permission issues. If you must sign in as the root user, ensure that you initiate the MySQL server by utilizing the --user=mysql option. to avoid the creation of files possessed by the root user, which could lead to potential problems.

Step - 2: Locate the .pid File for the MySQL Service

In the second step, you need to locate the .pid file associated with MySQL. Typically, the filename of the .pid file begins with mysqld or your system's hostname and concludes with the .pid extension

Step - 3: Stop the Process of MySQL by Using MySQLId

Step 3 involves stopping the MySQL server and terminating the running mysqld process before proceeding with the creation of a new root password. This can be done using the following command:

Remember to update mysql-data-directory/name_of_host.pid with the filename discovered in the second step and to specify the complete path to the file.

Step - 4: Generate the Password File

The following step involves creating a new password file. You can use any text editor to execute the following command:

Here, replace the new_password with the password of your choice and save the file changes at the location home/me/mysql-init.

Step - 5: To Apply the New Password, You Need to Restart the MySQL Server

To apply the new password, you need to restart the MySQL server using the following command:

This command will initiate MySQL and apply the password change from the text file. When restarting the MySQL server to apply the new password, keep in mind that you might have to include other options, like specifying the --defaults-file option before executing the init command. The specific options you require may depend on how you normally start your server.

Step - 6: Performing Cleanup of File

After completing the previous steps, the final step is to log in to the MySQL server with the account of the root user to ensure that the updated password is working correctly. If there are no issues, you may safely remove the file that was generated in Step 4.

How to Reset MySQL Root Password in Windows?

Step - 1: Halt MySQL Server

To start resetting the root password for MySQL in Windows, the initial step is to halt the MySQL server. You can achieve this by opening the "Run" box using the Win+R key combination, typing in "services.msc", and press Enter. Once you see the list of services, find the MySQL service and right-click on it. From there, choose the "Stop" option to shut down the MySQL server.

Step - 2: Open Up a Text Editor of your Choice

The subsequent step involves launching a text editor, which can be found by searching for Notepad on a Windows operating system.

Step - 3: Generate a New Text File that Contains the Password Alteration Command

The third step involves creating a text file with the new password. To do this, we need to type in the following password alteration command in the text file:

Here, replace new_password with your desired password and save the file to the home/me/mysql-init location. Don't forget to save the file before exiting the text editor.

Step - 4: Launch a Command Prompt

The following step involves opening the command prompt in Windows by pressing Ctrl+Shift+Esc. Then, navigate to the file option, select run the new task, and enter cmd.exe. After that, check the option to run it as an administrator and enter OK.

Step - 5: Initiate the MySQL Server Again Using the Updated Configuration File

Using the command prompt, go to the directory of MySQL by typing:

Then, type the following command to start the MySQL server with the updated configuration file located at this path:

Step - 6: Clean Up

After verifying that you can log into your MySQL server as root using the new password, you can proceed to delete the C:\mysql-init.txt file that was created in the previous steps.