CODEWITHSUNDEEP
mysql
OstrichGlue
OstrichGlue

Reputation: 345

How can I reset a root MySQL password on Windows?

I'm trying to backup an old MySQL database hosted on a Windows server, but need to reset the root password, as no one at the company remembers it. I've tried following these instructions, but have had no luck so far. I located the Service (MySQL56) stopped it, and opened a command prompt as an admin. MySQL 5.6 is being used.

I then used cd to navigate to C:\Program Files\MySQL\MySQL Server 5.6\bin, and run the following command:

mysqld --defaults-file="C:\\Program Data\\MySQL\\MySQL Server 5.6\\my.ini" --init-file="C:\\mysql-init.txt

The --defaults-file option is copied straight from the Properties of the Service, so I know it is looking at the right place. I've also verified that there is a my.ini file at that location.

The contents of the mysql-init.txt file is just a single line containing this command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('TheNewPassword');

When I run this, I get a warning that says: TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

mysqld then appears to run for a few seconds before stopping (I confirmed this by having task manager open. I see mysqld.exe pop up then disappear, and the command prompt returns in the window.

Trying to use this new password to log in does not work after starting the service back up. I'm at a loss and have tried several different methods to reset the password, none of which have worked. Is there something I am missing?

EDIT: I've noticed that if I don't include the --init-file option, it starts and continues to run without stopping...however, since it didn't run the command, the password was not reset in this case.

Upvotes: 0

Views: 5537

Answers (1)

A. Dzebo
A. Dzebo

Reputation: 646

Please follow theese stepst to reset your MySQL Password on Windows:

It just happens that you forget your MySQL root user password. And the answers you find online simply don't work. As always there is an complete and easy solution on Moder Software Trends site. Just follow theese steps and you will be good to go.

  1. Stop MySQl Service @ services.msc

  2. Create file change_mysql_pwd.txt with following content (replase YourNewPassword with desired new passwort you want to have for root user:

      ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';

  3. Save the file unter C:\ change_mysql_pwd.txt

  4. Run CMD.exe as Admin (Start->Cmd->Right Click->Run as Administrator)

  5. Type in cmd: cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

  6. Create “Data” Folder under "C:\Program Files\MySQL\MySQL Server 8.0\ (if already exists delete its contents!)

  7. Type in cmd: mysql --initialize

  8. Type in cmd: mysqld --init-file=C:\change_mysql_pwd.txt

  9. Login with root user account and the passwort set above.

  10. Delete C:\ change_mysql_pwd.txt file

Enyoj...

Upvotes: 0

Related Questions