Recover MySQL root Password

by on April 18, 2006 · 276 comments· LAST UPDATED August 4, 2010

in , ,

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 276 comments… read them below or add one }

1 Anonymous April 29, 2006 at 1:16 pm

THANKS!
SAVED MY ASS!

Reply

2 Arnab March 8, 2011 at 11:56 am

Thanks , it really works

Reply

3 travb October 22, 2011 at 11:44 pm

You can recover MySQL database server password with following five EASY steps.

Why is it that EVERYTIME someone writes something is easy, it is not?

I have no idea how to shut off mysql. And there is no explanation here.

Reply

4 NNII December 28, 2011 at 8:32 pm

yes, it does in fact have how – /etc/init.d/mysqld stop

Reply

5 Joe April 11, 2013 at 4:39 pm

I had the same problem ‘mysql stop’ required a password :(
In a terminal on the command line as system root:

root@localhost# ps -A
PID      TTY  TIME          CMD
....
23386  ?       00:00:00   mysqld_safe
23543  ?       00:00:00   mysqld
....
root@localhost# kill -9 23543
root@localhost# kill -9 23386
root@localhost# ps -A

Look through the list of processes and confirm the processes were killed.
Continue with step #2

Reply

6 Márcio January 23, 2014 at 1:09 am

I am at lubuntu 13.04 and i Can’t use mysqld commands
i.e when i use # /etc/init.d/mysql stop

This is shown on terminal:

“Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql”

when I use # mysqld_safe –skip-grant-tables &
this is shown:

140122 23:07:14 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
140122 23:07:14 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
140122 23:07:14 mysqld_safe A mysqld process already exists

(I think this post are a bit old)

Reply

7 Márcio January 23, 2014 at 1:20 am

Worked to me after using ps -A to check what process is running Mysql then Killing it with kill command, after I folowed the steps and worked well. Tanks!
This post Is OLD BUT GOLD!

Reply

8 Anonymous May 4, 2006 at 10:11 am

Thanks a lot!
I’ve been googling for 2 hours and your solution was the only one that worked!

Reply

9 Anonymous May 17, 2012 at 2:46 am

Serioiusly?

Reply

10 random_guy April 10, 2013 at 6:30 pm

oh my god…. you forced me to comment because your asking “seriously?” to a comment made 6 years earliers LMAOOOOO

Reply

11 TimeTraveller December 26, 2013 at 6:22 pm

And you replied 1 year later

Reply

12 ASAP April 7, 2014 at 6:41 pm

Here I’m replying another year later.

Reply

13 Charles May 19, 2006 at 8:34 am

thank you! i forgot my password and was afraid I had to reinstall mysql!. you saved me. props to you and msn search!

Reply

14 Anonymous May 20, 2006 at 2:33 am

On most *nix systems as root (or with sudo) you can also do:

cat /root/.mysql_history|more

Which is very informative.

Reply

15 John August 17, 2011 at 3:05 am

-Much easier! thanks!

Reply

16 nixCraft May 20, 2006 at 2:58 am

Nice :)

Reply

17 Anonymous October 20, 2006 at 4:15 pm

Hi,

When I try stop mysql on my server, I receive a error 1045. Access denied for user: ‘root@localhost’ (Using password: NO)

Thanks

Reply

18 Jm December 4, 2006 at 12:54 am
19 Timothy matara January 12, 2007 at 6:28 am

This is great help, thanks for this posting!

Reply

20 asia March 8, 2007 at 7:11 am

i want free download of Recover MySQL root password

Reply

21 Andrew May 10, 2007 at 2:16 pm

Brilliant guide – lifesaver!

Reply

22 DngloZ May 12, 2007 at 4:00 pm

thanks for this great post

Reply

23 Linda Botes July 5, 2007 at 7:51 am

Thanks! I’ve been looking at numerous other solutions and none of them worked. This one worked, no hassles.

Reply

24 Valentin July 23, 2007 at 10:34 pm

Great tutorial.. it just worked fine

Reply

25 Gerard July 31, 2007 at 12:10 pm

Coolness!!! thanks for this post! already bookmarked!!!hehehe..

Reply

26 Gerard July 31, 2007 at 12:15 pm

Coolness!!! This site is really the best! I also got a ftpbackup script from here! the best how to’s, I wish you the best and I hope that you could add more!!!

Reply

27 amin August 17, 2007 at 8:15 am

I got the following error

ERROR! MySQL PID file could not be found!

do have any solution

Reply

28 Meef August 30, 2007 at 5:44 pm

Um… I’ve been trying to actually change the password for about 3 days now, and I tried this tutorial… No errors, but when I start the MySQL client and type in my password it just beeps and goes away :( Is there any other way?

Reply

29 nixCraft August 30, 2007 at 6:10 pm

Open X terminal and at shell prompt use command:
mysql -u root -p

You should able to see an error message.

Reply

30 murdock September 5, 2007 at 3:05 am

Thanks for the help. This was just what I was looking for. I have no idea how I blew away my password, but at least this helped me get back in.

Thanks!
Murdock

Reply

31 alex September 27, 2007 at 3:03 pm

THANK YOU!!!!!!!!!!!! THANK YOU!!!!!!!!!!!

Reply

32 Sam October 3, 2007 at 10:26 am

Dude, you rock my world. shotalot

Reply

33 SPS October 14, 2007 at 3:09 pm

Yeah.. it is workin very well.. it saved me from formatting my Server… college 1……. :)..s o it works………..

Reply

34 Sanford November 30, 2007 at 1:49 am

