How to Recover or Reset MySQL root Password after You Forgot and Lost It

MySQL has come with a safe mode wherein access privileges are not checked, which essentially enables you to log in anonymously to change anything in any database.

And we are going to get in this safe mode to reset the lost MySQL root password.

1. First, you need to stop the current MySQL daemon by:
   /etc/init.d/mysql stop
2. Then you can start the safe mode of MySQL and skip privileges check by:
   mysqld_safe --skip-grant-tables &
   The & in the end gets it to run in the background so we can continue to reset the root password.
3. Now you can log into the MySQL server anonymously (well not anonymously but without privilege check):
   mysql --user=root mysql
4. And reset the root password by updating the user table of the mysql database:

   UPDATE user SET Password=PASSWORD('newrootpwd') WHERE user='root';
   FLUSH PRIVILEGES;

5. Get out of the safe mode and restart the normal MySQL daemon:
   /etc/init.d/mysql restart
6. At last, you should be able to connect to the database server by your new root password.

As always, you will have to log in as root of the entire server or you won’t be able to stop the MySQL daemon and start the safe mode in the first place.

Note that I’m recovering the root password of MySQL in Ubuntu 9.04 Jaunty with MySQL installed by aptitude. Specific procedures may vary distribution by distribution but I’m sure the fundamental logic remains the same: getting in the safe mode to reset the root password.