How to change your MySQL User Password

Today I had to admit that I did not remember the password for a MySQL user account. Since it’s saved in encrypted form simply reading it out won’t mean I can recover it. A Google search did eventually bring up the correct way of doing it, however it took me quite some time – so here’s how you can do this:

Login to your MySQL server via the following command:

mysql -u root -p

and enter your MySQL root password. This will get you into the MySQL prompt. First thing we want to do is select the database which holds user accounts (as well as a lot of other important MySQL related information), aptly titled mysql:

use mysql;

You’ll get a message telling you that the database has changed. Now change the password for a given user account using this command:

update user set password=PASSWORD('newpassword') where user='username';

OK I know this is always cryptic when it’s not explained properly. Let’s assume here that our username is jeff and our new password is thomas. Then your actual command would look like this:

update user set password=PASSWORD('thomas') where user='jeff';

Makes sense, right? Now your password is changed in the database but they haven’t filtered into memory yet. Let’s change that by typing

flush privileges;

And voila – your password has been updated. There’s no need to restart the MySQL demon. Exit the MySQL with

exit;

and you’re all done.