How Can I Recover My Lost MySQL root Password?

|

Many people use the MySQL open source relational database server. Mostly for websites, but I’ve also seen it used elsewhere. One problem that I recently ran into with my local development installation is… “what was my root password again?” Aaaggghh!

OK, sorry. Don’t panic; it can be recovered. A quick survey of the internet and I found this very simple and very useful how-to Recover lost MySQL root password mini-HowTo. Is is very well written and easy to follow… but, it only covers the process from a Linux installation. The basic trick here in either platform is to restart your MySQL server in and tell it to skip the grants table. So here is my addition on the Windoze platform.

Recover Your Lost root Password In MySQL (Windows)

  1. Stop the MySQL server
    1. You can accomplish this by opening the Services control panel applet, finding the MySQL service listing and clicking the Stop icon.
    2. Alternately you can open up the command line and type > net stop MySQL
  2. Start the MySQL server and skip grants
    1. Go to the MySQL installation directory and then the \bin directory under that (in the console)
    2. Type > mysqld-nt --skip-grant-tables
    3. Now the MySQL server should be running wide open (ie. every user that logs in has full access)
  3. Change root password
    1. Now from another console open the mysql client
    2. Run the following SQL query replacing the dots (…) with your new password 
      update user set password = password('.......') where user = 'root' and host='localhost';
  4. Flush the privileges 
    flush privileges;
  5. Quit mysql 
    quit;
  6. Stop MySQL server
    I tried actually doing a CTRL-C in the console that was running mysqld… but that did not work. So I just opened up the handy-dandy task manager, found the mysqld-nt.exe listing and clicked on it and finally clicked the End Process button
  7. Restart MySQL server normally
    1. You can accomplish this by opening the Services control panel applet, finding the MySQL service listing and clicking the Start icon.
    2. Alternately you can open up the command line and type > net start MySQL

There you have it! All should be well in the world once again and you should now be able to login to MySQL with your new root password. If not… umm… reinstall? lol