9

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?

12

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.

  • @Anil: thanks, you made it an excellent answer. – Eugene Yarmash Feb 21 '12 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.