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

To determine the connection method that is used by MySQL connection, the below command can be used −

netstat −ln | grep 'mysql'

On Unix, MySQL programs treat the host name ‘localhost’ in a special manner. Hence, it behaves differently than what is expected of it.

Type of Connection

To know the type of connection from within the mysql CLI, the below command can be used −

mysql> \s

Output −

Connection: 127.0.0.1 via TCP/IP
(or)
Connection: Localhost via UNIX socket

TCP/IP connection to the local server

To ensure that the client makes a TCP/IP connection to the local server, the --host or -h can be used. This will specify the host name value as 127.0.0.1, or the IP address or name of the local server. The connection protocol can also be specified explicitly, for localhost as well, with the help of the --protocol=TCP option. Let us see an example −

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

The --protocol={TCP|SOCKET|PIPE|MEMORY} option specifies explicitly that a certain protocol has to be used to connect to the server

Connections on Unix to localhost

Connections on Unix to localhost are made with the help of a Unix socket file by default. Let us see how this done using the below command −

shell> mysql --host=localhost

To force a TCP/IP connection to be used, a --protocol option can be specified. Let us see how it can be done −

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

Some of the protocol types have been listed below −

• TCP − TCP/IP connection to connect to a local or remote server. It is available on all platforms.

• SOCKET − It is the Unix socket file connection to local server. It is available on Unix only.

• PIPE − It is the named-pipe connection to local or remote server. It is available on windows only.

• MEMORY − It is the shared-memory connection to local server. It is available on windows only.