MySQL: Reset Lost Root Password

Here is a quick Step-by-Step “how to” which helps restoring MySQL root password that was lost/forgotten.

It happens to everybody, especially if several distributed (different) systems are maintained, where the password is not the same. Here is what needs to be done to restore it:

Step 1: Stop MySQL daemon if it is currently running

Depending on the operating system MySQL is installed on, the daemon can be checked/stopped differently. Here is an example on how to do it in Unix-like systems.

[ NOTE ]: You might need to run it as a Unix System superuser (root) - depending on 
          how the system is configured, and what permissions your Unix account is granted)

Here is how to stop/kill the existing mysql daemon, in case it is running:

      ps -ef | grep mysql      - checks if mysql/mysqld is one of the running processes.
 
      pkill mysqld             - kills the daemon, if it is running.

Note: if pkill (’process kill’) is not on a particular Unix system, use kill -9 ‘pid’, where ‘pid’ corresponds to processes that were found with ps -ef | grep mysql

Step 2: Run MySQL safe daemon with skipping grant tables

      mysqld_safe --skip-grant-tables &

Step 3: Login to MySQL as root with no password

      mysql -u root mysql

Step 4: Run UPDATE query to reset the root password

In MySQL command line prompt issue the following two commands:

      UPDATE user SET password=PASSWORD("ualue=42") WHERE user="root";
      FLUSH PRIVILEGES;

“ualue=42” is a common password for “The Hitchhiker’s Guide to the Galaxy” people which reads “Ultimate Answer to Life, the Universe, and Everything=42“

Step 5: Stop MySQL safe daemon

Follow the first two steps, but this time kill (pkill) “mysqld_safe” instead of “mysqld”

Step 6: Start MySQL daemon

Depending on the operating system (Unix-like examples):

      /etc/rc.d/rc.mysql start

OR

      /etc/init.d/mysql start

OR

      /etc/rc.5/mysql start

etc.. check existing MySQL configuration

Step 7: Root password is reset and ready to use

Password is reset. Privileges are flushed. Start MySQL and login as root with the password set in step 4:

      mysql -u root -p mysql

Note: sometimes (most of the time) ‘root user’ privileges are required for the system (OS) in order to stop/start processes

what is next? Reset Lost Password in Sun Application Server

261 comments

  1. great guide, thanks so much =)

  2. Thank you! I’ve been futzing with the crap howto on the mysql main site with no luck.. Maybe you can suggest including your guide on mysql’s page?

  3. @QED,

    Sure, very welcome! If you believe the guide is useful feel free to suggest mysql site to reference to it. You (people who find this guide helpful) are the only true measure of whether it is indeed useful or not, I can’t really claim it is :)

    /Toly

  4. A simple way for the new root password (if MySql daemon is stoped):
    #mysqld_safe –skip-grant-tables mysqladmin -u root flushprivileges password “new_password”

    $mysql -u root -p
    Enter password:
    mysql>

    It’s simple.

  5. Thanks for this post. One suggestion: for the Fedora users, Step 6 should be replace by service mysqld start

  6. Thank you very much, it really saved my life ;-)

  7. Descriptive, Fabulous and just what I wanted..!

  8. where to write all these commands…in CMD or MYSQL :(

  9. Dear Toly,
    I really would like to solve it but I can’t! in my case there’s no way to stop/kill the existing mysql daemon. Neither with “pkill mysqld” nor with “sudo kill -9 9470″ I succeed, infact when I run:
    sudo mysqld_safe –skip-grant-tables
    it always says:
    “120109 23:15:40 mysqld_safe A mysqld process already exists”
    it’s very sad, I know I’m wrong in something but what?
    someone can help me?
    thans to all

  10. @Lore,

    Search for mysql process(es):

    ps -ef | grep mysql

    which, in case mysql(d) is running, should give you:

    mysql    22211     1  0  2011 ?        00:27:12 /usr/sbin/mysqld

    where “22211″ is the mysql’s PID (process ID) you’d need to kill ( in your case the number will be different ).

    And kill it with “kill -15 22211″ or with “kill -9 22211″ if 15 does not work.

    /Toly

  11. Cathryn Mataga

    Ooh, nice, thanks. This actually worked. (After futzing, like others, with instructions on the main site.)

  12. Thanks so much!

  13. wonderful it actually worked for me.thank you

  14. Fantastic! I have spent much frustrating trying to figure out how to fix this MySql installation bug on Mac. Your instructions (after much time searching on Google) finally solved it. I only wish your post had surfaced higher on Google. I actually arrived here from someone else’s link.

  15. Hey man, Thanks sooooooooooooooooo much for this tutorial!!!! You are a LIFESAVER. I have looked for days and couldn’t find anything until I came to this!!!

  16. Great article ! saved the day for me . Thanks !!

  17. I been trying to use mysql for months. I have to do it cause of a program for work. I do not understand any of these comments and only get error messages…I did not even realize that I set a password to begin with…
    Nothing wotk I can’t create a database and load data into cause it gives me some permission deined message about a root and @ which I have no idea what it even means!
    Please could anyone help me. And in a understandable non computer expert language.

    Step 2: I get follwing error message:
    Gustavs-MacBook-Air:~ gustav$ 121208 18:28:39 mysqld_safe Logging to ‘/usr/local/mysql/data/Gustavs-MacBook-Air.local.err’.
    cat: /usr/local/mysql/data/Gustavs-MacBook-Air.local.pid: Permission denied
    rm: /usr/local/mysql/data/Gustavs-MacBook-Air.local.pid: Permission denied
    121208 18:28:39 mysqld_safe Fatal error: Can’t remove the pid file:
    /usr/local/mysql/data/Gustavs-MacBook-Air.local.pid
    Please remove it manually and start /usr/local/mysql/bin/mysqld_safe again;
    mysqld daemon not started
    /usr/local/mysql/bin/mysqld_safe: line 107: /usr/local/mysql/data/Gustavs-MacBook-Air.local.err: Permission denied

    Step 3
    Enter password:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

    etc etc etc. I dont understand any of this. nothing! I I have to use it for a project I am doing.

  18. On Ubuntu, MySQL is compiled with –disable-grant-options
    –skip-grant-tables isn’t available.

  19. On ubuntu/debian, u can use the debian-sys-maint user to recover it. no need to start mysql in safe mode. you can read the full instructions here: http://newexception.com/recover-mysql-root-password

  20. yes

  21. Recover MYSQL root paswword in five easy steps…
    http://www.itsprings.com/?p=207

  22. Recover MYSQL password in five easy steps. :)
    http://www.itsprings.com/?p=207

  23. Hi , any alternate way to recover the password without mysqld restart ?

  24. same problem with me, I am windows 7 user.
    I can access my php admin, but when I login my Mysql shows error on home page of phpmy admin

    General Settings
    Change password

    and in Database column
    Create database: No Privileges

    Any help would be highly appreciated

    Thanks
    joshi

  25. This guide works well for my Centos 6.3 mysql 5.5 installation.
    I would like to note that somehow my root user had a hostname that simply would not work. In order to restore access to the root account I had to follow this guide but add the step of setting the user.host to 127.0.0.1.
    update user set host=’127.0.0.1′ WHERE user=”root”;
    Only after that step was I able to connect and login as root.

tell me something...
  1. (required)
  2. (valid email - optional)
  3. Captcha
  4. (required)