dotkam.com stats

MySQL: Reset Lost Root Password

Here is a quick Step-by-Step “how to” which helps restoring MySQL root password that was lost/forgotten.

It happens to everybody, especially if several distributed (different) systems are maintained, where the password is not the same. Here is what needs to be done to restore it:

Step 1: Stop MySQL daemon if it is currently running

Depending on the operating system MySQL is installed on, the daemon can be checked/stopped differently. Here is an example on how to do it in Unix-like systems. 

NOTE: You might need to run it as a Unix System superuser (root) - depending on how the system is configured,
          and what permissions your Unix account is granted)

Here is how to stop/kill the existing mysql daemon, in case it is running:

      ps -ef | grep mysql - checks if mysql/mysqld is one of the running processes.
 
      pkill mysqld             - kills the daemon, if it is running.

Note: if pkill (’process kill’) is not on a particular Unix system, use kill -9 ‘pid’, where ‘pid’ corresponds to processes that were found with ps -ef | grep mysql

Step 2: Run MySQL safe daemon with skipping grant tables

      mysqld_safe --skip-grant-tables &

Step 3: Login to MySQL as root with no password

      mysql -u root mysql

Step 4: Run UPDATE query to reset the root password

In MySQL command line prompt issue the following two commands:

      UPDATE user SET password=PASSWORD("ualue=42") WHERE user="root";
      FLUSH PRIVILEGES;

“ualue=42” is a common password for “The Hitchhiker’s Guide to the Galaxy” people which reads “Ultimate Answer to Life, the Universe, and Everything=42“

Step 5: Stop MySQL safe daemon

Follow the first two steps, but this time kill (pkill) “mysqld_safe” instead of “mysqld”

Step 6: Start MySQL daemon

Depending on the operating system (Unix-like examples):

      /etc/rc.d/rc.mysql start

OR

      /etc/init.d/mysql start

OR

      /etc/rc.5/mysql start

etc.. check existing MySQL configuration

Step 7: Root password is reset and ready to use

Password is reset. Privileges are flushed. Start MySQL and login as root with the password set in step 4:

      mysql -u root -p mysql

Note: sometimes (most of the time) ‘root user’ privileges are required for the system (OS) in order to stop/start processes

what is next? Reset Lost Password in Sun Application Server…

This entry was posted on Tuesday, April 10th, 2007 at 09:52 and is filed under howto, mysql, tutorials. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.

