How can I determine the connection method used by a MySQL client?

Question

When using a MySQL client (e.g. mysql) how can I determine whether it connected to the server using a Unix socket file or by using TCP/IP?

Answer 1

Finding the transport

Try using netstat -ln | grep 'mysql' and you can see how it is connected by the output. if you have access to shell

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.

If yould like to know the connection type from within the mysql CLI, use the '\s' (status) command.

mysql> \s

The output would have a line like one of the following (on Unix).

Connection:             127.0.0.1 via TCP/IP

or

Connection:             Localhost via UNIX socket

Forcing a particular transport

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:

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP

The --protocol={TCP|SOCKET|PIPE|MEMORY} option explicitly specifies a protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For example, connections on Unix to localhost are made using a Unix socket file by default:

shell> mysql --host=localhost

To force a TCP/IP connection to be used instead, specify a --protocol option:

shell> mysql --host=localhost --protocol=TCP

Protocol types:

• TCP: TCP/IP connection to local or remote server. Available on all platforms.
• SOCKET: Unix socket file connection to local server. Available on unix only.
• PIPE: Named-pipe connection to local or remote server. Available on windows only.
• MEMORY: Shared-memory connection to local server. Available on windows only.

A Unix socket file connection is faster than TCP/IP, but can be used only when connecting to a server on the same computer.