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
  4. Flush the privileges
  5. Quit mysql
  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

  • bhaumik

    hi,

    when i’m skipping grant tables, it will me gives me the errors.

    D:xamppmysqlbin>mysqld -nt –skip-grant-tables
    110113 12:23:43 [Warning] Can’t create test file usrlocalmysqldataSERVER-PC
    .lower-test
    110113 12:23:43 [Warning] Can’t create test file usrlocalmysqldataSERVER-PC
    .lower-test
    mysqld: Can’t change dir to ‘usrlocal\mysqldata’ (Errcode: 2)
    110113 12:23:43 [ERROR] Aborting

    110113 12:23:43 [Note] mysqld: Shutdown complete

  • Nikhil

    thanks dude..helped a lot..finally got to use mysql šŸ˜€

  • emmanuel

    hello dear. i wanted to change my password instead of

    update user set password = password(‘…….’) where user = ‘root’ and host=’localhost”

    i did this

    update user set password = (‘…….’) where user = ‘root’ and host=’localhost”

    ever since then i cant connect with the new password and also i cant even use the skip grant table to change the forgotten password and also i cant use the init file method. what should?

  • Pingback: I installed MySQL (32bits) on my Imac (OSX 10.6.8), but what should I do next?()