Many thanks, this saved me a lot of time.

Reply

35 Dave Brixius November 30, 2007 at 2:51 am

Greetings. Thank you so much. We are just getting our server up and running and working through some mysql issues. You saved me a lot of time and headaches. Dave

Reply

36 Amir January 29, 2008 at 8:16 am

Thank you very much

Reply

37 Charles February 5, 2008 at 6:47 am

I love you all. This is the only solution that worked not even de.mysql’s solution worked…

Reply

38 john February 6, 2008 at 5:30 pm

great, works exactly as shown. give the man a cigar!

Reply

39 grateful February 12, 2008 at 11:21 am

Vivek,

does this affect the dbases / the rest of the users in any harmful way?

Reply

40 Tony February 12, 2008 at 12:02 pm

another relieved joomla user here as Im now back in charge of mysql thanks a lot!

Reply

41 Janus March 9, 2008 at 9:46 am

Thank you.
God bless.

Reply

42 richard scrivener March 12, 2008 at 4:38 am

You saved my ass!!! I can’t thank you enough!

Reply

43 Nilesh March 20, 2008 at 12:03 pm

gr8 one! thanks.

Reply

44 Victor April 22, 2008 at 1:10 pm

I am using a windows op, how do I recover the password under Windows

Reply

45 Mark May 9, 2008 at 3:44 pm

I think I read this same article the last time my root acct got locked out. Thanks for hosting this page.

Reply

46 gleb May 11, 2008 at 1:10 pm

I’ve done everything exactly as it says , but still canot connect with my mysql server. I allways have this error: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
Even when i do NOT use a password , it just not working! i tryed to reintall mysql through synaptic , but still the same shit… No difference. Can enyone give an advise?

Reply

47 nisha May 12, 2008 at 6:45 am

Hi,

Can I remove an existing password following you guide, except that i leave this portion:

mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;

as this :

mysql> update user set password=PASSWORD(“”) where User=’root’;

Would this work? I need to remove the password for a bit, for testing purposes…and then set the password again….any help/advise is much appreciated…

-nisha-

Reply

48 nisha May 13, 2008 at 7:59 am

Hi again,

The problem I mentioned above, well, I tested it,and it works….thank you for the perfect guide ;-)
This is the i-can’t-remember-number-of-times that one of your articles has helped me… so, thanks for that too!

Reply

49 Marty Wolf May 16, 2008 at 6:07 pm

I tried all others, which failed!!!! This one works!!!! Thank you for making the instructions so simple to follow for us NON-LINUX Gurus. Much Thanks!

Reply

50 Jens May 17, 2008 at 10:50 am

Thanks alot!! Also saved me some hours :)

Reply

51 Emmi May 20, 2008 at 11:58 pm

Gleb, you need to use the unix shell as root to kill the mysql server process directly, then you can follow the mysql start without privileges command and get back in that way.

If you don’t know how to get a process list and send it a signal, you should read up just a little bit on that.

man ps
man kill

But something like:
ps aux | grep mysql
kill -TERM [processnumber# from command above]

(yes there is a one step command, but I personally ashew wildcards in my kill commands)

Reply

52 dario June 10, 2008 at 6:31 pm

thank you very very much!

Reply

53 Feodor June 12, 2008 at 10:21 am

thanks for this clear tutorial

Reply

54 sixhome June 15, 2008 at 2:09 pm

Thanks! Many thanks!

Reply

55 FAISAL SAEED June 24, 2008 at 9:53 am

Good work. Thanks a alot!

Faisal

Reply

56 uberdog June 25, 2008 at 5:22 pm

BIG TNX!!!

Reply

57 Ochieng, Edmund June 27, 2008 at 1:31 pm

After staying idle most of the day since i messed up the database, this is the best thing that happened on this day

Reply

58 Ko July 3, 2008 at 9:08 am

THANKS!!!

Useful and to the point!

Ko.

Reply

59 Wesley Chapel Homes for Rent July 11, 2008 at 5:36 pm

The gift that keeps on giving, 2 years and counting, this helped me out big time… Thanks!

Reply

60 Paolo July 21, 2008 at 11:52 am

Fantastic! thanks a lot!

Reply

61 Robin86 July 23, 2008 at 4:13 pm

great! good job :)
this is really worth printable…

Reply

62 Fatiha July 31, 2008 at 12:37 pm

Thank you, it works now.

Reply

63 jim August 4, 2008 at 4:48 pm

thanks this worked perfectly!!

Reply

64 kailash August 4, 2008 at 10:51 pm

Thanks a ton. I was stuck since a very long time. Could not find anywhere but here it was very clear.
Thanks very much!

Reply

65 NetWebLogic August 15, 2008 at 11:10 am

Bookmarked for future reference! Thanks for the very straightforward explanation, can’t think of a better way to do it!

Reply

66 Steffen August 16, 2008 at 10:07 am

Thank you very much! Saved some hours of reinstalling mysql.

Reply

67 Mohammad August 25, 2008 at 3:10 pm

Thank you. I’ve been trying using Toad and Password recovery tools but to no avail. This got it done!

Reply

68 Matt September 2, 2008 at 11:19 pm

Thank you very much. Windows users, you can do all of those commands from the command prompt, make sure you run it in admin mode, and change directory to where the mysql files are located, so when you run those commands it works. :)

Reply

69 me September 24, 2008 at 6:25 am

Thank you , this is very helpful, as some one said it was the one excellent solution worked for me.

my day is better

Reply

