2

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.

  • 1
    Is MySQL 5.7 installed as a service ??? – RolandoMySQLDBA Jul 6 '16 at 18:01
  • I am not sure, since I am learning. But I remember I installed MySQL along with all the components (WorkBench 6.3, Notifier, etc.) on the PC. – Shubham Deshmukh Jul 6 '16 at 18:09
2

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

  • It does not allow me to 'skip-grant-tables' under my.ini. When I try to save the file after the changes, it alerts me that 'access is denied'. What should I do? – Shubham Deshmukh Jul 6 '16 at 18:14
  • Did you shutdown mysql first ? Did you open the CLI as Administrator ? – RolandoMySQLDBA Jul 6 '16 at 18:15
  • Yes, I have stopped the MySQL58 service from the services list. How do I open CLI as administrator? – Shubham Deshmukh Jul 6 '16 at 18:20
  • Do you have the Windows Command Line on the Desktop ? If you do, right click on it and click Open as Administrator. – RolandoMySQLDBA Jul 6 '16 at 18:22
  • You must then open C:\Program Files (x86)\MySQL\MySQL 5.7\my.ini in Notepad and add skip-grant-tables – RolandoMySQLDBA Jul 6 '16 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.