Resetting MySQL Root Password

The official MySQL docs are not very clear (and also leave several parts out) on how to reset the MySQL root password -
http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html#resetting-permissions-windows

It’s much simpler and easier than they make it out to be -

These instructions to reset the root password will work for any MySQL installation on Windows, including WAMP Server and XAMPP. But some of the file paths used here are specific to WampDeveloper (just update paths to your environment).

1. Reboot/restart your system.

2. Stop the MySQL Service.

You can do so via:
A) Command Line – Run cmd.exe as admin, then execute: net stop MySQL
B) Window Services Manager – Run services.msc, select MySQL Service, stop it.
C) WampDeveloper’s System Tab – Select MySQL Service, stop it.

3. Create file C:\mysql-init.txt with the following contents -

UPDATE mysql.user SET Password=PASSWORD('xyz123') WHERE User='root';
FLUSH PRIVILEGES;

This SQL will set the root password to: xyz123

4. Open the command-line as admin (via WampDeveloper’s System Tab), run this command -

mysqld --defaults-file=C:\\WampDeveloper\\Config\\Mysql\\my.ini --init-file=C:\\mysql-init.txt

*Note the drive letter of the WampDeveloper folder and the mysql-init.txt file path used, update to your environment.

This will attempt to start a MySQL process, using your my.ini file (which contains the needed paths to the Database folder and other settings), and on startup executing the SQL in the provided file (to change the root password).

5. You should now see a blinking cursor. The MySQL process is now running. After a few seconds, close the command-line window.

6. Terminate/exit the above running MySQL process by opening up another command-line window as admin and running this command -

mysqladmin -u root -p shutdown

When it asks you for the password, enter the above new root password: xyz123

7. Start the MySQL Service via WampDeveloper’s System Tab.

8. All root accounts (host: localhost, 127.0.0.1, ::1) can now be accessed by password: xyz123

Note – Opening the command-line or running cmd.exe “as admin”, means that the command-line instance has Administrative privileges granted to it. Simply double-clicking on cmd.exe will not usually open it in this mode. You have to right-click cmd.exe and select ‘Run as admin’. The command-line button in WampDeveloper’s System Tab always opens it in admin mode.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>