70 akzidenz September 26, 2008 at 7:02 pm

Thanks a lot!
Its much more elegant than the solution in the official mysql reference.

Reply

71 Richard J. Thomma November 24, 2008 at 12:53 pm

It works — the very only one by far — Kubuntu 8.10
Description is crisp concise and foolproof

Thank You very much

I wish I knew howto tell google

Reply

72 Mike Runs November 24, 2008 at 8:44 pm

Excellent!

Buy that person a beer! Or other meaningful beverage of choice.

Thanks!!

Reply

73 Didrik Nordström December 18, 2008 at 6:05 pm

Thank you! Saved my ass too.

Reply

74 Tim December 21, 2008 at 5:13 am

/etc/init.d/mysql stop
* Stopping MySQL database server mysqld [fail]

You ware going to need to fill in the gaps. If it fails then…….

Reply

75 More January 5, 2009 at 5:32 pm

amazing… thanks a lot!!

Reply

76 ThouArtMyGod January 8, 2009 at 6:51 pm

Thanks ever so much

Reply

77 Stephen Robinson February 17, 2009 at 4:23 pm

thanx, wasted 2 hours googling and this method works,
Stephen :)

Reply

78 Skyline February 19, 2009 at 2:11 pm

skip-grant-tables…. NICE!

Reply

79 Jhon F. Ortiz O. February 20, 2009 at 4:33 pm

Hi!
Wonderful guide. Congratulations!
Bye!

Reply

80 megan February 24, 2009 at 5:06 am

So Simple, yet so valuable. THANKS!

Reply

81 Bais March 10, 2009 at 9:05 am

Very good, on ubuntu 9.04 mysqld 5.1 come installed with a mysterous password, I reseted it with this procedure and It works fine, thank you.

Reply

82 Levon March 11, 2009 at 10:55 pm

Thank you very much, I’ve waisted more than 2 hours trying to recover MySql password.
Thanks again :)

Reply

83 ofir March 20, 2009 at 2:59 pm

You saved me a lot of frustration, Thanks

Reply

84 dawg April 11, 2009 at 8:10 am

omg, dude, you’re a genious….

Reply

85 Timmy April 21, 2009 at 4:12 pm

Thank You..

Reply

86 carolija.eu April 24, 2009 at 1:37 am

Nice, thanks !

Reply

87 Michael Watson April 27, 2009 at 5:21 am

This worked really well. Thank you :)

Reply

88 newbie May 12, 2009 at 2:05 pm

Thanks!

It works gr8!

Reply

89 Darrell May 12, 2009 at 2:27 pm

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU.

Most sincerely,
Darrell Goodman
Toronto, Canada

Reply

90 the_guv May 17, 2009 at 8:50 am

you are a ruddy hero.

thank you.

Reply

91 Rafael May 20, 2009 at 10:18 pm

Tutoriais como esse deveriam ficar na wikipedia dos tutoriais!

Abraço,

Reply

92 Stuart May 26, 2009 at 2:42 am

This didn’t work for me. I now get the following when trying to log in: (I’m logged in as root):

ctru2:~ # mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

What does this mean, and how do I get around it?

Reply

93 Jorge Calás May 26, 2009 at 2:39 pm

Thanks!!!! It was really easy and worked like charm.

Server version: 5.0.51a-24 (Debian)

Reply

94 ChrisW June 3, 2009 at 10:51 am

if you are using windows, you will still have the problem if you are using phpMyAdmin, open the config.inc.php, should be in your windows directory, change the password in there.

Reply

95 zabi July 2, 2009 at 6:50 pm

hi i want to know that how to remove or recover password which is on file when we open it in mysql it is asking for password how to remove them plz any one tell

Reply

96 Mikheil Kalandadze July 17, 2009 at 3:52 pm

THANK YOU.

Help is easy and usable ;) GREAT

Reply

97 dman3d July 23, 2009 at 1:04 am

Thanks a lot pal,
your method rocks, save me a lot of time.

Reply

98 Senthil Kumar July 30, 2009 at 6:00 pm

I have tried your solution 5 times. But not working :((.
[root@u15345903 /]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@u15345903 /]# mysqld_safe –skip-grant-table &
[1] 2278
[root@u15345903 /]# Starting mysqld daemon with databases from /var/lib/mysql
mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.83 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=’blocialdb123′ where user =’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye
[root@u15345903 /]# /etc/init.d/mysqld stop
STOPPING server from pid file /var/run/mysqld/mysqld.pid
090730 13:58:21 mysqld ended

Stopping MySQL: [ OK ]
[1]+ Done mysqld_safe –skip-grant-table
[root@u15345903 /]# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
[root@u15345903 /]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@u15345903 /]#

Reply

99 Martin August 6, 2009 at 11:45 am

i want to recover my password in windows.what should i do

Reply

100 frick August 12, 2009 at 2:17 am

The new MYSQL has different table name for user. See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html for details.

Reply

101 vorbis August 13, 2009 at 4:20 pm

Thanks! Worked perfectly!

Reply

102 Jhon August 16, 2009 at 9:48 pm

i search for a couple hours Thank you

Reply

103 LowKey August 20, 2009 at 6:16 am

Nice! Very helpful , Thanks!

Reply

104 atrin August 30, 2009 at 8:34 am

hi , at first i want to thank you for your post its very useful for me
actually i do all of above but when i want to access mysql with root (#mysql -u root -p)
it said that ” ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) ”

could you please help me about this

Reply

105 newbie September 6, 2009 at 5:59 am

