Reset Forgotten MySQL Root Password 

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server. If you have forgotten that password wait for another article. Original article posted on reset mysql root password.

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe --skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql --user=root mysql

update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.

Share this page:

31 Comment(s)

Add comment

Comments

From: at: 2008-04-15 05:50:00

The following statement wil reset passwords for ALL USERS, not just root. This will do the desired thing:

UPDATE user SET Password=PASSWORD('newpwd') WHERE User='root';

Check out http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html for more advice. 

 

From: Kalin Mandaliev at: 2012-07-27 11:49:50

You are incredible :) nice how to !!!!

From: Rana at: 2012-08-09 07:13:59

It worked for me,Thanks a lot. Really worth reading.I do found simile article. at http://www.phptechi.com/how-to-reset-mysql-root-user-password.html hope this help.

 

From: cnemo at: 2008-09-23 02:39:50
From: Anonymous at: 2008-12-08 19:38:14

This howto is wrong and dangerous! please add where user='root' at the end of the update statement!

From: Anonymous at: 2008-12-16 21:07:31

where user='root' must be added... Otherwise, it worked without a problem. thanx.

From: Anonymous at: 2009-02-11 13:35:41
From: Gee Thanks for LEAVING OUT THE MOST CRITICAL SYTAX! at: 2009-02-11 05:32:41

I did this this other day before I saw the comments.  My database is hosed.  Thankfully it was my small system but now I need to figure out how to fix it.  Thanks. 

 Next time please test this on your own server before posting! We appreciate the effort but can't afford this kind of help!

From: Anonymous at: 2009-02-09 01:32:50

Thank you for fucking up my entire database and all it's users!

.. someone either edit or delete this tutorial

From: mlokhandwala at: 2010-07-25 04:30:11

I tried this technique and it worked perfectly. Just that I used

mysqld --skip-grant-tables

to start the deamon without applying priviliges

From: Anonymous at: 2010-10-08 07:40:21

i like to use password recovery software

From: hyperion at: 2010-10-12 20:08:58

tested this on my dev server. smoked the database.

obviously mis-written or not tested by the author.

From: Anonymous at: 2012-11-13 21:52:40

You should never ever tried post on production server!! D

From: Anonymous at: 2012-11-30 16:45:49

They said dev server, dev != production to me. Works perfectly for me though so I wonder what they did wrong.

From: mindstalker at: 2014-07-17 13:40:47

If you start getting a disconnection error after following the root reset steps do this.

sudo mysql_upgrade -u root -pnewpassword

(need to put -p then newpassword with no space as it won't always properly prompt you for password, this will fix the database better than then repair database functionality)

From: santangs at: 2011-04-09 10:53:03

Thank 4 tutor, mysistem worked now..

From: mindchaser at: 2011-06-10 21:40:24

worked for me on Ubuntu 10.10.  Thanks!

From: Anonymous at: 2012-05-02 18:59:05

It worked for me too.. :-)

From: Erik Cayré at: 2012-05-23 09:01:44

Thank you:)

From: Anonymous at: 2012-07-17 13:15:45

also can by

  $ /usr/bin/mysqladmin -u root password 'new-password'

From: Anonymous at: 2012-12-13 23:32:15

You have to enter the current password to use mysqladmin so this does NOT work.

From: natalia at: 2013-04-01 08:40:36

On ubuntu/debian, u can use the debian-sys-maint user to recover it. no need to start mysql in safe mode. you can read the full instructions here: Recover MySQL root password

From: John at: 2013-07-10 08:44:50

kool Article password recovered thanks :) 

however also took help from below URL

http://planetghost.com/mysql/recover-mysql-root-password-if-forgotten

From: Lance at: 2013-12-02 13:03:33

Thank you!! It worked a treat for me.

From: Binita at: 2013-12-06 08:14:22

Thank you. Works perfectly on  MYSQL - 5.6.14.

From: Leopoldo Vettor at: 2014-08-01 00:01:11

Even in 2014!!!! =)

From: [email protected] at: 2014-08-02 20:45:26

GOOD.

thanks.

 

 

From: joost at: 2015-01-19 00:25:22

I can login yes but now all my databases are empty...

From: momox at: 2015-03-06 07:13:19

nice gan.

it's work

From: Georgi Georgiev at: 2015-03-19 11:52:50

Totaly not working.

From: till at: 2015-03-19 12:19:28

Used it several time and it always worked.