Recover MySQL root Password

Posted on in Categories Debian Linux, Linux, MySQL, OpenBSD, RedHat/Fedora Linux, Solaris, Suse Linux, Tips, Troubleshooting, Ubuntu Linux, UNIX last updated August 4, 2010

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld 
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

309 comment

    1. You can recover MySQL database server password with following five EASY steps.

      Why is it that EVERYTIME someone writes something is easy, it is not?

      I have no idea how to shut off mysql. And there is no explanation here.

      1. I had the same problem ‘mysql stop’ required a password :(
        In a terminal on the command line as system root:

        [email protected]# ps -A
        PID      TTY  TIME          CMD
        ....
        23386  ?       00:00:00   mysqld_safe
        23543  ?       00:00:00   mysqld
        ....
        [email protected]# kill -9 23543
        [email protected]# kill -9 23386
        [email protected]# ps -A
        

        Look through the list of processes and confirm the processes were killed.
        Continue with step #2

        1. I am at lubuntu 13.04 and i Can’t use mysqld commands
          i.e when i use # /etc/init.d/mysql stop

          This is shown on terminal:

          “Rather than invoking init scripts through /etc/init.d, use the service(8)
          utility, e.g. service mysql stop

          Since the script you are attempting to invoke has been converted to an
          Upstart job, you may also use the stop(8) utility, e.g. stop mysql”

          when I use # mysqld_safe –skip-grant-tables &
          this is shown:

          140122 23:07:14 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
          140122 23:07:14 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
          140122 23:07:14 mysqld_safe A mysqld process already exists

          (I think this post are a bit old)

          1. Worked to me after using ps -A to check what process is running Mysql then Killing it with kill command, after I folowed the steps and worked well. Tanks!
            This post Is OLD BUT GOLD!

      1. oh my god…. you forced me to comment because your asking “seriously?” to a comment made 6 years earliers LMAOOOOO

  1. thank you! i forgot my password and was afraid I had to reinstall mysql!. you saved me. props to you and msn search!

  2. On most *nix systems as root (or with sudo) you can also do:

    cat /root/.mysql_history|more

    Which is very informative.

  3. Hi,

    When I try stop mysql on my server, I receive a error 1045. Access denied for user: ‘[email protected]’ (Using password: NO)

    Thanks

  4. Thanks! I’ve been looking at numerous other solutions and none of them worked. This one worked, no hassles.

  5. Coolness!!! This site is really the best! I also got a ftpbackup script from here! the best how to’s, I wish you the best and I hope that you could add more!!!

  6. Um… I’ve been trying to actually change the password for about 3 days now, and I tried this tutorial… No errors, but when I start the MySQL client and type in my password it just beeps and goes away :( Is there any other way?

  7. Thanks for the help. This was just what I was looking for. I have no idea how I blew away my password, but at least this helped me get back in.

    Thanks!
    Murdock

  8. I think I read this same article the last time my root acct got locked out. Thanks for hosting this page.

  9. I’ve done everything exactly as it says , but still canot connect with my mysql server. I allways have this error: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
    Even when i do NOT use a password , it just not working! i tryed to reintall mysql through synaptic , but still the same shit… No difference. Can enyone give an advise?

  10. Hi,

    Can I remove an existing password following you guide, except that i leave this portion:

    mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;

    as this :

    mysql> update user set password=PASSWORD(“”) where User=’root’;

    Would this work? I need to remove the password for a bit, for testing purposes…and then set the password again….any help/advise is much appreciated…

    -nisha-

  11. Hi again,

    The problem I mentioned above, well, I tested it,and it works….thank you for the perfect guide ;-)
    This is the i-can’t-remember-number-of-times that one of your articles has helped me… so, thanks for that too!

  12. I tried all others, which failed!!!! This one works!!!! Thank you for making the instructions so simple to follow for us NON-LINUX Gurus. Much Thanks!

  13. Gleb, you need to use the unix shell as root to kill the mysql server process directly, then you can follow the mysql start without privileges command and get back in that way.

    If you don’t know how to get a process list and send it a signal, you should read up just a little bit on that.

    man ps
    man kill

    But something like:
    ps aux | grep mysql
    kill -TERM [processnumber# from command above]

    (yes there is a one step command, but I personally ashew wildcards in my kill commands)

  14. Thanks a ton. I was stuck since a very long time. Could not find anywhere but here it was very clear.
    Thanks very much!

  15. Thank you very much. Windows users, you can do all of those commands from the command prompt, make sure you run it in admin mode, and change directory to where the mysql files are located, so when you run those commands it works. :)

  16. It works — the very only one by far — Kubuntu 8.10
    Description is crisp concise and foolproof

    Thank You very much

    I wish I knew howto tell google

  17. /etc/init.d/mysql stop
    * Stopping MySQL database server mysqld [fail]

    You ware going to need to fill in the gaps. If it fails then…….

  18. Very good, on ubuntu 9.04 mysqld 5.1 come installed with a mysterous password, I reseted it with this procedure and It works fine, thank you.

  19. THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU.

    Most sincerely,
    Darrell Goodman
    Toronto, Canada

  20. This didn’t work for me. I now get the following when trying to log in: (I’m logged in as root):

    ctru2:~ # mysql -u root -p
    Enter password:
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

    What does this mean, and how do I get around it?

  21. if you are using windows, you will still have the problem if you are using phpMyAdmin, open the config.inc.php, should be in your windows directory, change the password in there.

  22. hi i want to know that how to remove or recover password which is on file when we open it in mysql it is asking for password how to remove them plz any one tell

  23. I have tried your solution 5 times. But not working :((.
    [[email protected] /]# /etc/init.d/mysqld stop
    Stopping MySQL: [ OK ]
    [[email protected] /]# mysqld_safe –skip-grant-table &
    [1] 2278
    [[email protected] /]# Starting mysqld daemon with databases from /var/lib/mysql
    mysql
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.0.83 Source distribution

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

    mysql> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> update user set password=’blocialdb123′ where user =’root’;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0 Changed: 0 Warnings: 0

    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    mysql> quit;
    Bye
    [[email protected] /]# /etc/init.d/mysqld stop
    STOPPING server from pid file /var/run/mysqld/mysqld.pid
    090730 13:58:21 mysqld ended

    Stopping MySQL: [ OK ]
    [1]+ Done mysqld_safe –skip-grant-table
    [[email protected] /]# /etc/init.d/mysqld start
    Starting MySQL: [ OK ]
    [[email protected] /]# mysql -u root -p
    Enter password:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
    [[email protected] /]#

  24. hi , at first i want to thank you for your post its very useful for me
    actually i do all of above but when i want to access mysql with root (#mysql -u root -p)
    it said that ” ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) ”

    could you please help me about this

  25. Awesome, I have been playing using MySQL on and off for almost 2years to understand it from a higher level and still getting use to it! This is the best straight forward instructions I’ve seen….and trust me I’ve looked at heaps :)

    So from a newbie, THANKS for taking the time!
    John B.

  26. How do i log in as root user if I do not know the password? Also, when I use the # /etc/init.d/mysql stop, it tells me that ‘#’ is not recognized as an internal or external command. I am not very knowledgeable about these things. I am on a windows system and I don’t know the root password. Please help. Thanks.

  27. Thanks Buddy U make my day

    Thanks again

    can u guide me how i make user in mysql and give them right to use

    If u have solution Guide me

  28. Hi guys. This help looks soo helpfull but i dont know how to use it. i really need to reset my password. but i dont know how to Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option. I went to mysql\bin\mysqld but i cant type or do anything! can someone help me how to do those steps?

  29. Thanks to help me but I have not root user in user table of mysql database.
    Can you please tell me why such kind of things happened?

  30. This was absolutely what they say, hitting the nail on the head. I was getting the error message and was hitting my hed on the wall when this gentleman came along and it worked like smooth butter.
    Thanx man , you rock

  31. Am still trying to find a way to SET the root password. WAMP mysql 3.1.16. Win Vista 32 – currently no password required.

    Nothing on the ‘MySQL Change root Password’ thread works.

  32. It just hangs

    100331 12:28:26 mysqld_safe Logging to syslog.
    100331 12:28:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

  33. Thanks, I have spent three days trying to get MySQL functioning. It seemed that the password was lost, but after running the password still works.

  34. I tried many times… Do not work for me…
    I Can’t change the password and I can’t remember the previous one.
    Maybe I doing some mistake with the line:

    update user set password=PASSWORD(“MyPassword”) where User=’root’;
    when I run: mysql -u root -p

    I put MyPassword…. And I get this error:

    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

    Does anybody knows what I doing wrong?

  35. Hi
    In the first command the prompt returns a messagem saying : ERROR 1045 (2800) Access denied for user [email protected] (Using password NO)

    Even after i have looged in as root in the linux system (sudo su root)

    Any tip ?

  36. actually i used wrong command….
    update mysql.user set password=”root” where user=”root” instead of
    update mysql.user set password=password(“root”) where user=”root”
    plz temme how to recover my password…thnx in advance

    1. !!!! FULL — BUT QUICK — REINSTALL !!!!

      To the ones still suffering from this persistent — and annoying — issue, the best solution I’ve come across so far is to remove & re-install mysql (using yum, apt-get, dpkg, or whatever you like the most).

      Now, the tricky step that most of the online tutorials forget to mention is that after removing mysql and — of course — BEFORE reinstalling it, you MUST remove the original db’s directory, usually under /var/lib/mysql/

      The latter is specially true for CentOS users.

      Here´s a wrap-up of the whole process:

      1) Make sure mysql is not running:
      [[email protected] ~] # /etc/init.d/mysqld stop

      2) Remove mysql:
      [[email protected] ~] # yum remove mysql-server
      [[email protected] ~] # yum remove mysql

      2) Remove the offending db:
      [[email protected] ~] # rm -rf /var/lib/mysql

      3) Re-install mysql:
      [[email protected] ~] # yum install mysql-server
      [[email protected] ~] # yum install mysql
      [[email protected] ~] # yum install mysql-devel

      4) Re-install php’s access to mysql:
      [[email protected] ~] # yum install php-mysql

      5) Start your newly installed db engine:
      [[email protected] ~] # /etc/init.d/mysqld start

      Now you can log in without using a password.

      6) Set a new password for root:
      [[email protected] ~] # mysql
      mysql> USE mysql;
      mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE user=’root’;
      mysql> FLUSH PRIVILEGES;
      mysql> EXIT

      I hope this solution saves you some headaches. It took me several hours to find out what was causing the problem.

      Cheers to all.
      Theo

  37. [email protected]:~# mysql stop
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

  38. hi
    10.6 OS 64bit
    i couldn’t get
    bash-3.2# /etc/init.d/mysql start
    bash: /etc/init.d/mysql: No such file or directory

    on a mac the path is /usr/local/mysql/support-files/mysql.server start

    so i used this /usr/local/mysql/support-files/mysql.server start –skip-grant-tables ( got this from here)

    and followed your instructions

    any idea why mysqld doesn’t work to start or stop server

    i get this
    bash-3.2# mysqld stop
    110321 13:33:12 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.5.10-osx10.6-x86_64/data/ is case insensitive
    110321 13:33:12 [ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

    110321 13:33:12 [ERROR] Aborting

    110321 13:33:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

  39. I don’t have the MySQL root password as I had help setting it up, and the he never gave me the root password I now I can’t find the idiot (for Months).

    I tried the tutorial above, but I can’t get in. The error I get is “ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)”

    Where am I supposed to insert the new username and password?

    Thanks!
    Mats

    1. Mats en the step 4:

      Step # 4: Setup new MySQL root user password

      mysql> use mysql;
      mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
      mysql> flush privileges;
      mysql> quit

      Remigio

  40. mysql.user table may also need to be updated to new password format.
    Run mysql_upgrade (or, earlier, mysql_fix_privilege_tables) to do this.

  41. I encountered the stunning situation where there was no root user

    replace

    update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;

    with

    insert into user(Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) values ('localhost', 'root', PASSWORD("my_password"), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',   'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',    'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');
    
    1. Same here — no root user, 0 rows affected on the update… couldn’t figure it out. your insert worked fine for me, then ran the update to set the password, then flushed privs, then restarted mysql…. done and working now… thanks!

  42. I think there is a mis-communication in your tiourtal…I copied and pasted these two lines:./bin/mysqladmin -u root password ‘**PASSWORD**’./bin/mysqladmin -u root -h **IP_ADDRESS** password ‘**PASSWORD**’and I got this error:Unknown MySQL server host ‘**IP_ADDRESS**’ (1)’Check that mysqld is running on **IP_ADDRESS** and that the port is 3306.You can check this by doing ‘telnet **IP_ADDRESS** 3306′What should I do?

  43. – Quote – My daaabtse is about 1GB, so I set my innodb_buffer_pool_size to 2GB– End Quote –Is that your daaabtse memory size or your daaabtse file size? Thanks

  44. When I tried this for the first time, I got the following error saying,
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
    Then resolved that issue following the steps in http://ubuntuforums.org/showthread.php?t=804021
    Again for the second time, re-tried the steps in this blog. And it PERFECTLY WORKED.

    Thanks alot.

  45. [email protected]:~$ mysqld_safe –skip-grant-tables
    nohup: ignoring input and redirecting stderr to stdout
    Starting mysqld daemon with databases from /var/lib/mysql
    mysqld_safe[7459]: started
    STOPPING server from pid file /var/run/mysqld/mysqld.pid
    mysqld_safe[7469]: ended

    mysqld started in safe mode but suddenly it ended as above. Any idea ??

  46. Thanks! Had to use mysqladmin shutdown since both the below commands did not work. On Ubuntu 12.04.

    [email protected]:~$ sudo service mysql stop
    stop: Unknown instance:

    [email protected]:~$ /etc/init.d/mysql stop
    Rather than invoking init scripts through /etc/init.d, use the service(8)
    utility, e.g. service mysql stop

  47. Advice does not work if Upstart is being used. Upstart will just keep respawning the process. You must first hide /etc/init/mysql.conf from Upstart.

  48. Worked very well. Thanks!!
    I did it on both development and live server hosting critical University databases today running Suse Linux. Saved me a lot after many days.
    There were one or things I had to add *:

    (1) Stop the server: /etc/init.d/mysql stop (be sure to do it at off peak hours!)
    (2) Start the mysql daemon: # mysqld_safe –skip-grant-tables &
    (3)* While its running, open another terminal and login to mysql
    # mysql -u root (hit enter)
    (4) Now you are logged in.
    mysql>use mysql;
    mysql>update user set password=PASSWORD(“YOUR_NEW_PASS”) where User=’root’;
    you should see the output: Query ok etc.
    mysql>flush privileges;
    mysql>quit
    (5) stop the server: /etc/init.d/mysql stop
    (6) *Confirm that no mysql processes are still running (in my case they were and made the server fail to start). To check this: run this
    ps ax | grep mysql
    Now kill the process IDs of any mysql still running in the background using kill PID
    e.g. kill 4425. Do not use kill -9 as this may crash the database entirely!!
    Confirm no mysql process is still running: ps ax | grep mysql
    (7) Then start the server: /etc/init.d/mysql start
    (8) login with your new password: mysql -u root -p

    That worked for me on SLES10. hope it helps someone.

    Thanks.

  49. What about specifying an alternate port? I’m trying

    mysqld_safe –skip-grant-tables –port=3307

    and getting:

    130530 21:05:21 mysqld_safe Logging to ‘/var/log/mysqld-error.log’.
    130530 21:05:21 mysqld_safe A mysqld process already exists

    It doesn’t acknowledge the –port parameter ….

  50. Can you please tell me what is going on?
    It never finishes step 2

    [email protected]:~$ sudo /etc/init.d/mysql stop
    * Stopping MySQL database server mysqld [ OK ]
    [email protected]:~$ sudo mysqld_safe –skip-grant-tables &
    [3] 5595
    [email protected]:~$ 131030 16:44:40 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
    131030 16:44:40 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
    131030 16:44:40 mysqld_safe A mysqld process already exists

    I tried opening a second terminal with
    sudo mysql -u root
    but it told me that it wouldn’t open with no password. What is the story with the log files? Is this is what is stopping it?

    Thanks,
    Ilan

  51. Even after so many years since this solution has been posted, it is still invaluable !
    Saved me a few headaches….. : – )

    Thanx heaps !

  52. After installing my sql,it ask to enter the password bt couldnt get that one.i tried “root”,”password” but couldnt open…can u give me one solution please….

  53. I used to be suggested this web site by means of my cousin. I am no longer sure whether this post is written by him as no one else understand such specific about my problem. You are amazing! Thank you!

  54. This page save my ass.

    But I logged to mysql as debian-sys-maint user, the password is located at /etc/mysql/debian.cnf then I followed the steps on this page.
    The debian-sys-maint user has full privileges.

  55. (mysqld_safe –skip-grant-tables &)this command is not working it getting stuck not getting any output.

    [[email protected] ~]# /etc/init.d/mysqld stop
    Stopping mysqld: [ OK ]
    [[email protected] ~]# mysqld_safe –skip-grant-tables &
    [1] 3650
    [[email protected] ~]# 150908 02:55:32 mysqld_safe Logging to ‘/var/log/mysqld.log’.
    150908 02:55:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

  56. Hi,

    Is there any way to view the password of Mysql command line client.
    I dont want to change or reset the password because of the changing month password policy and I would be in a trouble.

  57. Somehow the password field changed to authentication_string.

    UPDATE mysql.user
        SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
        WHERE User = 'root' AND Host = 'localhost';
    

Leave a Comment