Awesome, I have been playing using MySQL on and off for almost 2years to understand it from a higher level and still getting use to it! This is the best straight forward instructions I’ve seen….and trust me I’ve looked at heaps :)

So from a newbie, THANKS for taking the time!
John B.

Reply

106 gajula vinod kumar September 10, 2009 at 10:23 am

Great job
Without this i had reinstall and configure my bugzilla server
Thanks a lot

Reply

107 Michael Irwin September 13, 2009 at 8:21 pm

Definitely the most valuable tip of the year so far! Well written, clearly written.

Thank you very much!

Mike I.

Reply

108 Carl September 17, 2009 at 3:17 am

Thanks. Very clear and well written, extremely helpful.

Reply

109 Ashish kumar September 17, 2009 at 6:21 am

thanx
this is gr8 job
so that i recover my mysql password

Reply

110 Amy September 18, 2009 at 7:41 pm

How do i log in as root user if I do not know the password? Also, when I use the # /etc/init.d/mysql stop, it tells me that ‘#’ is not recognized as an internal or external command. I am not very knowledgeable about these things. I am on a windows system and I don’t know the root password. Please help. Thanks.

Reply

111 eric lee September 28, 2009 at 10:42 pm

Cool man !! you’re cool!

Reply

112 Alfonso September 30, 2009 at 9:56 pm

Hey… Thanks

this works perfect..

Funciona perfectamente…

Reply

113 Shekhar Charodiya October 2, 2009 at 7:33 am

Thanks Buddy U make my day

Thanks again

can u guide me how i make user in mysql and give them right to use

If u have solution Guide me

Reply

114 nixCraft October 2, 2009 at 9:30 am
115 Alejandro October 15, 2009 at 3:10 am

Thaaaaaanks!!! finally could set my password with your help, was trying for days.. !!!

Reply

116 Tuvshuu October 26, 2009 at 2:36 pm

Hi guys. This help looks soo helpfull but i dont know how to use it. i really need to reset my password. but i dont know how to Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option. I went to mysql\bin\mysqld but i cant type or do anything! can someone help me how to do those steps?

Reply

117 niraj tiwari October 28, 2009 at 2:40 am

i created my joomla with it set up and creeat user root and no password now my user and password is not working

Reply

118 niraj tiwari October 28, 2009 at 2:42 am

i miss my user and password

Reply

119 thank November 13, 2009 at 7:07 pm

helped me with my problem too.

googled around but your solution was the leanest

Reply

120 Samir Dhok November 15, 2009 at 2:37 pm

This works perfect.
Thanks

Reply

121 prajw2 November 15, 2009 at 3:17 pm

it works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!thankz a lot

Reply

122 irelock November 19, 2009 at 4:43 pm

MY a$$ was saved. Id almost thank you in person. Thanks a million and 1.

Reply

123 Girish November 24, 2009 at 5:16 am

yess…… its working………Thanks a lot………

Reply

124 Paul Mwiu November 26, 2009 at 10:07 am

Thanks for the above information. It has really helped alot.
Cheers!!

Reply

125 vibhav December 1, 2009 at 12:11 pm

awsem…..brilliant….
thanx a lot…..

Reply

126 Vipul December 9, 2009 at 12:01 pm

Thanks to help me but I have not root user in user table of mysql database.
Can you please tell me why such kind of things happened?

Reply

127 Olle December 10, 2009 at 1:12 pm

Yep, thanks!

Reply

128 danidiaz January 5, 2010 at 4:33 pm

Excellent tutorial!!

Thank you very much!

Reply

129 Jayaprakash January 9, 2010 at 1:10 am

This was absolutely what they say, hitting the nail on the head. I was getting the error message and was hitting my hed on the wall when this gentleman came along and it worked like smooth butter.
Thanx man , you rock

Reply

130 Roque January 22, 2010 at 7:10 pm

Thank’s a lot ..!
Very simple way

Reply

131 Heliogabal February 2, 2010 at 12:28 am

thank You so much – this was just what I needed.

Reply

132 neilh February 15, 2010 at 10:02 pm

Am still trying to find a way to SET the root password. WAMP mysql 3.1.16. Win Vista 32 – currently no password required.

Nothing on the ‘MySQL Change root Password’ thread works.

Reply

133 neilh February 15, 2010 at 10:04 pm

That should of course have been MySql 5.1.36

Reply

134 MK February 18, 2010 at 1:25 pm

Thanks a lot!

saved my a#$

Reply

135 Bala Subramanian March 3, 2010 at 5:18 am

Thanks a lot man! Your post saved me rite on time!

Reply

136 neil March 10, 2010 at 6:04 am

the most explicitly perfect tutorial yet. Saved My Ass Too

Reply

137 gary March 17, 2010 at 10:01 pm

Awesome tip, tks, it was concise while providing enough information to be effective.
Tks.

Reply

138 pedro March 19, 2010 at 6:23 pm

Thanks dude. Saved me some problems :)

Btw, like was said previously, the commands will work in Windows as well.

Reply

139 Omar March 22, 2010 at 6:09 am

Great, thanks.

Reply

140 Thomas R Adams March 23, 2010 at 5:28 pm

I hope this works

Reply

141 ubuntupunk March 31, 2010 at 10:30 am

It just hangs

100331 12:28:26 mysqld_safe Logging to syslog.
100331 12:28:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Reply

142 ashenafis April 13, 2010 at 8:02 am

10 Q, it worked perfectly.

Reply

143 Nosferatu April 22, 2010 at 3:30 am

