|< Day Day Up >|
3.1 Invoking Command-Line Client Programs
MySQL client programs can be invoked from the command line; for example, from a Windows console prompt or from a Unix shell prompt. When you invoke a client program, you can specify options to control its behavior. Some options tell the client how to connect to the MySQL server. Other options tell the program what actions to perform.
This section discusses the following option-related topics:
shell> mysql --help
shell> mysql --version mysql Ver 12.22 Distrib 4.0.18, for apple-darwin7.2.0 (powerpc)
This output indicates that the mysql client is from MySQL version 4.0.18.
3.1.1 Specifying Command-Line Options
Typically, you invoke MySQL client programs with options that indicate to the program what you want it to do. This section describes the general syntax for specifying options, as well as some of the options that are common to most MySQL clients.
220.127.116.11 Command Option Syntax
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 be, but need not be, separated by a space. The following three option formats are equivalent; each one specifies myhost.example.com as the host machine where the MySQL server is running:
--host=myhost.example.com -h myhost.example.com -hmyhost.example.com
In most cases, if you don't specify an option explicitly, a program will use a default value. Default values make 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 --host or -h at all.
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.
18.104.22.168 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 must also identify yourself to the server with a username and password.
Each MySQL client has its own program-specific options, but all 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.
For command-line clients, all connection parameters are specified after the command name. The following discussion lists each option's long form and short form, as well as its default value. (You'll need to specify connection parameters for other types of client programs as well, such as the graphical MySQLCC client. Such a client might allow you to specify connection parameters on the command line, but might also provide an additional method of allowing you to indicate them, such as a dialog box.)
Two options provide identification information. These are the username and password of the account that you want to use for accessing the server. The server will reject a connection attempt unless you provide values for these parameters that correspond to an account that the server recognizes:
MySQL accounts are set up using the GRANT statement, which is discussed in the "Professional Study Guide."
Another option that affects how the connection setup occurs 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.
22.214.171.124 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 an option file saves you time: 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 [client] group can be used to specify options that you want all client programs to use. A common use for the [client] group is to specify connection parameters.
[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.
Note that if an option takes a value, spaces are allowed around the = sign, something that isn't true for options specified on the command line.
To use an option file, create it as a plain text file using an editor. Client programs can access options from multiple option files, if they exist. It isn't an error for an option file to be missing.
To tell a program to read a specific option file instead of the standard option files, use the --defaults-file option. For example, to use the file C:\my-opts for mysql on Windows, invoke the program like this:
shell> mysql --defaults-file=C:\my-opts
If you use --defaults-file, it must be the first option after the command name.
If a program finds that 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.
3.1.2 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 on the command line is optional. This is 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. For example, 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 write because the table name doesn't need to be qualified with the database name:
mysql> SELECT * FROM world.Country; mysql> SELECT * FROM Country;
|< Day Day Up >|