CODEWITHSUNDEEP
mysql
km6zla
km6zla

Reputation: 4877

Mysql ignoring port argument when connecting to localhost

I have 2 mysql instances running side-by-side temporarily. I went to do a diff of the schemas and knew something was wrong when nothing was reported back.

$ diff --suppress-common-lines -y <(mysqldump --port 3307 -d accounts) <(mysqldump --port=3306 -d accounts)

$ mysql --no-defaults --port 3306 -e "show variables like 'port'" dbname

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

$ mysql --no-defaults --port 3307 -e "show variables like 'port'" dbname                                                                                                                       
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

The port argument is being ignored!

Upvotes: 2

Views: 915

Answers (2)

Barmar
Barmar

Reputation: 780861

By default, connections to localhost use a Unix domain socket, not TCP, so the port number is ignored. The documentation explains:

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.

Another option would be to configure the port 3307 instance to use a different Unix socket filename. E.g. run the server with

mysqld --socket=/tmp/mysql3307.sock --port=3307

and then use

mysqldump --socket=/tmp/mysql3307.sock -d accounts

to connect to it.

Upvotes: 4

km6zla
km6zla

Reputation: 4877

I figured this had to have something to do with being on localhost so I tried connecting remotely and it worked! I got the different ports I expected and the diff I was looking for.

Huh? Weird. Then I wondered if maybe mysql was using the default socket to connect to the mysql instance and indeed it was. I found a protocol argument in the mysql manual and tried to specify --protocol=TCP.

$ mysql --no-defaults --port 3307 --protocol TCP -e "show variables like 'port'" dbname                                                                                                                       
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

Viola!

Upvotes: 1

Related Questions