php - Why has mysql stopped working on localhost and how do I fix it?
2013-08
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.
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:
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);
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 tops -eFw
should give you an indication, otherwise check the relevant man page), or using OS X's built-in Activity Monitor (Applications
->Utilities
);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 commandmysqladmin variables
should give you an idea of how it's configured;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 tosocket=/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.
I'm trying to figure out how I can start using mysql through terminal since I'm not much of a fan for phpmyadmin.
I read online where that told me to type this into terminal
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot
and I did and received this
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I've already setup the permissions through phpmyadmin
What can I be doing wrong, the guy in the tutorial didn't say that I had to include my login/pass anywhere in that link, I also did try including my login/pass and it still wouldn't work, it wouldn't execute, just display the help page for mysql.
What's a good third party application that would let me create tables, and work with MySQL? When I was using Windows I would use mysql maestro and it would do the job really well. Since I'm new to the Mac, what is out there?
Try --host=127.0.0.1
because the mysql client is trying to connect using the standard mysql socket (/tmp/mysql.sock
). Specifying an IP address won't use this socket connection. I had to change this in every program to be able to connect to the MAMP database, including Sequel Pro.
I suggest using Sequel Pro for all your Mac SQL DB GUI needs.
As for why your access is deined,
- Can you login to phpMyAdmin using root/root?
- What happens when you leave off the
--host=
portion?
You should leave off the --host
part or add --port=8889
but don't use just --host
. By including --host
without --port
you might be trying to connect to another MySQL installation running at the default 3306
.
It is possible that you changed the mysql
root password during installation. Typing it this way will prompt you for your password, or you could enter the changed password as needed:
/Applications/MAMP/Library/bin/mysql -uroot -p
Also, Sequel Pro is one of the nicest free solutions for the Mac. I use it all the time. To connect to MAMP with Sequel Pro, use the following settings:
host: 127.0.0.1
user name: root
password: root (or if you changed it, whatever that is)
port: 8889
Good luck, I tried to get MAMP to work and gave up on it. Nothing worked trying to get MySQL up and running on my machine until I installed XAMPP, which was surprisingly painless and trouble free. No additional configuration required once you have it set up, except for using its control panel application to start and stop the components you want to run at the moment.
Just posted on Stack Overflow a solution for a similar problem. Thought it might help someone to post it here as well.
I was trying to access MAMP's MySQL through the terminal on Mountain Lion, and getting the same ERROR 1045 (28000).
The --no-defaults flag solved it for me.
/Applications/MAMP/Library/bin/mysql --no-defaults -u root -proot -h localhost