Resetting a Lost MySQL password
Recently, I had the need to reset a lost MySQL root password. As it turns out, if you have root access to the server, it’s simple to the point of being trivial… so since I’m sure other people are bound to be in the same boat I was (and since I’ll forget it if I don’t write it down), here’s how you do it. Note that the server I was working on was running Debian. Other flavors of Linux may require some slight tweaks.
First thing to do is tell MySQL to stop, which can be accomplished with a simple command:
/etc/init.d/mysql stop
Now, start it up again in the background using the skip-grant-tables flag.
/usr/bin/mysqld_safe --skip-grant-tables &
This will let you connect to MySQL as root without a password (which is obviously not very secure, but this is only temporary). So, go ahead and connect:
mysql --user=root mysql
Now that you’re in MySQL, you can run queries. Specifically, you can run one that changes root’s password, flush priviledges, and exit:
update user set Password=PASSWORD('your-new-password-goes-here') WHERE User='root';
flush privileges;
exit
At this point you should be back at the command line. Remember that MySQL is still running in a very unsecure state, so we need to fix that. Bring the process you started in the background back into the foreground:
fg
And then press Ctrl+c to kill it.
Now that MySQL is shut down again, you can restart it in its normal mode:
/etc/init.d/mysql start
That’s all there is to it. If everything went as expected, you will have successfully changed the MySQL root password, and can use it to log in:
mysql --user=root --pass=your-new-password-goes-here
