Reset the Root User or Password in a MySQL WAMP Setup
Running MySQL in a Windows XAMPP test environment is pretty common. Having to restore the root user, or having to reset the root user password, however, is not. Here’s how to get your root user back up and running on your WAMP system in no time.
You’ll need to be logged in as an administrator.
Stop the Service, Kill the Process
The first thing that needs to be done is to stop the MySQL service. You can do this by either going to Windows Services, or just killing the process in Task Manager/Process Explorer. The easy way is to bring up Task Manager, find mysqld.exe, and kill it (delete key).
If that fails, go to Start -> Control Panel -> Administrative Tools -> Services, or type services.msc in the Run box. Right-click on the MySQL service, and select Stop. Poof!
Create a Text File
Open up notepad or your favorite text editor.
If you have a root user and you’re just trying to change the password, copy and paste this into the document:
UPDATE mysql.user SET Password=PASSWORD(‘TEST’) WHERE User=’root’;
FLUSH PRIVILEGES;
Otherwise, if you deleted your root user, try this:
INSERT INTO mysql.user (Host, User, Password, Select_priv, Update_priv, Create_priv, Insert_priv, Reload_priv, Grant_priv, Create_user_priv) VALUES (‘%’, ‘aroot’, PASSWORD(‘TEST’), ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);
FLUSH PRIVILEGES;
These must be on two separate lines! Also note that you can change the root password by modifying the TEST password in the examples.
Save your document as C:\mysql.txt. Or whatever you feel like naming it.
Start mysqld via Command Line
Open up the command prompt by typing in cmd in the Run box.
Next type in C:\xampp\mysql\bin\mysqld –defaults-file=”C:\\xampp\\mysql\\bin\\my.ini” –init-file=”C:\\mysql.txt” –console. You’ll need to change the directory to fit your XAMPP\mysql\bin directory (by default, it should be C:\xampp\mysql\bin). Also note the double backslashes.
You should get something like this: (note that my paths are slightly different)
Running mysqld.exe manually with the --init-file option should fix your troubles.
If you get any errors, check your paths and your syntax and try again.
Otherwise, you should be successful! Fire up HeidiSQL and connect to 127.0.0.1 as root/TEST. Not only should you get in, you should also be able to create new users with full privileges.
