[HOWTO] Reset MySQL root password on Ubuntu when you’ve forgotten it

To err is human. Humans forget things, MySQL root password is one of those things. However, its not very difficult to reset the root password if you have root access to the machine.

The tutorial does seem a bit long because of all the alternate ways to kill and start the mysqld, but trust me its simple.

Here’s in short what we will be going to do:

• Step 1: Stop mysql server process
• Step 2: Start mysqld with --skip-grant-tables option.
• Step 3: Run mysql without any parameters.
• Step 4: Change the root admin password (old password not required.)
• Step 5: Close mysql and restart mysql server.

And here are the detailed instructions to reset MySQL root password:

• The first thing you need to do is shut down mysqld if its running. In most of the linux flavor (including Ubuntu, ofcourse), you’d execute the following command to stop the mysqld:
  

  sudo /etc/init.d/mysql stop

  This would stop mysqld. You can confirm it by the following command:

  ps -A | grep mysql

  If it shows any entry of mysqld, it means it is still running. You can try the following command to stop it:

  sudo stop mysql

  See if it has closed now, if not, you’d need to remove its entry from the init.d by using the following command and restart the computer so it doesn’t autostart:

  sudo update-rc.d -f mysql remove

  Now after restarting the computer, mysql should not be running you check it from the same command ie. ps -A | grep mysql

• Now you need to again start the mysqld with --skip-grant-tables. This switch enables any client to login to mysql root without password. Use the following command to start mysqld again:
  

  sudo mysqld_safe --skip-grant-tables &

• Now type in mysql to start the client.
• Execute the following queries to change the password:
  

  UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE User=’root’;
  FLUSH PRIVILEGES;

  This would change the root password to new_password. You can change it to any password of your choice.

• Type in exit to shut down mysql.
• You now have to close the mysqld again. You can close the terminal, that would kill the process. If it doesn’t, just kill it. Use the following command:
  

  sudo pkill mysql*

  That would definitely kill the mysqld and all related subprocesses.

• Restart the mysql in normal mode. If you had removed the mysql from starting up using update-rc.d, see below for how to add it to startup scripts back again. To start mysqld execute either of the following command:
  

  sudo start mysql

  OR

  sudo /etc/init.d/mysql start

• To add the mysql to startup script:
  

  sudo update-rc.d mysql defaults

  You can now restart the computer and mysql will start automatically.

That was simple, wasn’t it?

Cheers!