Most MySQL programs understand several options that affect their operation. Options can be specified on the command line or in option files. In addition, some options can be specified by setting environment variables. Options specified on the command line take precedence over options specified any other way, and options in option files take precedence over environment variable values. Most MySQL programs understand the --help option, which provides a quick way to get online help about a program from the program itself. For example, if you're not sure how to use mysqlimport, invoke it like this for instructions: % mysqlimport --help The -? option is the same as --help, although your shell may interpret the '?' character as a filename wildcard character: % mysqlimport -? mysqlimport: No match. If that happens to you, try the following instead: % mysqlimport -\? Some options show up in help messages only under certain circumstances. For example, the --debug or SSL-related options appear only if MySQL has been compiled with debugging or SSL support, and Windows-only options, such as -pipe, are displayed only on Windows systems. Most options have both a long (full-word) form and a short (single-letter) form. The --help and -? options just described are an example of this. Long-form options that are followed by a value should be given in --name=val format, where name is the option name and val is its value. In most cases, if a short-form option is followed by a value, the option and the value can be separated by whitespace. For example, when you specify a username, -usampadm is equivalent to -usampadm. The -p (password) option is an exception; any password value must follow the -p with no intervening space. Each program description lists all options a program currently understands. If a program doesn't seem to recognize an option listed in its description, you may have an older version of the program that precedes the addition of the option. (But double-check the syntax just to make sure you simply haven't specified the option incorrectly.) The MySQL 4.0.2 "Great Divide" in Option ProcessingA significant change occurred in MySQL 4.0.2, when option handling was revised to provide a more uniform format for specifying boolean options that have on/off values. Such options now have a base form, and a standard set of related forms are recognized, as shown in the following table.
For example, many MySQL commands support a --compress option to turn on compression in the client/server protocol. Prior to MySQL 4.0.2, you either specify this option to enable compression or omit it to not use compression. That is still possible, but as of MySQL 4.0.2, --enable-compress and --compress=1 are also recognized to enable compression, and --disable-compress, --skip-compress, and --compress=0 are recognized as meaning compression should not be used. The program descriptions later in this appendix use the marker "(boolean)" to signify which options are subject to this kind of interpretation that is, options for which the prefixes and suffixes shown in the table are supported. The presence of this marker indicates that it's now preferable to use the --enable-, --disable-, and --skip- prefixes. However, when running programs from versions older than 4.0.2, you may need to use the =0 or =1 suffixes or perhaps even a different syntax. For example, older versions of mysql support a --no-named-columns option to suppress column names in query results. As of MySQL 4.0.2, the base option is --named-columns (which is also the default setting), and you suppress column headings using --disable-named-columns or --skip-named-columns. Given that the installed base of MySQL currently includes a large percentage of pre-4.0.2 installations, what this means is that it may sometimes not be clear which options to use when running a given MySQL program. (Most options are the same no matter your version of MySQL, but boolean option syntax has changed in some cases.) This problem should diminish over time as the installed base shifts toward MySQL 4 and older installations are phased out. When in doubt, invoke a program with the --help option to find out which option forms it supports. Other 4.0.2-related changes in option processing include:
Standard MySQL Program OptionsSeveral options have a standard meaning across multiple MySQL programs. Rather than writing out their meanings repeatedly in program descriptions, they are shown here once, and the "Standard Options Supported" section for each program entry indicates which of these options a program understands. (That section lists only long-format names, but programs also understand the corresponding short-format options unless otherwise specified.) The standard options are as follows:
Standard SSL OptionsThe following options are used for establishing secure connections. They are available as of MySQL 4.0.0, but only if MySQL is compiled with SSL support. See Chapter 12, "Security," for information on setting up secure connections.
Setting Program VariablesSeveral MySQL programs have variables (operating parameters) that you can set. As of MySQL 4.0.2, you can set a variable by treating its name as an option. For example, to invoke mysql with the connect_timeout variable set to 10, use the following command: % mysql --connect_timeout=10 This syntax also allows underscores in variable names to be given as dashes to make variable options look more like other options: % mysql --connect-timeout=10 Prior to MySQL 4.0.2, use the --set-variable option to set variables (or its short-form equivalent, -O). To set the connect_timeout variable, do so like this: % mysql --set-variable=connect_timeout=10 % mysql -O connect_timeout=10 --set-variable and -O are still supported in MySQL 4.0.2 and later, but are deprecated. For variables that represent buffer sizes or lengths, values are in bytes if specified as a number with no suffix or can be specified with a suffix of 'K' or 'M' to indicate kilobytes or megabytes. Suffixes are not case sensitive; 'k' and 'm' are equivalent to 'K' and 'M'. From MySQL 4.0.2 on, you can also use 'G' or 'g' to indicate gigabytes. Each program's variables are listed in the program's description in this appendix and are also displayed when you invoke the program with the --help option. Option FilesOption files were introduced in MySQL 3.22.10 and are supported by most MySQL programs. They provide a means for storing program options so that you don't have to type them on the command line each time you invoke a program. You can find example option files in the share/mysql directory under the MySQL installation directory or in the support-files directory of a source distribution. Any option specified in an option file can be overridden by specifying a different value for the option explicitly on the command line. MySQL programs that support option files look for them in several locations; however, it is not an error for an option file to be missing. Under UNIX, the following files are checked for options, in the order shown:
Under Windows, the following option files are read in order:
DATADIR represents the pathname to the data directory on your machine. (This is the pathname compiled into the server; it cannot be changed with the --datadir option.) Under Windows, DATADIR is C:\mysql\data. SYSTEMDIR represents the pathname to the Windows system directory (usually something like C:\Windows or C:\WinNT). Global option files are used by all MySQL programs that are option file-aware. An option file in a server's data directory is used only by programs from a distribution that was built with that directory as the default data directory location. User-specific files are used by programs run by that user. Windows users should be especially careful about the following issues when using option files:
Four options related to option-file processing are standard across most MySQL programs and have the following meanings; if you use any of them, it must be the first option on the command line.
If you invoke a program with the --help option, one part of the help message will show the option files that the program normally attempts to read. (The set of files read will be affected by use of the --defaults-file, --defaults-extra-file, or --no-defaults options.) Options are specified in groups. The following is an example: [client] user=sampadm password=secret [mysql] no-auto-rehash [mysqlshow] status Group names are given inside square brackets. The special group name [client] allows you to specify options that apply to all client programs. Otherwise, group names usually correspond to a specific program name. In the preceding example, [mysql] indicates the option group for the mysql client, and [mysqlshow] indicates the option group for mysqlshow. The standard MySQL client programs look at both the [client] group and the group with the same name as the client name. For example, mysql looks at the [client] and [mysql] groups, and mysqlshow looks at the [client] and [mysqlshow] groups. Any options following a group name are associated with that group. An option file can contain any number of groups, and groups listed later take precedence over groups listed earlier. If a given option is found multiple times in the groups a program looks at, the value listed last is used. Each option should be specified on a separate line. The first word on the line is the option name, which must be specified in long-name format without the leading dashes. (For example, to specify compression on the command line, you can use either -C or --compress, but in an option file, you can only use compress.) Any long-format option supported by a program can be listed in an option file. If the option requires a value, list the name and value separated by an '=' character. Consider the following command line: % mysql --compress --user=sampadm --set-variable=max_allowed_packet=16M To specify the same information in an option file using the [mysql] group, you'd do so as follows: [mysql] compress user=sampadm set-variable=max_allowed_packet=16M Observe that in an option file, set-variable is followed by a '=' character in addition to the '=' character between the variable name and its value. (On a command line, you can actually use either a '=' character or a space.) As of MySQL 4.0.2, which allows variable names to be treated directly as options, you can use: % mysql --compress --user=sampadm --max_allowed_packet=16M or: [mysql] compress user=sampadm max_allowed_packet=16M Option file lines that are empty or that begin with '#' or ';' are treated as comments and ignored. Leading spaces in option group lines are ignored. Certain escape sequences can be used in option file values to indicate special characters:
Be careful not to put options in the [client] group that really are understood only by a single client. For example, if you put the mysql-specific skip-line-numbers option in the [client] group, you will suddenly find that other client programs, such as mysqlimport, no longer work. (You'll get an error message, followed by the help message.) Move skip-line-numbers to the [mysql] group instead and you will be all right. Options understood by all or most MySQL programs that read the [client] group are as follows: character-sets-dir=charset_directory_path compress connect-timeout=seconds database=db_name debug default-character-set=charset_name disable-local-infile host=host_name init-command=query interactive-timeout=seconds local-infile password=your_pass pipe port=port_num return-found-rows socket=socket_name ssl-ca=ssl_certificate_authority file ssl-capath=ssl_certificate_authority_directory_path ssl-cert=ssl_certificate_file ssl-key=ssl_key_file timeout=seconds user=user_name Option file support was introduced in MySQL 3.22.10, but some of the allowable option values were introduced later:
Environment VariablesMySQL programs look at the values of the several environment variables to obtain option settings. Environment variables have low precedence; options specified using environment variables can be overridden by options specified in an option file or on the command line. MySQL programs check the following environment variables:
The mysql client checks the value of three additional environment variables:
|