Comments on Reset Forgotten MySQL Root Password

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. 

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

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.

here is more Linuxy way to do it: http://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/

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

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

THIS PAGE is CORRECT and more thorough!!!

The correct way to reset mysql root password

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!

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

.. someone either edit or delete this tutorial

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

mysqld --skip-grant-tables

to start the deamon without applying priviliges

i like to use password recovery software

tested this on my dev server. smoked the database.

obviously mis-written or not tested by the author.

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

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

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)

Thank 4 tutor, mysistem worked now..

worked for me on Ubuntu 10.10.  Thanks!

It worked for me too.. :-)

Thank you:)

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

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

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

however also took help from below URL

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

Thank you!! It worked a treat for me.

Thank you. Works perfectly on  MYSQL - 5.6.14.

Even in 2014!!!! =)

thanks.

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

nice gan.

it's work

Totaly not working.

Used it several time and it always worked. 

This is a life saving guidance. Thanks it really helped me.

"In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'." stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx

now solution is:

mysqld_safe --skip-grant-tables

or  mysql.server restart --skip-grant-tables if you have OSX and brew mysql installation

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 authentication_string=PASSWORD('new-password') where user='root';flush privileges;exit;

Thanks!

Terminal 1:

        sudo killall mysqld

        sudo mkdir /var/run/mysqld

        sudo chown mysql:mysql /var/run/mysqld

        sudo mysqld_safe --skip-grant-tables &

Terminal 2:

        mysql --user=root mysql

        UPDATE user SET authentication_string=PASSWORD('123456') WHERE user='root'; FLUSH PRIVILEGES;EXIT;

HI! As you have mentioned first to log in as "root", But my case is I have forgotten the root user's password itsel. Please provide support

Some errors on MySQL version 5.7.18:

The command mysqld_safe --skip-grant-table returns an error:

2017-06-13T21:42:44.090296Z mysqld_safe Logging to syslog.2017-06-13T21:42:44.094763Z mysqld_safe Logging to '/var/log/mysql/error.log'.2017-06-13T21:42:44.099027Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

Then I needed to create the /var/run/mysqld directory and set property to the mysql, otherwise was not possible to login:

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

After this issue this tutorial helped me.

Also the Password field name is authentication_string now.

Then:

update user set authentication_string=PASSWORD('new-password') where user='root';

Cool

Note: Since I use Dan's hosting (hosting danwin1210 me), the MySQL password is stored in the "dashboard" when I log in, so I can't forget it (and the hosting pass is in my AutoFill and mental memory)

:)