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.
Where to run this command?
I simply have access of phpMyAdmin.
You run this command from your SSH prompt. You need to be able to login to MySQL via SSH. If you only have access to phpMyAdmin then you can browse to Privileges, select your user name and change it that way.
And what if you have forgotten the root password?
If you’ve forgotten the MySQL root password you can follow these articles to create a new one:
Easy method: http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Complex method from the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html