Thnks if there were a puntuaction u’ll get a 10

Reply

144 EB April 29, 2010 at 1:19 am

Thanks, I have spent three days trying to get MySQL functioning. It seemed that the password was lost, but after running the password still works.

Reply

145 Caliventura May 1, 2010 at 7:38 pm

I tried many times… Do not work for me…
I Can’t change the password and I can’t remember the previous one.
Maybe I doing some mistake with the line:

update user set password=PASSWORD(“MyPassword”) where User=’root’;
when I run: mysql -u root -p

I put MyPassword…. And I get this error:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Does anybody knows what I doing wrong?

Reply

146 Ângelo May 12, 2010 at 9:03 pm

Hi
In the first command the prompt returns a messagem saying : ERROR 1045 (2800) Access denied for user root@localhost (Using password NO)

Even after i have looged in as root in the linux system (sudo su root)

Any tip ?

Reply

147 Anonymous May 16, 2010 at 8:44 pm

Excellent tutorial!
This worked like a charm.

congrats.

Reply

148 Garima May 19, 2010 at 5:06 am

Nice. Works very well!

Reply

149 Dave May 19, 2010 at 5:53 pm

Thanks it worked perfectly!

Reply

150 Aitor May 21, 2010 at 4:50 pm

Thank you! It also worked for me!

Reply

151 valen May 25, 2010 at 2:13 pm

thanks for your HELP!!!!

Reply

152 Humberto May 31, 2010 at 5:49 pm

Great article!!! Thanks!!!

Reply

153 Atlanta Web Design June 10, 2010 at 5:09 pm

Thank you for this information. Will the same steps work on a Windows box from the cmd prompt?

Reply

154 Gabriel Calusaru June 11, 2010 at 11:20 am

It doesn’t work. Password doesn’t change.

Reply

155 Engel June 21, 2010 at 1:14 pm

Thanks a lot!!!! saved me from fires of hell!!! lol

Reply

156 tarun June 23, 2010 at 10:43 am

thankz a lot! save my pc box

Reply

157 Shraddha Kulkarni June 25, 2010 at 6:37 am

Hello Vivek,

Thanks a Lot!!!
Excellent article. Saved my many applications.
Thanks once again!

Reply

158 Vincent June 27, 2010 at 11:40 am

Thank you, thank you :)
Sweet and nicely written tut.

Reply

159 InigoKintana July 16, 2010 at 7:18 am

Many Thanks.

The procedure works great!!!

Reply

160 Valenz July 24, 2010 at 12:06 am

GREAT GREAT GREAT!!!

Reply

161 febndy July 27, 2010 at 2:37 am

where should I put the codes? can anyone teach me from the beginning? I use Win 7 64 bit.

Reply

162 nikkie August 3, 2010 at 11:10 am

how can i recover my mysql password from cmd shell(windows environment)…

Reply

163 nikkie August 3, 2010 at 11:16 am

actually i used wrong command….
update mysql.user set password=”root” where user=”root” instead of
update mysql.user set password=password(“root”) where user=”root”
plz temme how to recover my password…thnx in advance

Reply

164 nikkie August 3, 2010 at 12:04 pm

i resolved it…..:D

Reply

165 Sriram August 11, 2010 at 5:16 pm

Thanks so much!

Reply

166 Theo September 5, 2010 at 11:58 pm

!!!! FULL — BUT QUICK — REINSTALL !!!!

To the ones still suffering from this persistent — and annoying — issue, the best solution I’ve come across so far is to remove & re-install mysql (using yum, apt-get, dpkg, or whatever you like the most).

Now, the tricky step that most of the online tutorials forget to mention is that after removing mysql and — of course — BEFORE reinstalling it, you MUST remove the original db’s directory, usually under /var/lib/mysql/

The latter is specially true for CentOS users.

Here´s a wrap-up of the whole process:

1) Make sure mysql is not running:
[root@mybox ~] # /etc/init.d/mysqld stop

2) Remove mysql:
[root@mybox ~] # yum remove mysql-server
[root@mybox ~] # yum remove mysql

2) Remove the offending db:
[root@mybox ~] # rm -rf /var/lib/mysql

3) Re-install mysql:
[root@mybox ~] # yum install mysql-server
[root@mybox ~] # yum install mysql
[root@mybox ~] # yum install mysql-devel

4) Re-install php’s access to mysql:
[root@mybox ~] # yum install php-mysql

5) Start your newly installed db engine:
[root@mybox ~] # /etc/init.d/mysqld start

Now you can log in without using a password.

6) Set a new password for root:
[root@mybox ~] # mysql
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
mysql> EXIT

I hope this solution saves you some headaches. It took me several hours to find out what was causing the problem.

Cheers to all.
Theo

Reply

167 Sami September 6, 2010 at 4:00 am

root@server:~# mysql stop
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Reply

168 العاب September 17, 2010 at 9:21 pm

thank you
anthoer way to do this
perl /scripts/mysqlpasswd root newpass

thank you

Reply

169 ChooseN October 6, 2010 at 10:41 am

THX A LOT

Reply

170 arora October 7, 2010 at 10:21 am

thank you so much brother for posting this….great great help.

Reply

171 nicko October 20, 2010 at 2:21 pm

eternal gratitude for this one! :)

Reply

172 Prabhu October 22, 2010 at 7:47 am

YOU THE MAN

Reply

173 _Jan November 13, 2010 at 6:06 pm

Thanks a lot! That did it!

Reply

174 Vipin January 6, 2011 at 1:57 pm

