Resetting forgotten MySQL root password

Posted on | May 27, 2011 | No Comments

If you have forgotten your root password for MySQL, then don’t panic. Although, if you don’t have root shell access, then do panic!

Anyway, to reset a password, you need to stop MySQL and restart it with a special setting that allows you to login without passwords. Obviously this is a dangerous switch and you have to remember to stop and start MySQL after you’ve finished!

Linux:

Login with superuser access (sudo su)

> /etc/init.d/mysql stop

This will stop the MySQL service on the server, and all connections will be cut off.

Then we need to start MySQL with the secret switch:

> mysqld_safe --skip-grant-tables &

I don’t get any messages after this, but you may get one saying the mysqld_safe has started.

Then connect as root:

> mysql -u root

You will be connected. And you can just assign the root user a new password:

mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD(NEWPASSWORD) WHERE user = "root";
mysql> FLUSH PRIVILEGES;
mysql> exit;

That’s it. So now, just stop and start mysql again (as superuser) and were done:

> /etc/init.d/mysql restart

 

Comments