Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
PDF (RPM) - 25.5Mb
HTML Download (TGZ) - 6.4Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.5Mb
Man Pages (TGZ) - 158.6Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Connecting to the MySQL Server

4.2.2 Connecting to the MySQL Server

This section describes how to establish a connection to the MySQL server. For additional information if you are unable to connect, see Section 6.2.7, “Troubleshooting Problems Connecting to MySQL”.

For a client program to be able to connect to the MySQL server, it must use the proper connection parameters, such as the name of the host where the server is running and the user name and password of your MySQL account. Each connection parameter has a default value, but you can override them as necessary using program options specified either on the command line or in an option file.

The examples here use the mysql client program, but the principles apply to other clients such as mysqldump, mysqladmin, or mysqlshow.

This command invokes mysql without specifying any connection parameters explicitly:

shell> mysql

Because there are no parameter options, the default values apply:

  • The default host name is localhost. On Unix, this has a special meaning, as described later.

  • The default user name is ODBC on Windows or your Unix login name on Unix.

  • No password is sent if neither -p nor --password is given.

  • For mysql, the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line:

shell> mysql --host=localhost --user=myname --password=password mydb
shell> mysql -h localhost -u myname -ppassword mydb

For password options, the password value is optional:

  • If you use a -p or --password option and specify the password value, there must be no space between -p or --password= and the password following it.

  • If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Other users on your system may be able to see a password specified on the command line by executing a command such as ps auxw. See Section, “End-User Guidelines for Password Security”.

As just mentioned, including the password value on the command line can be a security risk. To avoid this problem, specify the --password or -p option without any following password value:

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

When the password option has no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, mydb is not interpreted as a password because it is separated from the preceding password option by a space.)

On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MySQL does not have any limit for the length of the password. To work around the problem, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.

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.

Clients determine what type of connection to make as follows:

  • If the host is not specified or is localhost, a connection to the local host is assumed:

    • On Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled.

    • On Unix, the client connects using a Unix socket file. The --socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

  • On Windows, if host is ., or TCP/IP is not enabled and --socket is not specified or the host is empty, the client connects using a named pipe, if the server has named-pipe connections enabled. If named-pipe connections are not enabled, an error occurs.

  • Otherwise, TCP/IP is used.

The --protocol option enables you to establish a particular type of connection even when the other options would normally default to some other protocol. That is, --protocol may be given to specify the connection protocol explicitly and override the preceding rules, even for localhost.

Only connection options that are relevant to the selected protocol are used or checked. Other connection options are ignored. For example, with --host=localhost on Unix, the client attempts to connect to the local server using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number.

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, 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=
shell> mysql --protocol=TCP

If the server is configured to accept IPv6 connections, clients can connect over IPv6 using --host=::1. See Section 5.1.11, “IPv6 Support”.

On Windows, you can force a MySQL client to use a named-pipe connection by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. If named-pipe connections are not enabled, an error occurs. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

Connections to remote servers always use TCP/IP. This command connects to the server running on using the default port number (3306):

shell> mysql

To specify a port number explicitly, use the --port or -P option:

shell> mysql --port=13306

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix will use a socket file by default. You will need to force a TCP/IP connection as already described or any option that specifies a port number will be ignored.

For this command, the program uses a socket file on Unix and the --port option is ignored:

shell> mysql --port=13306 --host=localhost

To cause the port number to be used, invoke the program in either of these ways:

shell> mysql --port=13306 --host=
shell> mysql --port=13306 --protocol=TCP

The following list summarizes the options that can be used to control how client programs connect to the server:

  • --default-auth=plugin

    A hint about the client-side authentication plugin to use. See Section 6.3.6, “Pluggable Authentication”.

  • --host=host_name, -h host_name

    The host where the server is running. The default value is localhost.

  • --password[=pass_val], -p[pass_val]

    The password of the MySQL account. As described earlier, the password value is optional, but if given, there must be no space between -p or --password= and the password following it. The default is to send no password.

  • --pipe, -W

    On Windows, connect to the server using a named pipe. The server must be started with the --enable-named-pipe option to enable named-pipe connections.

  • --port=port_num, -P port_num

    The port number to use for the connection, for connections made using TCP/IP. The default port number is 3306.

  • --protocol={TCP|SOCKET|PIPE|MEMORY}

    This 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

    The following table shows the permissible --protocol option values and indicates the platforms on which each value may be used. The values are not case-sensitive.

    --protocol Value Connection Protocol Permissible Operating Systems
    TCP TCP/IP connection to local or remote server All
    SOCKET Unix socket file connection to local server Unix only
    PIPE Named-pipe connection to local or remote server Windows only
    MEMORY Shared-memory connection to local server Windows only
  • --shared-memory-base-name=name

    On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL. The shared-memory name is case sensitive.

    The server must be started with the --shared-memory option to enable shared-memory connections.

  • --socket=file_name, -S file_name

    On Unix, the name of the Unix socket file to use, for connections made using a named pipe to a local server. The default Unix socket file name is /tmp/mysql.sock.

    On Windows, the name of the named pipe to use, for connections to a local server. The default Windows pipe name is MySQL. The pipe name is not case-sensitive.

    The server must be started with the --enable-named-pipe option to enable named-pipe connections.

  • --ssl*

    Options that begin with --ssl are used for establishing a secure connection to the server using SSL, if the server is configured with SSL support. For details, see Section 6.4.2, “Command Options for Encrypted Connections”.

  • --user=user_name, -u user_name

    The user name of the MySQL account you want to use. The default user name is ODBC on Windows or your Unix login name on Unix.

It is possible to specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Jan Steinman on February 14, 2015
In the discussion above regarding "localhost" on UNIX systems, there is much explanation on how to force that to be a TCP/IP connection instead of a socket, as though that is a desirable thing to do.

A socket connection will always be faster and have less overhead than a TCP/IP connection to, and should be the preferred choice in most circumstances.