How to recover a lost password in MySQL 5.7.11? (for Windows)

Question

I am running MySQL 5.7.11 on Windows 10

I am unable to login to root through the command line client since I lost my password.

How do I change/reset the old password?

Please help, and thanks in advance.

Answer 1

If you installed MySQL as a service, you need the following:

STEP 01

Open Windows Command Line as Administrator and run

C:\> net stop mysql

STEP 02

Add this to C:\Program Files (x86)\MySQL\MySQL 5.7\my.ini under the [mysqld] group header

[mysqld]
skip-grant-tables

STEP 03

C:\> net start mysql
C:\> mysql

You should be logged into mysql

STEP 04

Suppose you want root@localhost's password to be hello, run this

UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit

STEP 05

C:\> net stop mysql

STEP 06

Remove the skip-grant-tables option from the my.ini

STEP 07

C:\> net start mysql

STEP 08

C:\> mysql -uroot -p

This should prompt you for the password. Enter new password and you should be in.

ALTERNATIVE

Instead of creating my.ini, do the following after you have shutdown MySQL

C:\> cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\> start mysqld.exe --skip-grant-tables
C:\> mysql

You should now be inside mysql without a password. Next run this

UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit    

Back at the command line, shutdown mysql like this:

C:\> mysqladmin shutdown

Now, you start MySQL57 Service back up