How To Reset MySQL Root Password

Reset MySQL root password
When you have forgotten your password or using a wrong one, this is what you will see

I recently had to setup a server that was half-assed configured, i.e. MySQL was setup but the guy who did it forgot to write down the password. As you might know, adding a new database, dropping tables, etc. is pretty much impossible when you don’t have the MySQL root password. Here’s a simple tutorial how to reset your MySQL root password.

  1. Stop the MySQL Server
    /etc/init.d/mysql stop
  2. Start the MySQL Server with –skip-grant-tables option. Be careful that MySQL is not accessible from the outside since this option allows everybody to login without username/password
    mysqld_safe --skip-grant-tables
  3. Login to MySQL with a simple command:
    mysql
  4. Use this command to set a new password (MY_NEW_PASSWORD):
    UPDATE mysql.user SET Password=PASSWORD('MY_NEW_PASSWORD') WHERE User='root';
    FLUSH PRIVILEGES;
  5. Restart MySQL Server
    /etc/init.d/mysql restart
  6. Login to your MySQL Server using the newly defined password
    mysql -uroot -pMY_NEW_PASSWORD

This tutorial assumes that you are able to start/stop MySQL on your system. I hope it is of any help to people in a similar situation.