Thanks. :-)

Reply

175 Robert January 27, 2011 at 5:44 pm

Let me echo what everyone else is saying…. THANK YOU!!!

Reply

176 Jeff January 28, 2011 at 5:58 pm

Thank you! Thank you! Thank you!

Reply

177 pdwalker February 10, 2011 at 3:29 am

Fantastic!

Thanks!

Reply

178 markos February 14, 2011 at 1:11 pm

Brilliant!!! Thanks a lot mate

Reply

179 Ahamed Mustafa February 19, 2011 at 12:09 pm

It worked like charm !! Excellent !! Thanks a lot…

Reply

180 Khalil March 15, 2011 at 3:00 am

Thanks a lot! Was very helpful to me too.
All the Best!

Reply

181 man910 March 16, 2011 at 2:48 pm

Thanks!

Reply

182 david familian March 21, 2011 at 8:49 pm

hi
10.6 OS 64bit
i couldn’t get
bash-3.2# /etc/init.d/mysql start
bash: /etc/init.d/mysql: No such file or directory

on a mac the path is /usr/local/mysql/support-files/mysql.server start

so i used this /usr/local/mysql/support-files/mysql.server start –skip-grant-tables ( got this from here)

and followed your instructions

any idea why mysqld doesn’t work to start or stop server

i get this
bash-3.2# mysqld stop
110321 13:33:12 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.5.10-osx10.6-x86_64/data/ is case insensitive
110321 13:33:12 [ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

110321 13:33:12 [ERROR] Aborting

110321 13:33:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Reply

183 Mats March 22, 2011 at 2:48 am

I don’t have the MySQL root password as I had help setting it up, and the he never gave me the root password I now I can’t find the idiot (for Months).

I tried the tutorial above, but I can’t get in. The error I get is “ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)”

Where am I supposed to insert the new username and password?

Thanks!
Mats

Reply

184 Remigio Hernández June 22, 2011 at 1:13 am

Thanks a lot VIVEk , it really works.

Reply

185 Remigio Hernández June 22, 2011 at 1:19 am

Mats en the step 4:

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Remigio

Reply

186 Thierry March 23, 2011 at 3:07 pm

you save my ass :D , thankssss

From Argentina

Thierry

Reply

187 Debu March 24, 2011 at 5:25 pm

Thanks. It really worked.

Reply

188 blarehead March 28, 2011 at 10:52 am

thanks!!!. it works great!

Reply

189 daminda April 7, 2011 at 5:04 pm

thank you very much.. very clear

Reply

190 rhyeal April 8, 2011 at 5:44 pm

Works like a charm. Thanks a ton!

Reply

191 Vanja April 10, 2011 at 9:38 pm

Awesome stuff! Saved me a re-installation! Thanks dude!

Reply

192 Shiv May 3, 2011 at 3:53 am

thanx Vivek… Dude you rock!!!.. Please prove some more link for mysql..

Reply

193 Me myself and Irene May 3, 2011 at 12:30 pm

Thanks a million :)

Reply

194 Faiyaz May 3, 2011 at 12:56 pm

Thankyou so much.. works cheer…………………….

Reply

195 Ricardo May 10, 2011 at 8:31 pm

Thanks Vivek!
u just saved my day!! :D

thumbs up!

Reply

196 Charlie May 14, 2011 at 2:02 am

mysql.user table may also need to be updated to new password format.
Run mysql_upgrade (or, earlier, mysql_fix_privilege_tables) to do this.

Reply

197 praveen May 16, 2011 at 5:02 am

Hi,

This really worked for newbie like me. Thank you.

Praveen

Reply

198 boe var June 12, 2011 at 8:43 am

Thanks a million! That step 2 is a life saver!

Reply

199 JC July 7, 2011 at 12:20 pm

Thanks!
You save me!!!

Reply

200 San July 27, 2011 at 5:49 pm

Really helpful information, saved me a lot of time.

Reply

201 fti July 30, 2011 at 10:31 pm

thanks a lot, clean information

Reply

202 Erez August 19, 2011 at 3:54 pm

Thanks A bunch! Simple to follow and woks!

Reply

203 Cuong August 28, 2011 at 2:37 pm

Thanks! very useful.

Reply

204 ardent September 6, 2011 at 7:15 am

Thanks guys, this greatly works.

Reply

205 Lnux7802 October 7, 2011 at 5:06 am

yep,this site having good kb

Reply

206 Robbo October 24, 2011 at 8:50 am

It really works! Thanks a lot!

Reply

207 anonymouse October 27, 2011 at 8:47 am

it was really helpful..thanks

Reply

208 Ian November 6, 2011 at 1:22 pm

Using – service mysqld stop/start worked for me.

Reply

209 Peter Fisher November 19, 2011 at 1:47 pm

Awesome Awesome Awesome. Very helpful indeed

Reply

210 Keimpe de Jong November 22, 2011 at 7:15 am

I encountered the stunning situation where there was no root user

replace

update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;

with

insert into user(Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) values ('localhost', 'root', PASSWORD("my_password"), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',   'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',    'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');

Reply

211 alan April 14, 2012 at 3:56 am

Same here — no root user, 0 rows affected on the update… couldn’t figure it out. your insert worked fine for me, then ran the update to set the password, then flushed privs, then restarted mysql…. done and working now… thanks!

Reply

212 Vivek Kumar November 22, 2011 at 9:57 am

Thanks! This worked well!

Reply

213 Ege December 6, 2011 at 12:37 am

Couldn’t be neater, thank you!

Reply

214 Ahmed Salem December 6, 2011 at 9:02 pm

thanks a lot . it’s really descriptive :)

