Why has mysql stopped working on localhost and how do I fix it?

Question

I use a MacBook Pro running Mac OS X 10.6.4

For a few years I have used MAMP to test websites locally but suddenly and for no apparent reason when I start the MAMP servers the mysql light stays red.

I'm not very clued up on how to actually run a server which is the main reason I use MAMP. I also have Sequel Pro installed for administering my databases. When I try to connect to mysql in Sequel Pro through a socket connection it says

The socket file could not be found in any common location. Please supply the correct socket location.

and then

MySQL said: Can't connect to local MySQL server through socket 'tmp/mysql.sock' (2)

I can connect and access all my databases if I connect with host 127.0.0.1 but I used to just connect through the socket and all was fine. Also all my testing sites which I hosted locally are no longer being processed by PHP.

I have no idea why this suddenly stopped working and any light someone could shine on the matter would be very much appreciated. A solution even more so.

Thanks.

Solution

I hate to suggest this, but given the level of detail you've supplied I'm guessing the underlying applications that MAMP is comprised of are nearly foreign to you (at least in terms of how they function at a lower level and how they can be manipulated via configuration, etc.), so I believe your best bet is to totally uninstall and reinstall.

However, if you are willing to start digging and learning, let's focus on MySQL first as it seems to be your larger issue:

1. Confirm what MySQLd is doing, or any problems it is having, by checking the logs (a good idea to supply that when you're asking questions, so others can help you);

2. To find the logs and/or what config file MySQLd is using, you'll need to determine how MAMP is calling MySQL - doing a process listing is the quickest way to see this, using ps (not sure of the BSD/OS X method, but something similar to ps -eFw should give you an indication, otherwise check the relevant man page), or using OS X's built-in Activity Monitor (Applications -> Utilities);

3. If no arguments are given to the MySQL daemon, then it's either a wrapper script (a file /mysql/command/of/process/running should tell you that) which you'll need to dig into to find the commands, or it's being called with default parameters... in which case the command mysqladmin variables should give you an idea of how it's configured;

4. Since the UNIX versus TCP socket option of MySQL is configurable your final step will be to find the config file (generally my.cnf) and check that the socket definition is in place (similar to socket=/tmp/mysql.sock), and once confirmed you'll need to also check the permissions are good for the user the MySQL daemon is running as.

Keep in mind most of these steps may become irrelevant, depending on the content of the logs. So finding them is of paramount importance. Likely the file already exists or there is a permission issue.

Comments

[punkrockbuddyholly]

You'd be pretty much spot on with your first suggestion. I'll have a look at the logs and if I can't fix it I'll post them here. Thanks.

[punkrockbuddyholly]

Thanks Zayne, it is fixed. Looking through the logs as you suggested revealed a few things which lead me to an answer. I switched it off then on again!