Why won't MySQL client use the port I specify?

Question

I was testing a connection issue and tested port 3307 as specified in my JDBC URL. It worked, so I assumed it was on that port. Then I saw the default port was 3306 and tried random ports, and it still worked. I expected it to fail. Why is it ignoring the port on the command line?

$ mysql -u root --port 999 -h localhost gb
MariaDB [gb]> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |

$ mysql --version
mysql  Ver 15.1 Distrib 10.1.30-MariaDB, for CYGWIN (i686) using  EditLine wrapper

I also tried -P 999 and it worked.

Earsplitting

Answer 1

It's ignoring the port because it's using a local socket connection, not a TCP/IP connection. That's because the host is specified as localhost.

localhost has special meaning in MySQL. It does not resolve to IP address 127.0.0.1 (like we might expect it to, based on our familiarity with that pattern.)

Behavior is documented in MySQL Reference Manual ...

excerpt from https://dev.mysql.com/doc/refman/5.7/en/connecting.html

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:

Brownedoff

I guess so. $ mysql -u root --port 999 -h 127.0.0.1 gb ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused") – Earsplitting

Answer 2

It is like spencer wrote, it's using a socket connection by default.

You can force TCP like that:

mysql -u root -p --port=3306 --protocol=TCP

Luminal

This is the answer that solved my problem on OSX, Thanks for this! @Luminal – Kaisership