Reply

215 Francis December 15, 2011 at 6:11 pm

Thanks for this.. Worked like a charm on CentOS 5.

Reply

216 Jaume December 21, 2011 at 5:21 pm

Gràcies. feia temps que ho necessitava.

Reply

217 MiniVZ December 26, 2011 at 12:58 am

Thank you, I have reset my MySQL Password. Works on CentOS 6, though its mysqld instead of mysql for the start/stop of mysql.

Reply

218 RK February 1, 2012 at 10:15 am

Very easy and Straight forward

Reply

219 Hiroshi February 7, 2012 at 8:38 am

Andrew,Swap out ‘**PASSWORD** for what ever psrawosd you want to use.**IP_ADDRESS** for your local machine IP ( EG 10.0.0.xx or 192.168.1.xx … )

Reply

220 Sam February 7, 2012 at 11:43 am

I think there is a mis-communication in your tiourtal…I copied and pasted these two lines:./bin/mysqladmin -u root password ‘**PASSWORD**’./bin/mysqladmin -u root -h **IP_ADDRESS** password ‘**PASSWORD**’and I got this error:Unknown MySQL server host ‘**IP_ADDRESS**’ (1)’Check that mysqld is running on **IP_ADDRESS** and that the port is 3306.You can check this by doing ‘telnet **IP_ADDRESS** 3306′What should I do?

Reply

221 Yuleisa February 8, 2012 at 12:52 am

– Quote – My daaabtse is about 1GB, so I set my innodb_buffer_pool_size to 2GB– End Quote –Is that your daaabtse memory size or your daaabtse file size? Thanks

Reply

222 supriya February 15, 2012 at 5:51 am

Thank you so much for the detailed steps. This really helped me to recover my database password…

Reply

223 pouya February 25, 2012 at 12:20 pm

really easy really helpful step by step guide. saved my ass. thank u thank u so muchhhhhhhhhh

Reply

224 firdi March 8, 2012 at 8:09 pm

thank you…thank you…thank you…thank you…thank you…

Reply

225 Tia Daryatmo March 11, 2012 at 9:42 am

wow! when there’s no one around, this tutorial helps me a lot!!! thank you.. and big hugs from jakarta to you!!!!!

@tdaryatmo

Reply

226 Eike Düvel March 19, 2012 at 11:16 pm

Why is this tutorial not on the first place in the google results? This guy saved me!

Reply

227 Phani March 21, 2012 at 9:52 pm

I used this more than once so far!!. Thanks for helping out.

Reply

228 koton April 9, 2012 at 12:09 pm

thank you.

Reply

229 killsometimes April 11, 2012 at 12:34 pm

Thanks for your useful article. I have done it and it works perfectly.

Reply

230 Helen Neely April 23, 2012 at 12:03 pm

Thanks for this tip. I was struggling with my installation and this helped. I didn’t think it was possible to reset the default password.

Thanks again.

Reply

231 Manisha May 24, 2012 at 4:38 am

When I tried this for the first time, I got the following error saying,
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
Then resolved that issue following the steps in http://ubuntuforums.org/showthread.php?t=804021
Again for the second time, re-tried the steps in this blog. And it PERFECTLY WORKED.

Thanks alot.

Reply

232 Sites em Porto Alegre June 12, 2012 at 8:06 pm

Thanks so much for this man!
I was banging my head against the wall

Reply

233 Sites em Porto Alegre June 12, 2012 at 8:07 pm

Thank you so much for this!
I was getting crazy already!

Reply

234 sukhbir June 20, 2012 at 7:08 am

Can anyone tell me where i have to write the above command ?

Reply

235 John July 9, 2012 at 1:33 pm

Thanks man, you saved my life :)

Reply

236 Edison roberto July 12, 2012 at 4:54 pm

Thanks! This worked well! 2

Reply

237 Manisha July 25, 2012 at 12:16 pm

Vivek, This works very well. Vivek rocks!!!

Reply

238 tohid August 3, 2012 at 7:07 pm

Thank you so much for the detailed steps. This really helped me to recover my database password.

Reply

239 keshaV August 16, 2012 at 12:33 pm

at last i did it… thanks a loads vivek :):)

Reply

240 Maxi August 28, 2012 at 11:45 am

Thank you very much!!!!
you helped me a lot!!!!

Reply

241 Tushar August 28, 2012 at 7:46 pm

setuser@setuser-desktop:~$ mysqld_safe –skip-grant-tables
nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[7459]: started
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[7469]: ended

mysqld started in safe mode but suddenly it ended as above. Any idea ??

Reply

242 sakar September 5, 2012 at 6:43 am

Thanks a ton. Saved me so much time.

Reply

243 tizzie September 6, 2012 at 5:49 am

Thanks!

Reply

244 romin September 13, 2012 at 11:23 am

Thanks for share

Reply

245 punit chitra October 4, 2012 at 12:04 pm

nice explanation and recovered my root password. simple and easy steps

Reply

246 phe October 5, 2012 at 1:48 am

thanks bro, this work very easy :)

Reply

247 Mansoor October 17, 2012 at 7:38 am

Thanks worked like a Charm! keep doing the good work!

Reply

248 Keef November 2, 2012 at 3:14 pm

Nice instructions – worked fro me on Windows – many thanks.

Reply

249 Ryan November 5, 2012 at 4:30 am

Thank you. The script is clear and saved my day!

Reply

