Reset MySQL forgotten Root Password on Windows

Sometime specially on developer machine we forget MySql root password in that case it is not possible to reset root password normally. In that case you have to create new password as given below. There are two methods to do this.

mysql-1

Reset password on windows

  1. Login to system with administrator rights
  2. Stop MySql from service control(run services.msc). Find MySql service and stop it if not running as services or do not want to stop gracefully then kill mysqld.exe from Task manager.
  3. Create one text file with following SQL queries at any location like (C:init.txt)
    UPDATE mysql.user SET Password=PASSWORD(\'MyNewPass\') WHERE User=\'root\'; FLUSH PRIVILEGES; 
    
  4. Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for allroot accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
  5. Open command prompt(RUN –>cmd)
  6. Now locate mysqlbinmysqld.exe in your MySql installation location
  7. Now run following command from command window 
    C:>C:Program Filesmysqlbinmysqld --init-file=C:\\init.txt
    
  8. This will start MySql and reset all root user password which you have given init.txt file
  9. Now kill mysqld.exe from task manager and start mysql service normally.

Generic method for all systems

  • Stop mysql services or kill mysqld
  • start it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
  • Connect to the mysqld server with command
    shell> mysql 
    
  • Run following sql with new password
    mysql> UPDATE mysql.user SET Password=PASSWORD(\'MyNewPass\') -> WHERE User=\'root\'; mysql> FLUSH PRIVILEGES; 
    
  • Stop server and re-start normally.