There are endless guides out there on how to do this. This is what I found:
- The actual MySQL documentation. RTFM, right? Wrong.
- This link has specific instructions for MAMP users, which helped me understand the folder layout, but ultimately didn’t work.
- Stack Overflow has many answers, and this one helped a little bit. It lead me to this link, where the solution was to properly REVOKE and DROP the user and recreate. But, I was too chicken to drop the root user…
Here is my answer:
# stop the MySQL server. Can also do this from MAMP control panel.
$> killall -9 mysql
# start MySQL server and request it to not load in permissions
$> /Applications/MAMP/Library/bin/mysql --skip-grant-tables &
# launch mysql client. No password will be required.
$> /Applications/MAMP/Library/bin/mysql
# now that you are connected to the server, request it to load permissions
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# now reset the root password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ’newpassword';
The key part of that last statement is the mysql_native_password
part. All of the above linked solutions use this instead:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword’;
But this was failing for me, with the error:
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
The official documentation says that if this fails, try modifying the underlying table directly:
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('newpassword')
-> WHERE User = 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Warning (Code 1681): 'PASSWORD' is deprecated and will be removed in a future release.
Which, as you can see, didn’t work either. So there you are. This is what worked for me on MAMP v6.3, macOS Big Sur, MySql 5.7.32.