Reset your MySql server password

March 30, 2014#security, #mysql, #servercomments

— Published by Luciano Mammino's profile pictureLuciano Mammino

Few days ago I learned an interesting trick that allows you to reset the password on a MySql server. This trick is pretty useful in case you have lost (or you haven’t ever had) the password of your MySql root user.

You just need to throw some commands at the console. Note that I am assuming you are using Ubuntu or some other Debian derivate. Otherwise the commands may change a bit (but not to much I believe).

Reset your MySql server password

1. Stop the mysql daemon

sudo /etc/init.d/mysql stopRun

2. Disable the security checks

mysqld_safe --skip-grant-tables &

The mysqld_safe command will essentially restarts your MySql server but with the option --skip-grant-tables it also disables the grant tables used for authentication. You might think that this way the MySql server will deny every access attempt, but it will do the very contrary: it will allow any! That’s in fact where the trick lies: this way we are able to login to MySql as root (even if we don’t know the password) and edit the MySql users table to reset the root user password.

Yeah, I didn’t think this was possible before discovering it. But it is, and, even if it feels “insecure”, it might be useful in cases like this.

3. Login as root

mysql -u root -p mysql

It will ask you for a password… Yes, you get it! You can enter whatever you want and it will always grant you access as root! The last argument (mysql) specifies you want to access the MySql internal database (where user credentials are stored).

4. Change the root password

You’re using the MySql shell now. Just run:

update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User="root";

You need to change the "NEW-ROOT-PASSWORD" with a password of your choice (obviously).

5. Restart MySql

You’re back in your bash shell:

sudo service mysql restart

This will restart the MySql server and will enable the security checks again.

So that’s all! You can now login into your MySql server with the new password! And try to not lose it again, at least for a while ;)

Found a typo or something that can be improved?
In the spirit of Open Source, you can contribute to this article by submitting a PR on GitHub



Copyright © Luciano Mammino 2014-2022.

Built with Gatsby, Coffee and a lot of ❤︎.

Loige logo designed by Andrea Mangano.

Hosted on GitHub, accelerated by Cloudflare.

Theme inspired by React documentation.

Icons by Font Awesome.

BlogSpeakingAboutComment Policy