Reset MySQL root password on Windows

By Paul on 2 Replies

If you have forgotten the root password for MySQL on Windows, it’s fairly easy to reset it back to a preferred password.

There are several ways of doing so. I tried the different options stated in the documentation, but ended up with the following warning

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use –explicit_default_timestamp server option
(see documentation for more details)

The solution to reset the root password for MySQL on Windows I came up with is as follows

1. Create “mysql-init.sql” on C:\
Depending on your MySQL version, paste one of the following sql statements in “mysql-init.sql” and save the file.

MySQL 5.7.6 and later

[sql]ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;[/sql]

MySQL 5.7.5 and earlier

[sql]SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’);[/sql]

2. Open up my.ini, typically located in C:\ProgramData\MySQL\MySQL Server x.x\

Locate the [mysqld] section and add the following line

[shell]init-file=C:\\mysql-init.sql[/shell]

3. Restart MySQL using the console

net stop mysql
net start mysql

4. Check that your password has changed by running

mysql -u root -p

If your password has changed successfully, delete the file C:\mysql-init.sql so you don’t expose your password. Furthermore, remove the “init-file” line in my.ini

Same method works on Unix/Linux systems, but if you are looking for a more OS specific guide, take a look at the documentation.

2 comments

  1. This worked for me. Thank you. In my case, I had to reboot the Server to get the MySQL Service restarted (set to Automatic startup) because it would not restart using the Command Prompt or any other way.

    With the root@ Password now working, I was able to use MySQL Workbench to re-type the existing Password for the WordPress database that was still not connecting properly. That step resolved the database connection. I did not need to change the DB_USER or DB_PASSWORD in the WordPress “config.ini” core file… I just re-typed the existing Password value for that user via MySQL Workbench and saved it.

    I initially tried using the method published in the MySQL documention and it did not work for me either, producing the same error message that was noted. Very thankful to Paul for this practical solution.

  2. Hi @michaelalanherzog:disqus,
    I had the same problem as you, restarting MySql. Using net stop/start mysql resulted in MySql not starting. I didn’t dig more into this, but just restarted the server as you.

    I’m glad that my solution worked for you, and you got your WordPress up and running again.
    Thank you for your feedback. :)

Leave a Reply