It's pretty simple to reset the MySQL user account password using SET PASSWORD syntax when you know the existing password. If you assigned a root password previously and now you cannot recall it, you'll have to take some additional steps to assign a new root password.
Following the instructions will assist you in resetting the MySQL password for the Windows system without specifying the previous password.
Step 1: Log in to your Windows system with the Administrator user.
Step 2: If the MySQL server is running, you will have to stop it first. For the MySQL server running as a Windows service, go to Start → Administrative Tools → Services.
Find the MySQL service in the list and stop it. If the MySQL server isn't running as a Windows service, use the Windows Task Manager to stop it.
Step 3: Open a notepad and create a text file, and paste one of the following password assignment statements on a single line. In this statement, replace the New-Password with the password that you want to use.
Write the following statement in a text file for MySQL 5.7.6 and later
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New-Password';
Write the following statement in a text file for MySQL 5.7.5 and earlier
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('New-Password');
Step 4: Save this file and note down the file path. For this example, we have stored this file at C:\password-assign-statement.txt.
Step 5: Open a command prompt, from Start → Run → type cmd in the run menu and hit enter.
Step 6: At the command prompt, start the MySQL server with the special --init-file option. If you've installed MySQL server in a different location, be sure you adjust the MySQL installation path in the first command.
Similarly, replace your own password assignment statement file path (we created in step #3) in the second command.
# cd "C:\Program Files\MySQL\MySQL Server 5.6\bin"
# mysqld --init-file=C:\\password-assign-statement.txt.
As soon as you apply the above commands, the MySQL server will execute the statement stored in the file password-assign-statement.txt with the --init-file option at startup. It will change the password of the root user account to the password you mentioned in the file.
If you installed MySQL server using the MySQL Installation Wizard, specify a --defaults-file option as follows.
# mysqld
--defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.6\\my.ini"
--init-file=C:\\password-assign-statement.txt.#
Step 7: Start the MySQL service after updating the password. Go to Start → Administrative Tools → Services. Find the MySQL service in the list and start it.
Step 8: Once the MySQL server is started successfully, try to connect to the MySQL server with the root account using the new password. Now you should be able to connect to the MySQL server with the new password.
# mysql -u root -p
Step 9: Again, stop the MySQL server and restart it normally. If you are running the MySQL server as a Windows service, then start it from the Windows Services.
By following these steps, you can reset your MySQL root password on a Windows VPS without needing the old one. Always remember to store your new password securely for future use.
