Why is MySQL connecting through socket even though I'm specifying host and port?

Question

When trying to connect to a MySQL database running on port 3306 on localhost (using the command line tool mysql), I'm getting the error:

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

Here's the command I'm using:

> mysql -h localhost -P 3306
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Specifying user and password also doesn't work:

> mysql -h localhost -P 3306 -u dbuser -p
Password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Why is it trying to use a UNIX socket file (as it seems to be doing here) when I'm specifying host and port?

Answer 1

You need to use 127.0.0.1 instead of localhost:

> mysql -h 127.0.0.1 -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8347701
Server version: 5.6.35-80.0-log Percona Server (GPL), Release 80.0, 
Revision f113994f31
...

You're in!

Answer 2

MySQL client infers the protocol from the provided host.

For a given connection, if the transport protocol is not specified explicitly, it is determined implicitly. For example, connections to localhost result in a socket file connection on Unix and Unix-like systems, and a TCP/IP connection to 127.0.0.1 otherwise.

Source: MySQL Docs

To avoid that you can specify the protocol yourself:

> mysql -h localhost -P 3306 --protocol TCP

You should be able to log in :D.