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:
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 SyntaxOptions to MySQL programs have two general forms:
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 OptionsTo 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.
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.
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.
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:
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.
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:
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:
1.2.3. Using Option FilesAs 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:
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:
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 DatabaseFor 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 ClientWhen 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. |