1.2. Invoking Client Programs

MySQL client programs can be invoked from the command line, such as from a Windows console prompt or a Unix shell prompt. When you invoke a client program, you can specify options following the program name to control its behavior. Options also can be given in option files. Some options tell the client how to connect to the MySQL server. Other options tell the program what actions to perform.

You also specify connection parameters for graphical client programs such as MySQL Query Browser and MySQL Administrator. However, graphical clients provide an interactive interface by which you enter those parameters. See Section 1.2.5, "Establishing a Connection with a GUI Client." The graphical clients store connection parameters in their own files, in XML format. See Section 3.5, "Connection Management."

This section discusses the following option-related topics:

• The general syntax for specifying options

• Specifying connection parameter options

• Specifying options in an option file

Most examples in this section use the mysql program, but the general principles apply to other MySQL client command-line programs as well.

To determine the options supported by a MySQL program, invoke it with the --help option. For example, to find out how to use mysql, use this command:

 shell> mysql --help 

To determine the version of a program, use the --version option. For example, the following output from the mysql client indicates that the client is from MySQL 5.0.10:

 shell> mysql --version mysql  Ver 14.12 Distrib 5.0.10-beta, for apple-darwin8.2.0 (powerpc) 

It is not necessary to run client programs that have the same version as the server. In most cases, clients that are older or newer than the server can connect to it successfully.

1.2.1. General Command Option Syntax

Options to MySQL programs have two general forms:

• Long options consist of a word preceded by double dashes.

• Short options consist of a single letter preceded by a single dash.

In many cases, a given option has both a long and a short form. For example, to display a program's version number, you can use the long --version option or the short -V option. These two commands are equivalent:

 shell> mysql --version shell> mysql -V 

Options are case sensitive. --version is recognized by MySQL programs, but lettercase variations such as --Version or --VERSION are not. This applies to short options as well: -V and -v are both legal options, but mean different things.

Some options are followed by values. For example, when you specify the --host or -h option to indicate the host machine where the MySQL server is running, you must follow the option with the machine's hostname. For a long option, separate the option and the value by an equal sign (=). For short options, the option and the value can but need not be separated by a space. The option formats in the following three commands are equivalent. Each one specifies myhost.example.com as the host machine where the MySQL server is running:

 shell> mysql --host=myhost.example.com shell> mysql -h myhost.example.com shell> mysql -hmyhost.example.com 

In most cases, if you don't specify an option explicitly, a program uses a default value. This makes it easier to invoke MySQL client programs because you need specify only those options for which the defaults are unsuitable. For example, the default server hostname is localhost, so if the MySQL server to which you want to connect is running on the local host, you need not specify any --host or -h option.

Exceptions to these option syntax rules are noted in the following discussion wherever relevant. The most important exception is that password options have a slightly different behavior than other options.

1.2.2. Connection Parameter Options

To connect to a server using a client program, the client must know upon which host the server is running. A connection may be established locally to a server running on the same host as the client program, or remotely to a server running on a different host. To connect, you also must identify yourself to the server with a username and password.

Each MySQL client has its own program-specific options, but all command-line clients support a common set of options for making a connection to the MySQL server. This section describes the options that specify connection parameters, and how to use them if the default values aren't appropriate. The discussion lists each option's long form and short form, as well as its default value.

The primary options for connecting to the server specify the type of connection to make and identify the MySQL account that you want to use. The following tables summarize these two sets of options.

Table 1.1. Options for Establishing a Connection

Option	Meaning
--protocol	The protocol to use for the connection
--host	The host where the server is running
--port	The port number for TCP/IP connections
--shared-memory-base-name	The shared-memory name for shared-memory connections
--socket	The Unix socket filename or named-pipe name

Table 1.2. Options for MySQL User Identification

Option	Meaning
--user	The MySQL account username
--password	The MySQL account password

The --protocol option, if given, explicitly selects the communication protocol that the client program should use for connecting to the server. (In the absence of a --protocol option, the protocol used for the connection is determined implicitly based on the server hostname value and the client operating system, as discussed later.) The allowable values for the --protocol option are given in the following table.

--protocol Value	Connection Protocol	Allowable 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 server	Windows only
memory	Shared-memory connection to local server	Windows only

As shown by the table, TCP/IP is the most general protocol. It can be used for connecting to local or remote servers, and is supported on all operating systems. The other protocols can be used only for connecting to a local server running on the same host as the client program. They also are operating system-specific, and might not be enabled by default.

Named-pipe connections can be used only for connections to the local server on Windows. However, for the named-pipe connection protocol to be operative, you must use the mysqld-nt or mysqld-max-nt server, and the server must be started with the --enable-named-pipe option.

