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 Pro (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 (permissions elevated: right click it, select ‘Run as admin’), within it execute: “net stop MySQL” OR “net stop MySQL5
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 a permissions elevated command-line window (WampDeveloper’s System Tab, button: Command Line), and inside it, copy-paste or type this command (and press Enter) -

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 another command-line window (permissions elevated), and inside it, 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 a permissions elevated command-line or running cmd.exe “as admin”, means that the command-line instance has Administrative privileges granted to it. Running or 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.

One thought on “Resetting MySQL Root Password

Leave a Reply

Your email address will not be published.

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>