Recovering a lost root password for MySQL

Once in a while (hopefully not to often) you need to recover the root password of a mysql database. Here is a quick guide how I do this normally:

1. Stop the running database (if it isn’t stopped already)
/etc/init.d/mysql stop
2. Start the database with the ’skip-grant-tables‘ option
mysqld_safe –skip-grant-tables &
3. Open the mysql console
mysql -u root
4. Set a new password for root (replace yournewpassword with a password of your choice)
mysql> UPDATE user SET Password=PASSWORD('yournewpassword') WHERE user = 'root';
5. Make the changes work immediately
mysql> FLUSH PRIVILEGES;
6. Stop the database again
kill /path/to/mysql.pid || killall mysqld
7. Start it up again the usual way
/etc/init.d/mysql start

That’s it 🙂