why is mySQL connecting at any/all ports

Question

I'm running Linux Mint and trying to connect to mySQL this way

mysql --port=3306 -u root -p

Then it prompts me for my password. This is all fine. Why is it that when I type something like this it still works....

mysql --port=1234 -u root -p

Should that not fail since there is no mySQL server running on port 1234?

The reason I am asking this is because I want to create a SSH tunnel to connect to a database on another server. Let's say the SSH tunnel will forward all my traffic from localhost:3308 to myremoteserver:3306. Since my local mySQL server is accepting my connections on all ports, I cannot actually connect to port 3308 and hit the remote server. I am still hitting my local server....

Even if my SSH tunnel options might have been wrong, I was wondering if anyone knew why I can connect to port 1234 and it still hit my local mySQL server running on 3306?

Solution 1

IIRC mysql connects you to a Unix socket if you are connecting to localhost. Since it does not connect you via TCP in this case, there is no port involved and the port number you give does not matter.

Edit: Not sure if this is true on all systems, but If I use 127.0.0.1 or the hostname instead of localhost, mysql connects via TCP and the port number does matter - I can connect with the correct port number only.

Solution 2

To force a TCP connection use --protocol=TCP.

Example:

First the SSH tunnel

ssh -L 4000:localhost:3306 server.ch

and then connect to the remote mysql server with

mysql -h localhost --port=4000 --protocol=TCP -u root -p

Solution 3

It will ask you for your password before it tries to connect. If you enter your password (or anything else for that matter), and let it proceed, it will respond with something like:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock'

Solution 4

@titanoboa, thx for this! I was having the same issue. Just to add you can actually force TCP connection even for localhost using the following

[client]
port = 3306 
socket = /var/run/mysqld/mysqld.sock 
protocol = TCP

Cheers

View more solutions

Comments

[Girish Dusane]

I'm sorry I forgot to mention that it actually connects me after I enter my password...

[Girish Dusane]

Perfect thanks! Worked when I used 127.0.0.1. Is there anyway I can force mysQL to use TCP even if I type in localhost?

[Lee]

@girdus: You can create an option file. in the [client] section, add the line host=127.0.0.1. That won't change what happens when you use localhost, but it will force the default to be 127.0.0.1 instead of localhost. (see the link for documentation on how the file is structured, and where it should go.

[Yohanim]

it happened also on host=0.0.0.0