35 comments

  1. Drew
    September 15, 2007 at 03:03

    Dude, you rock. I’m a total n00b with database stuffs … and hunting out this blog (?) on dial-up was so worth it for such a straight forward solution. Thank you …

  2. vcc
    February 18, 2008 at 08:19

    Thanksfor the help but I still hate mysql

  3. trust
    August 11, 2008 at 14:02

    Nice script, I think I should try it. Thanks

  4. sleepless
    October 2, 2008 at 13:13

    I can’t thank you enough for your guide. I am truly grateful.

  5. newbie
    October 25, 2008 at 15:11

    Thanks! Works fine for me!!!

  7. jeffrey
    November 10, 2008 at 03:41

    I get the same error 1045 access denied for user ‘root’@'localhost’(using password: YES) but I am using Windows Vista and have forgotten my password

  8. Toly
    November 10, 2008 at 10:30

    @jeffrey,

    What step did you follow on Windows? I suspect that you did not stop/start the right mysql processes. Also did you find how to run “mysqld_safe”?

    – Toly

  9. cindy
    December 7, 2008 at 07:06

    Thanks a lot!

  10. bacon
    January 20, 2009 at 15:16

    Don’t use kill -9 with mysql! It shutdowns uncleanly. It’s very bad for a database! Use /etc/init.d/mysql stop

    http://speculation.org/garrick/kill-9.html

  11. Max
    January 24, 2009 at 10:53

    Sorry. I am a total zilch in mysql.

    Question: in step 7, with this command ( mysql -u root -p mysql) , are you logging in as root with a password of mysql. As set in step 4? I thought the password set in Step 4 was: ‘value=42′.

    Should I backup everything first before I do the above.

  12. Toly
    January 24, 2009 at 17:10

    @Max,

    It can be confusing if you read it literally, but what really happens is you are telling mysql to log you in with the root user “-u root”, who will provide the password “-p”, AND choose a db named “mysql” for this user.

    Check out “Step 3″, see there is “mysql -u root mysql”? That is exactly the same statement as in “Step 7″ except in “Step 3″ you are not saying “-p” —> “I am going to provide the password for this user”.

    – Toly

  13. auriux
    March 24, 2009 at 09:47

    None of those methods worked on 5.0.32-Debian_7etch5. I tried init file method, tried skipping grant tables and various combination of user – no luck to reset the password.

  14. Toly
    March 24, 2009 at 17:55

    @auriux,

    Make sure before you run

    mysqld_safe --skip-grant-tables &

    all myslq (mysqld, etc..) processes are killed (not running). You can check if that is the case by

    ps -ef | grep mysql

    Only when none of the mysql processes are running you can start mysqld_safe.

    Let me know if you have any difficulties, and if you do, at what step?

    Good Luck,
    – Toly

  15. colin
    April 22, 2009 at 14:28

    I’ve tried this (because it seems a root password is installed by default on my system). I can login ok thanks to the skip-grant-tables option, but i am not able to login again after I restart the system.

    This is on Dragonfly BSD.

  16. Toly
    April 23, 2009 at 02:52

    @colin,

    It might be that there is a startup script somewhere that resets a myslq server password in case it is empty (wild guess).

    Try to actually SET the password, instead of filling it blank. Let me know if it works for you,

    – Toly

  17. Ade
    July 17, 2009 at 08:02

    Great job! I was stuck without this hack!

  18. elliotash
    July 26, 2009 at 03:30

    I can login ok thanks to the skip-grant-tables option, but i am not able to login again after I restart the system. the database is hosted on windows

  19. elliotash
    July 26, 2009 at 03:33

    can anybody help with this problem it is really annoying now. i cannot remember the password. so I can login ok thanks to the skip-grant-tables option, but i am not able to login again after I restart the system.

  20. Pavan
    August 1, 2009 at 09:04

    Yes it is working for me …Thanks for your help

  21. Daniel
    August 6, 2009 at 00:32

    Excellent after 2 days of searching. Thanks Thanks

  22. lamapper
    September 3, 2009 at 05:19

    For ubuntu and debian users specifically who need to reset their MySQL root password, this sites steps worked flawlessly for me. Best of all, when I was finished, I only had to ‘restart’ the mysql server and I was able to login from another PC through a firewall for the first time ever to my server as the MySQL root without getting any error messages. Here is the link, decide for yourself! http://codesnippets.joyent.com/posts/show/2038

  23. case
    September 18, 2009 at 04:22

    Worked a treat for mysql root password rest on Ubuntu 9.04 jaunty.

    For Ubuntu. You will need to use the ‘kill -9 pid r’

    Also bear in mind that ‘ps -ef |grep mysql’ will show the grep process in the results. n00bs like me will waste some time killing this, then ps’ing to check it’s gone, and finding it there again :P

  24. Maik
    October 16, 2009 at 06:41

    It works. Thanks .

  25. Tarun Das
    November 30, 2009 at 12:52

    Everything else I found in internet was bogus, even official website of MySQL (http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html) did not help.
    IT’s SIMPLE and IT WORKS.
    Thank you very much

  26. sreejith
    December 1, 2009 at 02:22

    thanks …

  27. Shawn S
    December 8, 2009 at 03:54

    Thank you very much for this. I somehow jacked up my db a few months ago and couldn’t get into it. I know nothing about databases, so I was pretty frustrated with MySql and phpMyAdmin until I came across a comment on HowtoForge directing me here. This definitely gets bookmarked.

  28. margaret
    January 12, 2010 at 06:21

    This is very helpful,however am using Ubuntu and the query
    UPDATE user SET password=PASSWORD(“value=42″) WHERE user=”root”;
    did not work well….i hard to omit the word value and use it like:
    UPDATE user SET password=PASSWORD(“42″) WHERE user=”root”;

    Worked so smoothly! Thanks.

  29. Mike
    January 27, 2010 at 03:23

    Thanks, I assumed reinstalling would reset root password, but it didn’t.

    Thanks to your guide I was able to get going again!

  30. H4king
    February 6, 2010 at 18:08

    WHEN YOU POST …the admins on this web see your IP :) so they login on your mysql server whit password 42 …suckers

  31. Toly
    February 9, 2010 at 19:08

    Don’t worry @H4king, I have no intentions to look up people IPs + “42″ would not be the password people would use.

    However, thanks for the clever concern :)

  32. Markus
    February 18, 2010 at 17:35

    Thanks a lot. it works, yippie!

  33. sridhar
    March 6, 2010 at 15:10

    i’m using window OS, and i’m really stuked so,
    could you please provide steps for windows to solve the problem.

    thanks in advance…

tell me something...
  1. (required)
  2. (valid email - optional)
  4. Captcha
  5. (required)