Shared-memory connections can be used only for connections to the local server on Windows. The server must be started with the --shared-memory option. Specifying this option has the additional effect that shared memory becomes the default connection protocol for local clients.

The following list describes the other connection parameters. The descriptions indicate how parameter values may affect which connection protocol to use, but note that the values have this effect only if the --protocol option is not given.

• --host=host_name or -h host_name

  This option specifies the machine where the MySQL server is running. The value can be a hostname or an IP number. The hostname localhost means the local host (that is, the computer on which you're running the client program). On Unix, localhost is treated in a special manner. On Windows, the value . (period) also means the local host and is treated in a special manner as well. For a description of this special treatment, refer to the discussion of the --socket option.

  The default host value is localhost.

• --port=port_number or -P port_number

  This option indicates the port number to which to connect on the server host. It applies only to TCP/IP connections.

  The default MySQL port number is 3306.

• --shared-memory-base-name=memory_name

  This option can be used on Windows to specify the name of shared memory to use for a shared-memory connection to a local server.

  The default shared-memory name is MYSQL (case sensitive).

• --socket=socket_name or -S socket_name

  This option's name comes from its original use for specifying a Unix domain socket file. On Unix, for a connection to the host localhost, a client connects to the server using a Unix socket file. This option specifies the pathname of that file.

  On Windows, the --socket option is used for specifying a named pipe. For Windows NT-based systems that support named pipes, a client can connect using a pipe by specifying . as the hostname. In this case, --socket specifies the name of the pipe. Pipe names aren't case sensitive.

  If this option is omitted, the default Unix socket file pathname is /tmp/mysql.sock. The default Windows pipe name is MySQL.

As mentioned earlier, if the --protocol option is not given, the connection protocol is determined implicitly based on the server hostname and the client operating system:

• On Windows, a client can establish a named-pipe connection to the local server by specifying . (period) as the hostname.

• On Unix, the hostname localhost is special for MySQL: It indicates that the client should connect to the server using a Unix socket file. In this case, any port number specified with the --port option is ignored.

• To explicitly establish a TCP/IP connection to a local server, use --protocol=tcp or else 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.

Two options provide identification information. They indicate the username and password of the account that you want to use for accessing the server. The server rejects a connection attempt unless you provide values for these parameters that correspond to a MySQL account that is listed in the server's grant tables.

• --user=user_name or -u user_name

  This option specifies the username for your MySQL account. To determine which account applies, the server uses the username value in conjunction with the name of the host from which you connect. This means that there can be different accounts with the same username, which can be used for connections from different hosts.

  On Windows, the default MySQL account name is ODBC. On Unix, client programs use your system login name as your default MySQL account username.

• --password=pass_value or -ppass_value

  This option specifies the password for your MySQL account. There is no default password. If you omit this option, your MySQL account must be set up to allow you to connect without a password.

MySQL accounts are set up using statements such as CREATE USER and GRANT, which are discussed in Chapter 34, "User Management."

Password options are special in two ways, compared to the other connection parameter options:

• You can omit the password value after the option name. This differs from the other connection parameter options, each of which requires a value after the option name. If you omit the password value, the client program prompts you interactively for a password, as shown here:

   shell> mysql -p Enter password: 

  
  

  When you see the Enter password: prompt, type in your password and press Enter. The password isn't echoed as you type, to prevent other people from seeing it.

• If you use the short form of the password option (-p) and give the password value on the command line, there must be no space between the -p and the value. That is, -ppass_val is correct, but -p pass_val is not. This differs from the short form for other connection parameter options, where a space is allowed between the option and its value. (For example, -hhost_name and -h host_name are both valid.) This exceptional requirement that there be no space between -p and the password value is a logical necessity of allowing the option parameter to be omitted.

If you have a password specified in an option file but you want to connect using an account that has no password, specify -p or --password on the command line without a password value, and then press Enter at the Enter password: prompt.

Another option that affects the connection between the client and the server is --compress (or -C). This option causes data sent between the client and the server to be compressed before transmission and uncompressed upon receipt. The result is a reduction in the number of bytes sent over the connection, which can be helpful on slow networks. The cost is additional computational overhead for both the client and server to perform compression and uncompression. --compress and -C take no value after the option name.

Here are some examples that show how to specify connection parameters:

• Connect to the server using the default hostname and username values with no password:

   shell> mysql 

  
  

• Connect to the local server via shared memory (this works only on Windows). Use the default username and no password:

   shell> mysql --protocol=memory 

  
  

• Connect to the server on the local host with a username of myname, asking mysql to prompt you for a password:

   shell> mysql --host=localhost --password --user=myname 

  
  

• Connect with the same options as the previous example, but using the corresponding short option forms:

   shell> mysql -h localhost -p -u myname 

  
  

• Connect to the server at a specific IP address, with a username of myname and password of mypass:

   shell> mysql --host=192.168.1.33 --user=myname --password=mypass 

  
  

• Connect to the server on the local host, using the default username and password and compressing client/server traffic:

   shell> mysql --host=localhost --compress 

  
  

1.2.3. Using Option Files

As an alternative to specifying options on the command line, you can place them in an option file. The standard MySQL client programs look for option files at startup time and use any appropriate options they find there. Putting an option in a file saves you time and effort because you need not specify the option on the command line each time you invoke a program.

Options in option files are organized into groups, with each group preceded by a [group-name] line that names the group. Typically, the group name is the name of the program to which the group of options applies. For example, the [mysql] and [mysqldump] groups are for options to be used by mysql and mysqldump, respectively. The special group named [client] can be used for specifying options that you want all client programs to use. A common use for the [client] group is to specify connection parameters because typically you connect to the same server no matter which client program you use.

To write an option in an option file, use the long option format that you would use on the command line, but omit the leading dashes. If an option takes a value, spaces are allowed around the = sign, something that isn't true for options specified on the command line. Here's a sample option file:

 [client] host = myhost.example.com compress [mysql] safe-updates 

In this example, the [client] group specifies the server hostname and indicates that the client/server protocol should use compression for traffic sent over the network. Options in this group apply to all standard clients. The [mysql] group applies only to the mysql program. The group shown indicates that mysql should use the --safe-updates option. (mysql uses options from both the [client] and [mysql] groups, so it would use all three options shown.)

Where an option file should be located depends on your operating system. The standard option files are as follows:

• On Windows, programs look for option files in the following order: my.ini and my.cnf in the Windows directory (for example, the C:\Windows or C:\WinNT directory), and then C:\my.ini and C:\my.cnf.

• On Unix, the file /etc/my.cnf serves as a global option file used by all users. Also, you can set up your own user-specific option file by creating a file named .my.cnf in your home directory. If both exist, the global file is read first.

Programs look for each of the standard option files and read any that exist. No error occurs if a given file is not found. MySQL programs can access options from multiple option files.

To use an option file, create it as a plain text file using an editor.

To create or modify an option file, you must have write permission for it. Client programs need only read access.

To tell a program to read a single specific option file instead of the standard option files, use the --defaults-file=file_name option as the first option on the command line. For example, to use only the file C:\my-opts for mysql and ignore the standard option files, invoke the program like this:

 shell> mysql --defaults-file=C:\my-opts 

To tell a program to read a single specific option file in addition to the standard option files, use the --defaults-extra-file=file_name option as the first option on the command line. To tell a program to ignore all option files, specify --no-defaults as the first option on the command line.

Option files can reference other files to be read for options by using !include and !includedir directives:

• A line that says !include file_name suspends processing of the current option file. The file file_name is read for additional options, and then processing of the suspended file resumes.

• A line that says !includedir dir_name is similar except that the directory dir_name is searched for files that end with a .cnf extension (.cnf and .ini on Windows). Any such files are read for options, and then processing of the suspended file resumes.

If an option is specified multiple times, either in the same option file or in multiple option files, the option value that occurs last takes precedence. Options specified on the command line take precedence over options found in option files.

1.2.4. Selecting a Default Database

For most client programs, you must specify a database so that the program knows where to find the tables that you want to use. The conventional way to do this is to name the database on the command line following any options. For example, to dump the contents of the world database to an output file named world.sql, you might run mysqldump like this:

 shell> mysqldump --password --user=user_name world > world.sql 

For the mysql client, a database name can be given on the command line, but is optional because you can explicitly indicate the database name for any table when you issue queries. For example, the following statement selects rows from the table Country in the world database:

 mysql> SELECT * FROM world.Country; 

To select or change the default database while running mysql, issue a USE db_name statement, where db_name is the name of the database you'd like to use. The following statement makes world the default database:

 mysql> USE world; 

The advantage of selecting a default database with USE is that in subsequent queries you can refer to tables in that database without having to specify the database name. For example, with world selected as the default database, the following SELECT statements are equivalent, but the second is easier to enter because the table name doesn't need to be qualified with the database name:

 mysql> SELECT * FROM world.Country; mysql> SELECT * FROM Country; 

The default database sometimes is called the current database.

1.2.5. Establishing a Connection with a GUI Client

When you use a graphical client such as MySQL Query Browser or MySQL Administrator, the parameters used to connect to the server are similar to those used for command-line clients, but you specify them differently. Instead of reading options from the command line, the graphical clients present a dialog containing fields that you fill in. These programs also have the capability of saving sets of parameters as named connection profiles so that you can select profiles by name later. Section 3.5, "Connection Management," describes the connection process for the graphical clients.