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.

7 thoughts on “Resetting MySQL Root Password”

  1. worked as expected, I just had to move to the path where mysqld was stored before executing the commands, thanks!

  2. Thank you, but this didn’t work for me. Wamp 3.0.6 x64, changed default mysql root password from nothing to ‘toor’ using included Adminer, now am unable to log in to mysql anywhere. Tried this method multiple times, even trying to set to blank password again. No error on “resetting” the password, but it never seems to get set. What a nightmare. Perhaps time to move to XAMPP.

    1. Your issue is likely that the root account’s Host: field is set to something other than the IP you are accessing it on.

      There should be 3 root accounts, each for Host:
      localhost
      127.0.0.1
      ::1

      The last is the IPv6 of 127.0.0.1.

      You can update the above SQL statement to set the Host: field back to 127.0.0.1 or localhost.

  3. This helped me solve the problem with just little twist.
    1) Stopped MySQL
    2) Prepared mysql-init.txt file with the contents suggested and saved it in C drive.
    3) Opened Command-line (cmd) and changed directory to where they MySQL bin directory is located. In my case, it was:
    C:\>CD C:\wamp\bin\mysql\mysql5.6.17\bin
    4) Copied and pasted “mysqld –defaults-file=C:\\WampDeveloper\\Config\\Mysql\\my.ini –init-file=C:\\mysql-init.txt” into CMD updating “C:\\WampDeveloper\\Config\\Mysql\\my.ini” with “C:\wamp\bin\mysql\mysql5.6.17″ [where the my.ini file is located] in my case.
    5) Opened anther CMD and changed path to “C:\wamp\bin\mysql\mysql5.6.17\bin” and pasted “mysqladmin -u root -p shutdown”. Then pressed Enter key. Entered the latest password.
    6) Started MySQL service
    7) Tested it by opening MySQL Console in WAMP-> MySQL and entered the new password. Then it worked successfully.

    Thank you for the help again.

  4. Copy of command line error I get when trying to shutdown

    C:\wamp64\bin\mysql\mysql5.7.14\bin>mysqladmin -u root -p shutdown
    Enter password: ********
    mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Can’t connect to MySQL server on ‘localhost’ (10061)’
    Check that mysqld is running on localhost and that the port is 3306.
    You can check this by doing ‘telnet localhost 3306′

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=""> <s> <strike> <strong>