118

This is what I'm doing:

mysql --host=localhost --port=9999 mysql -u root -p --execute="show tables;"

The command works (connecting to port 3306) no matter what I provide in --port argument. I have two mysql servers running on one machine, and want to connect to the second one by explicitly providing its port number. What's going on? Why does mysql ignore this parameter?

Improve this question

2 Answers 2

Reset to default
207

When localhost parameter given, MySQL uses sockets. Use 127.0.0.1 instead.

Improve this answer
7
  • 22
    Quite infuriating that this fact isn't mentioned on the mysql man page.
    – Janek
    Nov 15, 2016 at 15:56
  • 2
    Besides accepted answer here, a bit more explanation can be found in this mysql bugreport and also link to man page. As one of commenters in bugreport page, I also don't understand why doesn't client at least throw a warning in case of using 'localhost' in connection string.
    – bukva-ziu
    Mar 26, 2017 at 8:51
  • 3
    You're a life saver man!
    – Touqeer Shafi
    Oct 17, 2017 at 11:56
  • 4
    Wow, unbelievable that this insane bug is still present 10 years later.. I guess that says a lot about Oracle as well as MariaDb teams. To be clear: the client actually takes the port command, it complains if the port is wrong but it ignores it and connects by socket instead. I started debugging the binary when I found this answer.
    – John
    Dec 20, 2019 at 4:23
  • 1
    A bug left long enough becomes expected behaviour which cannot be changed otherwise you'd break backwards compatability.
    – Jonno_FTW
    Apr 2, 2020 at 7:21
0

Only connection options that are relevant to the selected transport protocol are used or checked. Other connection options are ignored. For example, with --host=localhost on Unix, the client attempts to connect to the local server using a Unix socket file, even if a --port or -P option is given to specify a TCP/IP 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 (instead of localhost), or the IP address or name of the local server. You can also specify the transport protocol explicitly, even for localhost, by using the --protocol=TCP option. Examples:

mysql --host=127.0.0.1

mysql --protocol=TCP

https://dev.mysql.com/doc/refman/8.0/en/connecting.html

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .