In This Tutorial we will learn how we can reset MySQL root password from Windows Command prompt.
MySQL is one of the most used RDBMS ( Relational Database Management System). It is almost everywhere from blogging software to famous e-commerce platforms.
It is even recommended software to teach SQL across the Education Boards computer Science Syllabus.
One of the most common problem faced by the System Administrator of Windows operating system is – changed MySQL Password.
Here in this tutorial we will change the root password of MySQL database on windows operating system. The whole process was tried and tested on Windows 10 running MySQL 5.1 community version.
How to reset MySQL root passwords
Check whether MySQL server is running on your system or not. by Default MySQL server runs in the background, if it is running in background then you are required to stop it.
1.1 select This PC on your system, Right click on This Pc and select Manage
1.2 select services and applications from left hand side Pane
1.3 Now open all the registered services on your system and stop MySQL service from this list.
Step 2: Generate MySQL Root Password Reset Text File
Open your favorite Text Editor and type the following lines ( I am using Sublime Text ). They are actually MySQL command so try to be little bit careful here.
UPDATE MySQL.user SET Password =PASSWORD(‘newpassword’) WHERE User =’root’;
NOTE: Do not forget to place semicolon( 😉 after every line. As the commands will be processed by MySQL itself and we are very well aware that MySQL terminate each statement with semicolon(;).
Now save this file as ‘reset.txt’ or whatever name you want to give, But advised to save in such a position, So that you could easily recall its PATH.
Step 3: Reset MySQL Root Password Command
Now locate your MySQL bin folder inside your system and start command prompt at the same location in Administrator Mode.
But before we start typing our command to reset MySQL root password. Lets locate my.ini file responsible to force all MySQL setting.
In my case it is located inside ProgramDATA>MySQL>MySQL server 5.1,
NOTE : By default Programdata is a hidden folder, so you have to use View properties to see the hidden files and folder.
Now issue the following command on the command prompt.
MySQLd –default-files =”c:\\ ProgramDATA\\MySQL\\MySQL server 5.1\\my.ini” –init-file =g:\\reset.txt
If you are able to see message like the above on the screen, it means everything is working as per your planning and you are able to reset MySQL root password successfully.
Above command not only reset your MySQL root password but also start MySQL daemon (server), you are required to kill the current process started by the above command using Ctrl+Alt+Del combination.
Now start MySQL service from This Pc -> manage>Service and Applications->MySQL
Open your MySQL command prompt and login using new password.
Final thoughts on resetting MySQL Root Password
It was the very common problem with me as few of my students were changing the root password of my systems. We saved reset.txt file and now we are able to reset root password within a minute.
This process saved my lots of time and money. Hope this will also help you to reset your MySQL root password.