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
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- My 10 UNIX Command Line Mistakes
- 25 PHP Security Best Practices For Sys Admins
- The Novice Guide To Buying A Linux Laptop
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
Facebook it - Tweet it - Print it -
We're here to help you make the most of sysadmin work. So, subscribe!
{ 209 comments… read them below or add one }
THANKS!
SAVED MY ASS!
Thanks , it really works
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.
yes, it does in fact have how – /etc/init.d/mysqld stop
Thanks a lot!
I’ve been googling for 2 hours and your solution was the only one that worked!
thank you! i forgot my password and was afraid I had to reinstall mysql!. you saved me. props to you and msn search!
On most *nix systems as root (or with sudo) you can also do:
cat /root/.mysql_history|more
Which is very informative.
-Much easier! thanks!
Nice :)
Hi,
When I try stop mysql on my server, I receive a error 1045. Access denied for user: ‘root@localhost’ (Using password: NO)
Thanks
HEllo,
This one alo can help you for it.
http://linux-tweaks.blogspot.com/2006/09/access-denied-for-user-rootlocalhost.html
This is great help, thanks for this posting!
i want free download of Recover MySQL root password
Brilliant guide – lifesaver!
thanks for this great post
Thanks! I’ve been looking at numerous other solutions and none of them worked. This one worked, no hassles.
Great tutorial.. it just worked fine
Coolness!!! thanks for this post! already bookmarked!!!hehehe..
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!!!
I got the following error
ERROR! MySQL PID file could not be found!
do have any solution
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?
Open X terminal and at shell prompt use command:
mysql -u root -p
You should able to see an error message.
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
THANK YOU!!!!!!!!!!!! THANK YOU!!!!!!!!!!!
Dude, you rock my world. shotalot
Yeah.. it is workin very well.. it saved me from formatting my Server… college 1……. :)..s o it works………..
Many thanks, this saved me a lot of time.
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
Thank you very much
I love you all. This is the only solution that worked not even de.mysql’s solution worked…
great, works exactly as shown. give the man a cigar!
Vivek,
does this affect the dbases / the rest of the users in any harmful way?
another relieved joomla user here as Im now back in charge of mysql thanks a lot!
Thank you.
God bless.
You saved my ass!!! I can’t thank you enough!
gr8 one! thanks.
I am using a windows op, how do I recover the password under Windows
I think I read this same article the last time my root acct got locked out. Thanks for hosting this page.
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?
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-
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!
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!
Thanks alot!! Also saved me some hours :)
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)
thank you very very much!
thanks for this clear tutorial
Thanks! Many thanks!
Good work. Thanks a alot!
Faisal
BIG TNX!!!
After staying idle most of the day since i messed up the database, this is the best thing that happened on this day
THANKS!!!
Useful and to the point!
Ko.
The gift that keeps on giving, 2 years and counting, this helped me out big time… Thanks!
Fantastic! thanks a lot!
great! good job :)
this is really worth printable…
Thank you, it works now.
thanks this worked perfectly!!
Thanks a ton. I was stuck since a very long time. Could not find anywhere but here it was very clear.
Thanks very much!
Bookmarked for future reference! Thanks for the very straightforward explanation, can’t think of a better way to do it!
Thank you very much! Saved some hours of reinstalling mysql.
Thank you. I’ve been trying using Toad and Password recovery tools but to no avail. This got it done!
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. :)
Thank you , this is very helpful, as some one said it was the one excellent solution worked for me.
my day is better
Thanks a lot!
Its much more elegant than the solution in the official mysql reference.
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
Excellent!
Buy that person a beer! Or other meaningful beverage of choice.
Thanks!!
Thank you! Saved my ass too.
/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…….
amazing… thanks a lot!!
Thanks ever so much
thanx, wasted 2 hours googling and this method works,
Stephen :)
skip-grant-tables…. NICE!
Hi!
Wonderful guide. Congratulations!
Bye!
So Simple, yet so valuable. THANKS!
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.
Thank you very much, I’ve waisted more than 2 hours trying to recover MySql password.
Thanks again :)
You saved me a lot of frustration, Thanks
omg, dude, you’re a genious….
Thank You..
Nice, thanks !
This worked really well. Thank you :)
Thanks!
It works gr8!
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
you are a ruddy hero.
thank you.
Tutoriais como esse deveriam ficar na wikipedia dos tutoriais!
Abraço,
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?
Thanks!!!! It was really easy and worked like charm.
Server version: 5.0.51a-24 (Debian)
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.
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
THANK YOU.
Help is easy and usable ;) GREAT
Thanks a lot pal,
your method rocks, save me a lot of time.
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 /]#
i want to recover my password in windows.what should i do
The new MYSQL has different table name for user. See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html for details.
Thanks! Worked perfectly!
i search for a couple hours Thank you
Nice! Very helpful , Thanks!
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
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.
Great job
Without this i had reinstall and configure my bugzilla server
Thanks a lot
Definitely the most valuable tip of the year so far! Well written, clearly written.
Thank you very much!
Mike I.
Thanks. Very clear and well written, extremely helpful.
thanx
this is gr8 job
so that i recover my mysql password
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.
Cool man !! you’re cool!
Hey… Thanks
this works perfect..
Funciona perfectamente…
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
@ Shekhar, try the following url:
http://www.cyberciti.biz/faq/mysql-user-creation/
Thaaaaaanks!!! finally could set my password with your help, was trying for days.. !!!
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?
i created my joomla with it set up and creeat user root and no password now my user and password is not working
i miss my user and password
helped me with my problem too.
googled around but your solution was the leanest
This works perfect.
Thanks
it works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!thankz a lot
MY a$$ was saved. Id almost thank you in person. Thanks a million and 1.
yess…… its working………Thanks a lot………
Thanks for the above information. It has really helped alot.
Cheers!!
awsem…..brilliant….
thanx a lot…..
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?
Yep, thanks!
Excellent tutorial!!
Thank you very much!
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
Thank’s a lot ..!
Very simple way
thank You so much – this was just what I needed.
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.
That should of course have been MySql 5.1.36
Thanks a lot!
saved my a#$
Thanks a lot man! Your post saved me rite on time!
the most explicitly perfect tutorial yet. Saved My Ass Too
Awesome tip, tks, it was concise while providing enough information to be effective.
Tks.
Thanks dude. Saved me some problems :)
Btw, like was said previously, the commands will work in Windows as well.
Great, thanks.
I hope this works
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
10 Q, it worked perfectly.
Thnks if there were a puntuaction u’ll get a 10
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.
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?
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 ?
Excellent tutorial!
This worked like a charm.
congrats.
Nice. Works very well!
Thanks it worked perfectly!
Thank you! It also worked for me!
thanks for your HELP!!!!
Great article!!! Thanks!!!
Thank you for this information. Will the same steps work on a Windows box from the cmd prompt?
It doesn’t work. Password doesn’t change.
Thanks a lot!!!! saved me from fires of hell!!! lol
thankz a lot! save my pc box
Hello Vivek,
Thanks a Lot!!!
Excellent article. Saved my many applications.
Thanks once again!
Thank you, thank you :)
Sweet and nicely written tut.
Many Thanks.
The procedure works great!!!
GREAT GREAT GREAT!!!
where should I put the codes? can anyone teach me from the beginning? I use Win 7 64 bit.
how can i recover my mysql password from cmd shell(windows environment)…
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
i resolved it…..:D
Thanks so much!
!!!! 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
root@server:~# mysql stop
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)
thank you
anthoer way to do this
perl /scripts/mysqlpasswd root newpass
thank you
THX A LOT
thank you so much brother for posting this….great great help.
eternal gratitude for this one! :)
YOU THE MAN
Thanks a lot! That did it!
Thanks. :-)
Let me echo what everyone else is saying…. THANK YOU!!!
Thank you! Thank you! Thank you!
Fantastic!
Thanks!
Brilliant!!! Thanks a lot mate
It worked like charm !! Excellent !! Thanks a lot…
Thanks a lot! Was very helpful to me too.
All the Best!
Thanks!
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
Thanks a lot VIVEk , it really works.
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
you save my ass :D , thankssss
From Argentina
Thierry
Thanks. It really worked.
thanks!!!. it works great!
thank you very much.. very clear
Works like a charm. Thanks a ton!
Awesome stuff! Saved me a re-installation! Thanks dude!
thanx Vivek… Dude you rock!!!.. Please prove some more link for mysql..
Thanks a million :)
Thankyou so much.. works cheer…………………….
Thanks Vivek!
u just saved my day!! :D
thumbs up!
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.
Hi,
This really worked for newbie like me. Thank you.
Praveen
Thanks a million! That step 2 is a life saver!
Thanks!
You save me!!!
Really helpful information, saved me a lot of time.
thanks a lot, clean information
Thanks A bunch! Simple to follow and woks!
Thanks! very useful.
Thanks guys, this greatly works.
yep,this site having good kb
It really works! Thanks a lot!
it was really helpful..thanks
Using – service mysqld stop/start worked for me.
Awesome Awesome Awesome. Very helpful indeed
I encountered the stunning situation where there was no root user
replace
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');Thanks! This worked well!
Couldn’t be neater, thank you!
thanks a lot . it’s really descriptive :)
Thanks for this.. Worked like a charm on CentOS 5.
Gràcies. feia temps que ho necessitava.
Thank you, I have reset my MySQL Password. Works on CentOS 6, though its mysqld instead of mysql for the start/stop of mysql.
Very easy and Straight forward