10.3 Connecting the Client to the Server

A MySQL client can connect to a server running on the same machine. This is a local connection. A client can also connect to a server running on another machine, which is a remote connection.

MySQL supports connections between clients and the server using several networking protocols:

• A client can establish a local connection by using TCP/IP, by using a named pipe (on Windows NT-based systems), or by using a Unix socket file (on Unix systems).

• Remote connections are established only over TCP/IP. For remote connections, the client and the server need not be used on hosts that run the same type of operating system because TCP/IP is not operating system–dependent.

On Windows, the servers with -nt in the name (mysql-nt, mysql-max-nt) support named pipe connections. However, even for these servers, named pipes are disabled by default because on some Windows systems their use can cause server shutdown problems. To use named pipes, you must start the server with the --enable-named-pipe option. A local client can establish a named pipe connection to the server by specifying . (period) as the hostname. The client also may name no host at all; in this case, the client attempts a named pipe connection first and falls back to TCP/IP if that fails.

Client programs included with MySQL distributions (mysql, mysqladmin, and so forth) establish connections to the server using the native C client library. However, other interfaces are available. For example, ODBC-based applications can connect to a MySQL server by using MySQL Connector/ODBC. Java applications that use JDBC can connect using MySQL Connector/J. These connectors provide MySQL-specific drivers for the ODBC and JDBC protocols.

The different connection methods are not all equally efficient:

• In many Windows configurations, communication via named pipes is much slower than using TCP/IP. You should use named pipes only when you choose to disable TCP/IP (using the ---skip-networking startup parameter) or when you can confirm that named pipes are actually faster on your particular setup. A client can make sure that it connects using TCP/IP rather than a named pipe by specifying a host value of localhost, 127.0.0.1 (the address of the TCP/IP loopback interface), or the server's actual hostname or IP number.

• On Unix, a Unix socket file connection provides better performance than a TCP/IP connection. By convention, the hostname localhost is special for MySQL on Unix. It indicates that the client should connect to the server using a Unix socket file. To explicitly establish a TCP/IP connection to a local server, specify a host of 127.0.0.1 (the address of the TCP/IP loopback interface) or the server's actual hostname or IP number.

• On any platform, an ODBC connection made via Connector/ODBC is slower than a connection established directly using the native C client library. This is because ODBC adds overhead.

• On any platform, a JDBC connection made via Connector/J is likely to be roughly about the same speed as a connection established using the native C client library.

MySQL clients understand command-line options that enable you to specify the host where the server is running, your username, and password. The "Core Study Guide" discusses these options and how to use them when invoking client programs.