Resetting MAMP’s MySQL Root User Password

R

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 interpreter 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.2.1 released 2017-09-28 – which uses MySQL v5.6.35.

At some point a future release of MAMP will no doubt 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.

70 responses

66 Comments

  • hello,

    dacdeMacBook-Air:bin cissy$ 170903 12:39:24 mysqld_safe Logging to ‘/Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err’.
    170903 12:39:24 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql
    /Applications/MAMP/Library/bin/mysqld_safe: line 128: /Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err: Permission denied
    /Applications/MAMP/Library/bin/mysqld_safe: line 165: /Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err: Permission denied
    170903 12:39:24 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended
    /Applications/MAMP/Library/bin/mysqld_safe: line 128: /Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err: Permission denied

    [1]+ Exit 1 /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
    dacdeMacBook-Air:bin cissy$

    It’s not work for my mac,
    and needs your help,,thanks

    • @cissy,

      Looks like there’s a permissions issue with the file /Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err, which is a little strange. Are you logged into your Mac as the administrative user when running the Terminal command? You could try changing the command to this:

      /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 \
      --log-error=/Users/<your user name>/Desktop/temp.err &
      

      This will direct any errors to the named file circumventing the file /Applications/MAMP/db/mysql/dacdeMacBook-Air.local.err and may help determine if it’s simply a permissions issue or something more sinister. Don’t forget to change <your user name> to your user name.

      Regards, Steve.

        • @cissy,

          Sorry to hear you’re still having problems. If you’re thinking of re-installing MAMP, you might want to copy any MySQL databases you’ve created. These can be found in either /Applications/db/mysql or /Applications/db/mysql56 depending on which version of MAMP you’re using. Simply drag either of these folders to your Desktop. You can copy it back once you’ve re-installed MAMP.

          Regards, Steve.

  • 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.

    • @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.

      • 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?

        • @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.

  • 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.

  • 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?

    • @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.

  • 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.

  • 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!

  • 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…

    • @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.

      • 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 !!!

  • 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!

    • @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.

  • 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

    • @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.

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

  • 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 🙂

  • 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”.)

  • 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)

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

  • 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!

4 Pingbacks

Steve

Recent Comments

Recent Posts

Archives