MAMP mysql broken root user

Question

I've read many threads on SO and a few other places and haven't been able to fix my mysql installation in MAMP.

Background. I made a change to my root user the other day in an attempt to get a local service to connect properly. After rebooting my machine phpmyadmin in mamp no longer displayed any of my dbs and showed "No Privileges" to create a database. I tried many things to restore the privileges that I read here and elsewhere to no avail. --skip-grant-tables does not seem to work. Connecting as root in the shell I don't have permission as localhost but I do as 127.0.0.1.

show grants for root@localhost;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

I've installed a fresh installation of MAMP and copied over my DBs. While it works, I've run into instances where DBs are not showing all the tables.

So I have to solve one of two problems. I either have to fix the original install or make it so the all tables show for my dbs in my fresh mamp install.

Lesson learned here is don't be lazy and screw around with your root user locally and just create another login.

UPDATE When I connect as:

/Applications/MAMP/Library/bin/mysql -uroot -h127.0.0.1 -p
mysql> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------------------------------------+
| Grants for root@localhost                                              |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION    | 
| GRANT ALL PRIVILEGES ON `db`.* TO 'root'@'localhost' WITH GRANT OPTION | 
+------------------------------------------------------------------------+

But when I connect as:

/Applications/MAMP/Library/bin/mysql -uroot -hlocalhost -p
mysql> SHOW GRANTS FOR 'root'@'localhost';
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost | 
+----------------+
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     | 
+----------------+

So when I connect as localhost either by IP or socket I am somehow not logged in as root. I find this very confusing?

Answer 1

After several days of beating my head on this I discovered the solution. It took a while to put the pieces together because the MAMP installation is a bit different then a standard mysql installation. I still don't understand a few of the details but here we go.

Even though I was logging in as root the reality was that I was an anonymous user indicated by the above and ''@'localhost'. So there is obviously a permissions error which has three parts: username, password, and host. So something about my root account changed (I believe it was the password), but how the whole thing responded was a bit wonky, as in instead of telling me my password and username combo was incorrect, I was getting logged in with no permissions. I believe I exacerbated this situation a bit by running some commands like the below.

mysqladmin -u root password 'password'

In the end here is what I did, and I want to make clear this is the instructions for a MAMP installation on OS X, although I'm sure they can be easily adapted for Windoz.

Big thanks to this thread for helping me see the light on how to resolve this.

1. Open the Activity Monitor.
2. Shut down MAMP
3. Ensure that mysql is shut off in Activity Monitor. Now, you might see a _mysqld process running which might be the default OSX mysql running.

4. Run the following command, replacing the port number with the one you use. This will start mysql minus the user permissions.

   /Applications/MAMP/Library/libexec/mysqld --skip-grant-tables --port=8889 --socket=/Applications/MAMP/tmp/mysql/mysql.sock

5. In your console login to mysql with no creds.

   mysql

6. Run the below commands.

   FLUSH PRIVILEGES;
   GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
   

7. Shut down mysqld and restart it without the skip-grant-tables option

8. Login to mysql to confirm that things work.

9. You will need to fix your phpmyadmin as well. I want to preface this with for some reason I do not understand these changes to not propagate quickly, and suddenly just start working.

   Go into /Application/MAMP/bin/phpMyAdmin/config.inc.php and enter the correct credentials for your newly created login. Make sure they are exact including host.

10. Lastly after phpmyadmin is working go into the mysql.user table and see where things could have gone wrong. It's important to understand what got messed up.

There are a few things about what happened in the above that I would love some clarification on, like why MAMPs phpmyadmin credential changes do not work immediately.

Hope all this helps!