Resetting MAMP’s MySQL Root User Password

Steve October 27 Local Server , , with 62 and 3
Home/Local Server/Resetting MAMP’s MySQL Root User Password

Those of you familiar with MAMP will no doubt know that MAMP’s MySQL root user password is set to root by default and changing it is not as straightforward as it perhaps should be.

I’ve previously described a way of successfully changing the root user password providing, of course, that you know the current password. If you’ve forgotten it, this method will not work and a way of re-setting the password is required.

I’ll be using the Terminal to reset the root password, but first we need to ensure that MAMP’s MySQL server is not running.

If MAMP is running and the Stop Servers when quitting MAMP option is checked then quitting MAMP will suffice.

MAMP's Start/Stop Options

MAMP’s Start/Stop Options

 

 

If not, click the Stop Servers button.

Stop MAMP's Apache and MySQL Servers

Stop MAMP’s Apache and MySQL Servers

 

 

Next, start MAMP’s MySQL server from the Terminal with the --skip-grant-tables option. As this option allows anyone to connect without a password and with all privileges — albeit temporarily — the --skip-networking option is included to prevent remote access.

Copy and paste the following command into a Terminal window and press enter:

/Applications/MAMP/Library/bin/mysqld_safe \
--skip-grant-tables \
--skip-networking \
--socket=/Applications/MAMP/tmp/mysql/mysql.sock \
--pid-file=/Applications/MAMP/tmp/mysql/mysql.pid &

 

When Terminal displays the following message, press enter again to return to the Terminal prompt.

mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql

 

At the prompt type the following command to access the MySQL command interpreter:

/Applications/MAMP/Library/bin/mysql

 

You should see the MySQL command interpreter prompt: mysql>

At the prompt type:

FLUSH PRIVILEGES;

 

Press enter, then type the following replacing NewPassword with the password of your choice:1

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPassword');

 

Press enter, then type:

FLUSH PRIVILEGES;

 

Press enter. Then, to quit the MySQL command line type:

\q

 

You should be returned to the Terminal prompt. Copy and paste the following command to shutdown MAMP’s MySQL server. You’ll be prompted for the new root password when you press enter.

/Applications/MAMP/Library/bin/mysqladmin \
--socket=/Applications/MAMP/tmp/mysql/mysql.sock shutdown -u root -p

 

Having reset the root user password you should follow the instructions here regarding updating the necessary files in MAMP with the new password.

These instructions were adapted from the official MySQL documentation.

 

1 As of MySQL v5.7.6 the PASSWORD() function has been deprecated. This doesn’t affect the current version of MAMP – v4.1.1 released 2017-02-08 – which uses MySQL v5.6.35.

At some point however, a future version of MAMP will most likely use a version of MySQL that doesn’t include the PASSWORD() function. When this happens – as of MySQL v5.7.6 – the command to reset the root password is:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

About the Author

A native Brit exiled in Japan, Steve spends too much of his time struggling with the Japanese language, dreaming of Fish & Chips and writing the occasional blog post he hopes others will find helpful.

62 Responses

59 Comments

  • Jonathan Pereira
    on December 13th, 2016 at 03:14
    Reply

    Very useful and straight forward! Thank you so much!

  • steve
    on September 14th, 2016 at 04:32
    Reply

    This doesn’t work on a Mac.

    Bash command

    /Applications/MAMP/Library/bin/mysqld_safe –skip-grant-tables –skip-networking –socket=/Applications/MAMP/tmp/mysql/mysql.sock –lower_case_table_names=0 –pid-file=/Applications/MAMP/tmp/mysql/mysql.pid &

    generates this nonsense…

    ‘/Applications/MAMP/db/mysql56/Steves-MacBook-Pro-2.local.err’.
    160913 12:25:23 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql56
    160913 12:25:23 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended

    …and this….

    /Applications/MAMP/Library/bin/mysql

    …only generates this message…

    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)

    So, there is no way to know or change the root password. QED.

    • Author
      Steve
      on September 19th, 2016 at 20:58
      Reply

      @steve,

      Works perfectly on my Mac running 10.11.6.

      The problem is there are syntax errors in the command you pasted in your comment that’ll give you the exact same errors you’re receiving. Note that two leading hyphens are required and not the one hyphen as in your command.

      It should be:-
      --skip-grant-tables not –skip-grant-tables,
      --skip-networking not –skip-networking and so on.

      Regards, Steve.

      • Anthony Sykes
        on January 7th, 2017 at 05:47
        Reply

        Bash command

        /Applications/MAMP/Library/bin/mysqld_safe -–skip-grant-tables –-skip-networking –-socket=/Applications/MAMP/tmp/mysql/mysql.sock –-lower_case_table_names=0 –-pid-file=/Applications/MAMP/tmp/mysql/mysql.pid &

        generates this nonsense…

        ‘/Applications/MAMP/db/mysql56/Steves-MacBook-Pro-2.local.err’.
        160913 12:25:23 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql56
        160913 12:25:23 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended

        …and this….

        /Applications/MAMP/Library/bin/mysql

        …only generates this message…

        ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)

        I entered the command with “–” instead of “-” and encountered the same issue. I also tried running it with sudo leading it. Is it still not working. Does anyone have a fix when encountering this issue?

        • Author
          Steve
          on January 11th, 2017 at 13:31
          Reply

          @Anthony @bps @steve

          I’ve done a little digging and the culprit is the option
          --lower_case_table_names=0

          In the version I was using – MAMP 3.5 – the MySQL version is 5.5.42. In MAMP 4.1 the MySQL version is 5.6.34.

          With MAMP 3.5, starting MySQL from the Terminal works without issue, but the MySQL error log – /Applications/MAMP/db/mysql/computer-name.local.err – contains the following warning:

          [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system ‘/Applications/MAMP/db/mysql/’ is case insensitive.

          Setting --lower_case_table_names to 0 configures MySQL to use case sensitive table names. However, macOS Sierra and previous versions of OS X by default use the HFS+ file system which is not case sensitive, hence the warning.

          With MySQL 5.6.34 (MAMP 4.1) this warning has been upgraded to an error causing the process to be aborted. The same error log contains:

          4156 [ERROR] The server option ‘lower_case_table_names’ is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination.
          4156 [ERROR] Aborting

          When starting MySQL via the MAMP GUI the MySQL system variable lower_case_table_names is set to 2.

          So, the solution is to either omit the
          --lower_case_table_names option or include it with a setting of 2; --lower_case_table_names=2. I’d probably choose the former.

          Either way you’ll be able to start the MySQL server from the command line and reset the password.

          I’ve amended the instructions to reflect this change.

          Hope this helps.

          Steve.

      • bps
        on December 28th, 2016 at 02:53
        Reply

        Having the same troubles as Steve, but from copying and pasting exactly what you had in the tutorial.

  • Natalie
    on July 13th, 2016 at 03:41
    Reply

    Somehow, I changed the MySQL root password in MAMP and now I am locked out of localhost. I can’t connect via localhost at all and I can’t access by databases. I have Googled information on how to reset the root password, but I have only been able to find information on MAMP for MAC. I have MAMP for Windows. What are my options, if any? I appreciate any help you can give me. Thank you.

  • Natalie Davis
    on July 12th, 2016 at 12:22
    Reply

    Hello,

    I have a Windows 10 system. For some reason, I can’t move past the skip-grant-tables command. When I paste it into my terminal window and press ‘enter’, it returns “the system cannot find the path specified.” What am I doing wrong?

    • Author
      Steve
      on July 15th, 2016 at 07:34
      Reply

      @Natalie

      According to this document MAMP for windows is installed in C:\MAMP and not /Applications/MAMP as it is on a Mac – hence the error message.

      I have no experience using MAMP on Windows but I assume to start MAMP’s MySQL server from the Windows command line you’d need to replace all occurrences of /Applications/MAMP in the command with C:\MAMP.

      For example:

      C:\MAMP\Library\bin\mysqld_safe –skip-grant-tables –skip-networking –socket=C:\MAMP\tmp\mysql\mysql.sock –lower_case_table_names=0 –pid-file=C:\MAMP\tmp\mysql\mysql.pid &

      Regards, Steve.

  • Denise
    on June 25th, 2016 at 15:03
    Reply

    Been struggling with this for days. You deserve an Oscar.

  • Jennie
    on February 28th, 2016 at 07:31
    Reply

    You just saved my life. Thank you.

  • Uche
    on December 12th, 2015 at 22:52
    Reply

    Very helpful post! I ran into these issues when conflicts arose between my MAMP package and my Mac server instances of apache and a prior installed standalone instance of MySQL. Helped with finding the path to MAMP running things again.

  • asmau
    on November 19th, 2015 at 01:18
    Reply

    You are a life saver…I am a mediocre programmer and i almost ripped my hair out for this. BOOKMARKED!!!!

  • James
    on October 6th, 2015 at 08:45
    Reply

    This was awesome, thanks!

  • Birodh
    on August 2nd, 2015 at 11:40
    Reply

    This really helped my. Tons of thanks.

  • Alki
    on July 16th, 2015 at 07:27
    Reply

    Thaaaank you! You saved my night and the whole day tomorrow! Why don’t you post this answer to stackoverflow, people are struggling for this answer there…

    Anyway, many thanks!

  • exoboy
    on June 3rd, 2015 at 18:07
    Reply

    Thanks! This just saved me TONS of grief!

  • Isuru
    on May 17th, 2015 at 00:41
    Reply

    Thank you very much for this article. Helped me.

  • wurk
    on May 5th, 2015 at 22:15
    Reply

    Thanks a bunch, the config file from mamp didnt work for me. this did!

  • Andre
    on April 16th, 2015 at 11:49
    Reply

    Solved my problem!

  • Nasim
    on April 13th, 2015 at 13:39
    Reply

    Thank you very much!! It nice solution it solved my problem!

  • Amir
    on February 26th, 2015 at 04:32
    Reply

    Thank you very much!!! You are my hero today!!!

  • Fernando
    on February 20th, 2015 at 21:52
    Reply

    Thank you so much! Simple explanation.

  • Brian
    on January 24th, 2015 at 02:05
    Reply

    Thank you

  • Dougal Crowder
    on January 16th, 2015 at 20:46
    Reply

    Thank you so much. This was a real timesaver.

  • John Robinson
    on January 8th, 2015 at 20:44
    Reply

    Thanks so much for this Steve. You’re a scholar and a gentleman!

    • Author
      Steve
      on January 9th, 2015 at 14:40
      Reply

      Thanks John. Glad you found it useful.

  • Liam
    on January 7th, 2015 at 22:52
    Reply

    Took me a few minutes to solve an hour long headache. Thank you!

  • abdallah nofal
    on December 21st, 2014 at 22:34
    Reply

    awesome! really thanks…solved my problem

  • David
    on December 5th, 2014 at 05:59
    Reply

    Nice Solution! It the one that solves me the problem! Regards!

  • Marcel Delfino
    on November 5th, 2014 at 12:16
    Reply

    Awesome! thanks…worked a treast

    • Author
      Steve
      on November 6th, 2014 at 06:11
      Reply

      @Marcel,

      Glad you found it useful.

      Regards, Steve.

  • Dan Jesse
    on September 2nd, 2014 at 07:56
    Reply

    Cheers for this, helped me out so quickly. Really appreciate you taking the time to make it so easy for less clever people like myself 😀

    • Author
      Steve
      on September 2nd, 2014 at 14:06
      Reply

      @Dan,

      Glad you found helpful.

      Regards, Steve.

  • flo
    on August 23rd, 2014 at 23:57
    Reply

    Hello Steve, I tried your solution, it first didn’t work and then after quitting Terminal and opening it again.

    At the first step I got this: “mysqld_safe Logging to ‘/Applications/MAMP/db/mysql/MYCOMPUTER.local.err’.
    chown: /Applications/MAMP/db/mysql/MYCOMPUTER.local.err: Operation not permitted”, but then with “mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql” as you wrote.

    By pressing enter and typing “/Applications/MAMP/Library/bin/mysql” I’ve got this:

    “/Applications/MAMP/Library/bin/mysql
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)
    MYCOMPUTER:~ MYCOMPUTERACCOUNT$ 140823 16:48:17 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended”

    I’m a bit desesperate then it’s already a long time that I can’t do anything because can’t come into localhost…

    • Author
      Steve
      on August 24th, 2014 at 10:07
      Reply

      @Flo,

      Was unable to replicate the error in this situation, but got the same error message when using /Applications/MAMP/bin/startMysql.sh then /Applications/MAMP/Library/bin/mysql. If MAMP’s MySQL server was running I’d expect to see the file mysql.pid in the /Applications/MAMP/tmp/mysql folder, but it wasn’t there.

      Not sure why this happened, but in this situation a simple computer restart fixed the issue.

      Regards, Steve.

      • flo
        on August 25th, 2014 at 06:35
        Reply

        Thank you so much Steve for your answer and your helpfulness !

        It first did not work and I had to do it again, start Disk Utilities programm to repair the Mac autorisations, restart the computer, and the same again. After a while the conection to MySQL server worked finally, a big surprise! …But very shortly then came error 2002 ! Then again Disk Utilities and restart, now it works !!!

        Thank you so much !!!

        • Author
          Steve
          on August 25th, 2014 at 15:51
          Reply

          @Flo

          Glad to hear you’ve got it working.

          Regards, Steve.

  • Joanne
    on August 15th, 2014 at 15:47
    Reply

    Thank you so much!
    It helps me a lot! 🙂

  • PAUL
    on August 13th, 2014 at 02:26
    Reply

    last line didn’t work for me. I got

    /Applications/MAMP/Library/bin/mysqladmin: shutdown failed; error: ‘Access denied; you need (at least one of) the SHUTDOWN privilege(s) for this operation’

    any guesses?

    Killed the processes myself and everything worked. Thanks for the post!

    • Author
      Steve
      on August 13th, 2014 at 13:49
      Reply

      @PAUL

      As the error implies the MySQL user doesn’t have SUPER or SHUTDOWN privileges in order to shutdown the MySQL server which seems odd if it’s the root user. I have no suggestions other than using kill -SIGTERM pid-of-mysqld-process in Terminal which worked for you.

      Regards, Steve.

  • Patrick
    on July 1st, 2014 at 12:43
    Reply

    I’m not sure if you’re still following this comment thread, Steve, but I’ve got a quick question: when I get to the last step of your guidelines to enter the new password in the ‘Enter Password:’ line, Terminal won’t let me type anything. It’s like it is frozen. When I press enter it gives me the error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’.

    Any thoughts on why it won’t let me enter my new password?

    Thanks,

    Patrick

    • Author
      Steve
      on July 1st, 2014 at 13:43
      Reply

      @Patrick,

      In Terminal the cursor will not move when you enter the password. This is normal behaviour.

      Alternatively, instead of ending the command -u root -p and waiting for the password prompt you could add the new password to the end of the command like so -u root -pNewPassword which will circumvent the password prompt. Make sure there is no space between -p and NewPassword

      Regards, Steve.

      • Patrick
        on July 1st, 2014 at 23:23
        Reply

        Beautiful! Thank you, Steve!

        • Author
          Steve
          on July 2nd, 2014 at 06:04
          Reply

          You’re welcome Patrick.

          Regards, Steve.

  • Dominic
    on June 29th, 2014 at 00:53
    Reply

    Brilliant, thank you so much! After an hour of trying to reset my password this sorted me out. You’re a good man.

    • Author
      Steve
      on June 29th, 2014 at 13:36
      Reply

      @Dominic,

      You’re welcome.

      Regards, Steve.

  • Klara Charlton
    on June 9th, 2014 at 10:56
    Reply

    Thank you so much for this excellent blog! As someone pointed out above, basically everything else on the web seems cobbled together in a half-baked manner. You have been of great help today. I am so glad that I found your website! Thank you 🙂

    • Author
      Steve
      on June 9th, 2014 at 12:20
      Reply

      @Klara,

      You’re welcome. Glad you found it useful.

      Regards, Steve

  • Michael
    on May 27th, 2014 at 22:36
    Reply

    Thanks for this, worked perfectly. (Until I found your site I felt like I was in a loop where everywhere I went said something to the effect of “this is a common problem, go find the answer somewhere else”.)

    • Author
      Steve
      on May 28th, 2014 at 07:24
      Reply

      @Michael,

      Glad you found it helpful.

      Regards, Steve.

  • DMZ
    on May 23rd, 2014 at 07:56
    Reply

    This was amazingly helpful where other pages failed. Two corrections:
    FLUSH… needs a ; at the end
    And then for the last command, you may be bomb out if you don’t specify -u root -p (and then enter the password when prompted)

    • Author
      Steve
      on May 23rd, 2014 at 14:48
      Reply

      @DMZ

      Thanks. Have made the necessary corrections.

      Regards, Steve

  • MehmetD.
    on May 4th, 2014 at 06:29
    Reply

    Thank you, it worked perfect. this issue was about to kill me 🙂

  • Bobby Digital
    on February 27th, 2014 at 06:36
    Reply

    Awesome! I was trying other solutions and this is the only one that actually worked. Simple copy and paste solution!

  • Antónia
    on February 5th, 2014 at 02:51
    Reply

    Hello,

    Thanks!
    It was the only thing that solves my problem, of connecting to mysql server.

  • Toby Couchman
    on January 24th, 2014 at 17:12
    Reply

    Just spent around an hour before I came across your site. The tutorial got me up and running in about 5 minutes!

    Thanks a bunch!
    Toby

  • jon
    on October 28th, 2013 at 00:54
    Reply

    I was struggling all day with this issue — “error: 1045: Access denied for user ‘root’@’localhost'” — only your solution got to the root of it! Thanks a lot!

    • Author
      Steve
      on October 28th, 2013 at 06:51
      Reply

      @jon

      Glad you found it useful.

      Regards, Steve.

3 Pingbacks

  • A Solution Made (a little too) Easy with MAMP Pro - Janel Lucia
    on December 30th, 2016 at 09:51
    Reply

    […] Once MAMP Pro was successfully installed I went to the MySQL tab and changed my password. You don’t have to know your old password to change it here, it’s that easy. Not knowing my old password was the main problem I was having. (if you know your old password and want to change it, view the terminal instructions here) […]

  • A Solution Made (a little too) Easy with MAMP Pro | Janel Lucia
    on November 16th, 2016 at 12:45
    Reply

    […] Once MAMP Pro was successfully installed I went to the MySQL tab and changed my password. You don’t have to know your old password to change it here, it’s that easy. Not knowing my old password was the main problem I was having. (if you know your old password and want to change it, view the terminal instructions here) […]

  • mysql: Reset root password without knowing it (Mac MAMP) | Mixed Pickles
    on January 26th, 2016 at 20:34
    Reply

    […] So here is an easy way how to reset your root password. I got it from here: […]

Add comment

©Tech Otaku 2009 - 2017