250 en November 14, 2012 at 12:10 pm

Thanks! Had to use mysqladmin shutdown since both the below commands did not work. On Ubuntu 12.04.

eric@app-server:~$ sudo service mysql stop
stop: Unknown instance:

eric@app-server:~$ /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Reply

251 Edward November 20, 2012 at 4:45 pm

Thanks a ton. You saved hours of hit and trial.

Reply

252 John December 10, 2012 at 7:59 am

Advice does not work if Upstart is being used. Upstart will just keep respawning the process. You must first hide /etc/init/mysql.conf from Upstart.

Reply

253 Allen January 13, 2013 at 4:22 am

Saved me a ton of time and frustration with the mysql root. Thanks a million.

Reply

254 genks February 22, 2013 at 12:22 am

it saved my time! thanks a lot

Reply

255 kutex09 March 4, 2013 at 3:01 pm

Worked very well. Thanks!!
I did it on both development and live server hosting critical University databases today running Suse Linux. Saved me a lot after many days.
There were one or things I had to add *:

(1) Stop the server: /etc/init.d/mysql stop (be sure to do it at off peak hours!)
(2) Start the mysql daemon: # mysqld_safe –skip-grant-tables &
(3)* While its running, open another terminal and login to mysql
# mysql -u root (hit enter)
(4) Now you are logged in.
mysql>use mysql;
mysql>update user set password=PASSWORD(“YOUR_NEW_PASS”) where User=’root’;
you should see the output: Query ok etc.
mysql>flush privileges;
mysql>quit
(5) stop the server: /etc/init.d/mysql stop
(6) *Confirm that no mysql processes are still running (in my case they were and made the server fail to start). To check this: run this
ps ax | grep mysql
Now kill the process IDs of any mysql still running in the background using kill PID
e.g. kill 4425. Do not use kill -9 as this may crash the database entirely!!
Confirm no mysql process is still running: ps ax | grep mysql
(7) Then start the server: /etc/init.d/mysql start
(8) login with your new password: mysql -u root -p

That worked for me on SLES10. hope it helps someone.

Thanks.

Reply

256 Chandana April 10, 2013 at 11:02 am

Thank you for the information. Keep up the good work!

Reply

257 Gayan April 12, 2013 at 5:43 am

Thanx alot,
Supper tutorial….

Reply

258 edram May 7, 2013 at 9:44 pm

Genial…. (Y) Thanks!!!

Reply

259 Andrew May 31, 2013 at 1:05 am

What about specifying an alternate port? I’m trying

mysqld_safe –skip-grant-tables –port=3307

and getting:

130530 21:05:21 mysqld_safe Logging to ‘/var/log/mysqld-error.log’.
130530 21:05:21 mysqld_safe A mysqld process already exists

It doesn’t acknowledge the –port parameter ….

Reply

260 Chamara July 2, 2013 at 5:22 pm

Thank you so much. This post was very useful

Reply

261 ketan July 26, 2013 at 7:29 am

Thanks..it works great :)

Reply

262 Ahmed Samir August 13, 2013 at 1:00 pm

Saved me a lot of time Thank you .

Reply

263 anon August 20, 2013 at 2:49 pm

Cheers – saved a network engineer talking to db admins!

Reply

264 anonymoous September 10, 2013 at 6:26 am

Thank you ….Saved time

Reply

265 Aun Raza September 23, 2013 at 10:49 am

Thanks alot it saved alot of my time. this really works :)

Reply

266 jpk October 4, 2013 at 6:00 am

Thanks for it..
Worked like a charm :)

Reply

267 Ilan October 30, 2013 at 2:53 pm

Can you please tell me what is going on?
It never finishes step 2

ilan@ilan-main:~$ sudo /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]
ilan@ilan-main:~$ sudo mysqld_safe –skip-grant-tables &
[3] 5595
ilan@ilan-main:~$ 131030 16:44:40 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
131030 16:44:40 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
131030 16:44:40 mysqld_safe A mysqld process already exists

I tried opening a second terminal with
sudo mysql -u root
but it told me that it wouldn’t open with no password. What is the story with the log files? Is this is what is stopping it?

Thanks,
Ilan

Reply

268 walter okenye November 13, 2013 at 3:31 pm

wooow! neva been such happy before in the world of linux,…thanx geek.

Reply

269 sandeep November 14, 2013 at 8:50 am

Thank you very much. The post was very useful in recovering my root password for mysql. Thanks a lot !

Reply

270 Piotrek January 20, 2014 at 9:18 pm

Even after so many years since this solution has been posted, it is still invaluable !
Saved me a few headaches….. : – )

Thanx heaps !

Reply

271 Rahul dev January 23, 2014 at 6:58 am

After installing my sql,it ask to enter the password bt couldnt get that one.i tried “root”,”password” but couldnt open…can u give me one solution please….

Reply

272 William February 8, 2014 at 4:15 pm

Excellent style and way to explain.
Thank you.

Reply

273 Disel February 22, 2014 at 1:15 am

I used to be suggested this web site by means of my cousin. I am no longer sure whether this post is written by him as no one else understand such specific about my problem. You are amazing! Thank you!

Reply

274 Jon February 26, 2014 at 9:57 pm

thanks !!!!!!!!

Reply

275 irimiaio March 1, 2014 at 11:08 am

Thanks! It works! :)

Reply

276 papastatic April 8, 2014 at 9:01 am

just wanted to say a sincere thanks to you.

Reply

Leave a Comment

Tagged as: , , , ,

Previous post:

Next post: