I decided some time ago to move on my Mac from MAMP to a native installation of Apache, MySQL and PHP. After installing everything I startet my application and got an error saying the database connection failed. I checked that MySQL was running and that I could connect to it manually. Everything was fine. Running under MAMP with the exact same configuration everything worked. The final deployment server is a Debian machine and there it worked with the exact same settings too. It also worked on a Windows machine using XAMPP.
It was getting late and I just couldn’t understand what the problem was, I read each character of the configured username, password and database name like 100 times to make sure I had everything right. Then I just changed the host from localhost to 127.0.0.1 and didn’t expect anything to change but there it was, my application could connect to the database !
On that evening I just went to bed making a mental note I had to understand what was the difference. The next day I did some research and could figure out what was the problem:
Just looking at the mysql_connect page in the PHP online manual brought the answer. There is a note saying:
Whenever you specify “localhost” or “localhost:port” as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use “127.0.0.1” instead of “localhost”.
Of course, when I searched for an answer online before I found the solution I never saw this manual page. But now it’s clear.
It’s rather confusing since it effectively means that MySQL has redefined localhost to mean “connect to a unix domain socket”. And when it uses unix domain socket, it will ignore whatever you define as a port. This is of course kind of an issue if you want to have multiple instances of MySQL running.
It also looks like the default behavior on Windows is to use TCP-IP. But on Unix-like operating systems, it depends on whether you use localhost or 127.0.0.1.
If you need to use localhost and cannot configure 127.0.0.1, you’ll have to use socat to establish a relay between a unix domain socket and the MySQL tcp port.
One of the reason why it works with some localhost on a machine and not on the other might also be that the path to the unix domain socket is not the one you expect. Usually the path would be /tmp/mysql.sock. But if your mysql instance uses a different one, you should adapt the mysql.default_socket setting in php.ini and point it to the right path (e.g. /opt/local/var/run/mysql5/mysqld.sock, /var/mysql/mysql.sock, /private/tmp/mysql.sock or /usr/local/mysql/run/mysql_socket). If you’re using PDO, the setting you need to change is probably pdo_mysql.default_socket. You should be able to find the right path using the following:
mysqladmin variables | grep socket
or this:
mysqld --verbose --help | grep "^socket"
or this:
mysql_config.pl --socket
You can read the location where PHP looks for the MySQL socket in php.ini (mysql.default_socket, mysqli.default_socket and pdo_mysql.default_socket). If you have no php.ini file yet copy php.ini.default or rename it:
sudo cp /etc/php.ini.default /etc/php.ini
You can then change the path there. After changing the path, you need to restart the Apache web server e.g.:
sudo apachectl restart
If you do not want to change php.ini, you can also create a link:
mkdir /var/mysql ln -s /tmp/mysql.sock /var/mysql/mysql.sock
You might have to use sudo to have the permissions to perform the above actions. Also make sure that the permission of the /var/mysql directory are appropriate.
Note that if you see the socket at the right location but disappearing for unknown reasons, you my want to trying deleting the /etc/my.cnf global options file. I saw this somewhere during my search on the internet but do not quite remember where.
On the other hand instead of changing the path in php.ini you may want to change the path in the MySQL configuration (my.cnf):
[mysqld] socket=/var/mysql/mysql.sock
And restart the MySQL server.
Note that using the following hostname for the connection should also work although I haven’t tried it myself:
localhost:/tmp/mysql.sock
(change /tmp/mysql.sock to the path to the actual MySQL socket).
If you are on Mac OS X, please read the General Notes on Installing MySQL on Mac OS X. It states that the default location for the MySQL Unix socket is different on Mac OS X and Mac OS X Server depending on the installation type you chose and provides a table with a list of location depending on the installation type.
In MAMP, the location should be /Applications/MAMP/tmp/mysql/mysql.sock. So if you are using an external PHP installation with MySQL from MAMP, you’ll probably need to run the following:
sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock