mysql running but cannot connect to 127.0.0.1

Question

I have installed mysql55-server using macports.

I can start the server successfully via:

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql55-server.plist

And confirm I am running the correct mysql:

$ which mysql 

/opt/local/lib/mysql55/bin/mysql

If I run $mysql, I can connect successfully. If I then run:

mysql> show databases;

It shows two databases.

Using Sequel Pro, I can connect via socket:

u: root
p: root
Socket: /opt/local/var/run/mysql55/mysqld.sock

Connects and loads up the databases just fine.

The problem is connecting via 127.0.0.1 or localhost.

If I try to connect using either through standard connection in Sequel Pro, I get:

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Can't connect to MySQL server on '127.0.0.1' (61)

So it seems the mysql server is not identified as 127.0.0.1. In my hosts file I have local host listed as 127.0.0.1

127.0.0.1   localhost
255.255.255.255 broadcasthost
::1             localhost 
fe80::1%lo0 localhost 

Also if I try to run:

$ mysql -h 127.0.0.1 -u root

I get:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

I have the same setup on Mavericks which works fine. I have updated to Yosemite on another computer and are experiencing these problems. I have tried to cross check all settings between machines. It all seems to matchup. Why would localhost, 127.0.0.1, not be connecting, even though the server is running?

UPDATE

Per @Marc B and neverpanic, I changed my.cnf (/opt/local/etc/mysql55/my.cnf). I removed the include to pull data from the original config file at the top and commented out skip-networking:

[client]
port                   =  3306
socket                 = /opt/local/var/run/mysql55/mysqld.sock
default-character-set  =  utf8

[mysqld_safe]
socket                 = /opt/local/var/run/mysql55/mysqld.sock
nice                   =  0 
default-character-set  = utf8

[mysqld]
socket                 =  /opt/local/var/run/mysql55/mysqld.sock
port                   = 3306
bind-address           =  127.0.0.1
skip-external-locking
#skip-networking

character-set-server   =  utf8

[mysqldump]
default-character-set  =  utf8

Restarted mysql and it worked.

Bobine

did you turn on networking support in mysql? It might be running with --skip-networking, which disables tcp connections. mysql should also show up as a listening port in netstat output. and/or you've got port 3306 firewalled. – Shakhty

Answer 1

MacPorts versions of MySQL disable networking using the skip-networking setting by default to allow side-by-side installation of multiple versions of MySQL (which is possible when using sockets, but impossible with TCP ports).

Adjust your MySQL configuration file to enable networking. See also the output of port notes mysql55.

Fie

Is there a way to instruct macports to comment out skip-networking? The cnf file is very clear that upgrades will overwrite any changes. If you want to run only one server, how do you keep that line commented out and networking enabled? – Lest

MacPorts will overwrite $prefix/etc/mysql$version/macports-default.cnf, but not $prefix/etc/mysql$version/my.cnf. Just remove or comment the include at the top of my.cnf that includes macports-default.cnf; since skip-networking is in the latter, that should do what you want. – Fie