Specifying Program Options
Most MySQL programs understand several options that affect their operation. Options may be specified on the command line or in option files. In addition, some options may 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.
Each program description later in this appendix lists all options that 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. To get a list of supported options, check the program's help message. 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 (command interpreter) might treat the '?' character as a filename wildcard character:
% mysqlimport -?
mysqlimport: No match.
If that happens to you, try this instead:
% mysqlimport -\?
Some options show up in help messages only under certain circumstances. For example, the SSL-related options appear only if MySQL has been compiled with SSL support, and Windows-only options such as --pipe appear 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. If a short-form option is followed by a value, in most cases the option and the value may be separated by whitespace. For example, when you specify a username, -usampadm is equivalent to -u sampadm. The -p (password) option is an exception; any password value must follow the -p with no intervening space.
Option names are case sensitive. For example, the myisamchk program supports both --help and --HELP, and the two options are slightly different.
Option values may or may not be case sensitive. Values such as usernames and passwords are case sensitive, but the value for the --protocol option is not case sensitive. For example, you can say either --protocol=tcp or --protocol=TCP to make a TCP/IP connection.
Program Option Conventions
Option handling was significantly revised in MySQL 4.0.2 to provide a more uniform format for specifying boolean options that have on/off values. Such options have a base form, and a standard set of related forms are recognized, as shown in the following table.
Option | Meaning | --name | Base option form; enable option | --enable-name | --enable- prefix; enable option | --disable-name | --disable- prefix; do not enable option | --skip-name | --skip- prefix; do not enable option | --name=1 | =1 suffix; enable option | --name=0 | =0 suffix; do not enable option |
For example, many MySQL client programs allow you to specify that you want to turn on compression in the client/server protocol. For these programs, you can specify the --compress option to enable compression, or omit it to not use compression. However, there are other ways to indicate what you want: --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 formats that explicitly disable an option are especially useful for options that are on by default. In the case of protocol compression, you can disable it simply by omitting the --compress option. But that does not work for options that are on by default. For example, in MySQL 4.1.2 and up, the --quote-names option for mysqldump is enabled by default. You cannot disable name quoting by omitting the option, but you can do so by specifying any of --skip-quote-names, --disable-quote-names, or --quote-names=0.
The program descriptions in this appendix use the marker "(boolean)" to signify which options are subject to the preceding interpretationthat is, options for which the prefixes and suffixes shown in the table are supported.
When in doubt, invoke a program with the --help option to find out which option forms it supports.
MySQL programs have other standard option-processing features:
Long options can be shortened to unambiguous prefixes, which can make it easier to specify options that have very long names. If a prefix is not long enough to be unambiguous, the program you invoke will tell you so and list those options that match the prefix:
% mysql --h
mysql: ambiguous option '--h' (help, html)
You can set program variables from the command line or in option files by treating variable names as option names. The older --set-variable=var_name=value and -O var_name=value syntaxes still are supported, but are considered deprecated. See "Setting Program Variables" for more information. A --loose- prefix is supported to help make it easier to use differing versions of a program that may not all understand quite the same set of options. For example, servers from version 4.1 and up understand the --old-passwords option, but older servers do not. If you specify the option as --loose-old-passwords, any server from 4.0.2 on will use or ignore the option according to whether or not it understands --old-passwords. With --loose, an option that is not recognized results only in a warning, not program termination with an error. The MySQL server, mysqld, supports a --maximum- prefix for specifying a maximum value to which user-modifiable variables may be set. For example, the server allows users to set their sort buffer size by changing the sort_buffer_size variable. If you want to place a maximum limit of 64MB on the value of this variable, start the server with a --maximum-sort_buffer_size=64M option.
Standard MySQL Program Options
Several options have a standard meaning and most or all MySQL programs interpret them the same way. 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 understand the corresponding short-format options as well, unless otherwise specified.
The following list describes the standard options. The default values shown are those that apply unless MySQL has been reconfigured at compile time.
--character-sets-dir=dir_name The directory where character set files are stored. --compress, -C (boolean) This option is used only by client programs. It requests the use of compression in the protocol used for communication between the client and the MySQL server, if both of them support it. --debug=debug_options, -# debug_options Turn on debugging output. This option is unavailable unless MySQL was built with debugging support enabled. The debug_options string consists of colon-separated options. A typical value is d:t:o,file_name, which enables debugging, turns on function call entry and exit tracing, and sends output to the file file_name. If you expect to do much debugging, you should examine the DBUG library user manual for a description of all the options you can use. The manual can be found in the dbug directory in MySQL source distributions. --default-character-set=charset The name of the default character set. --help, -? Print a help message and exit. For mysqld, this option by itself prints only a short usage message as of MySQL 4.1.1. Use --verbose --help for the full error message. --host=host_name, -h host_name This option is used only by client programs. It indicates the host to connect to (that is, the host where the server is running). The default value is localhost. --password[=pass_val], -p[pass_val] This option is used only by client programs. It indicates the password to use when connecting to the server. If you specify no pass_val after the option name, the program asks you to enter a password. If you do specify pass_val after -p, it must immediately follow the option letter with no space in between. In other words, the short form must be given as -ppass_val,not as -p pass_val. --pipe, -W Use a named pipe to connect to the server. This option is used only for client programs running under Windows, and only for connecting to Windows NT-based servers that support named pipes. --port=port_num, -P port_num For client programs, this is the port number to use when connecting to the server. This is used for TCP/IP connections (connections where the host is not localhost on Unix or "." on Windows). For mysqld, this option specifies the port on which to listen for TCP/IP connections. The default port number is 3306. --protocol=protocol_type This option is used only by client programs. It indicates what type of connection to make to the server. The protocol_type value can be tcp (use TCP/IP), socket (use a Unix socket file), pipe (use a Windows named pipe), or memory (use shared memory). The value is not case sensitive. Some connection types are platform specific or usable only for connecting to a local server running on the same host as the client program: Socket, pipe, and shared-memory connections can be used only for connecting to a local server. Socket connections can be used only on Unix. Named pipe and shared-memory connections can be used only on Windows. TCP/IP connections can be used on any platform and can be used to connect to local or remote servers.
The --protocol option can be used in conjunction with other options that provide information about how to connect to the server: For TCP/IP connections, you can also use the --host and --port options to specify the hostname and TCP/IP port number. For socket and named-pipe connections, you can also use the --socket option to specify the Unix socket filename on Unix or the named pipe name on Windows. For shared-memory connections, you can also use the --shared-memory-base-name option to specify the shared memory name.
--set-variable var=value, -O var=value This option allows you to set values for program operating parameters. var is the variable name, and value is the value to assign to it. The --set-variable and -O options are deprecated. See "Setting Program Variables" for more information. --shared-memory-base-name=name The name of the shared memory to use for shared-memory connections. The default name is MYSQL. The name is case sensitive. --silent, -s Run in silent mode. This doesn't necessarily mean the program is completely silent, simply that it produces less output than usual. Several programs allow this option to be specified multiple times to cause the program to become increasingly silent. (This works in option files, too.) --socket=file_name, -S file_name For client programs on Unix, this is the full pathname of the Unix socket file to use when connecting to the server with a hostname of localhost. The pathname is case sensitive if filenames are case sensitive on the MySQL host. The default Unix socket filename is /tmp/mysql.sock. For client programs on Windows, this is the name of the named pipe to use when "." is specified as the hostname. Pipe names are not case sensitive. The default pipe name is MySQL. --user=user_name, -u user_name For client programs, this is the MySQL username to use when connecting to the server. The default value is your login name under Unix and ODBC under Windows. For mysqld, this option indicates the name or user ID of the Unix account to be used for running the server. For this option to be effective, the server must be started as root so that it can change its user ID to that of the account that you specify. --verbose, -v Run in verbose mode; the program produces more output than usual. Several programs allow this option to be specified multiple times to cause the program to be increasingly verbose. (This works in option files, too.) --version, -V This option tells the program to print its version information string and exit.
Standard SSL Options
The following options are used for establishing secure connections. They are available only if MySQL is compiled with SSL support. See Chapter 12, "MySQL and Security," for information on setting up secure connections.
--ssl (boolean) Allow SSL connections. --ssl is implied by each of the other SSL options; the more common use of this option actually is as --skip-ssl to disallow SSL connections. --ssl-ca=file_name The pathname to the certificate authority file. --ssl-capath=dir_name The pathname to a directory of trusted certificates to be used for certificate verification. --ssl-cert=file_name The pathname to the certificate file. --ssl-cipher=str A string listing the SSL ciphers that may be used to encrypt traffic sent over the connection. The value should name one or more cipher types separated by commas. --ssl-key=file_name The pathname to the key file.
Setting Program Variables
Several MySQL programs have variables (operating parameters) that you can set. One way to set a variable is by treating its name as an option. For example, to invoke mysql with the connect_timeout variable set to 10, use this command:
% mysql --connect_timeout=10
This syntax also allows underscores in variable names to be given as dashes, which makes variable options look more like other options:
% mysql --connect-timeout=10
An older method for setting program variables uses the --set-variable option to set variables (or its short-form equivalent, -O). The syntax for using these options to set the connect_timeout variable looks like this:
% mysql --set-variable=connect_timeout=10
% mysql -O connect_timeout=10
--set-variable and -O 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', 'M', or 'G', to indicate kilobytes, megabytes, or gigabytes. Suffixes are not case sensitive; you can also use 'k', 'm', or 'g'.
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 Files
Most MySQL programs support option files. These 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 sample option files in the share/mysql directory under the MySQL installation directory on Unix, or in the installation directory itself on Windows. If you have a source distribution, look in the support-files directory for the sample files. They have names such as my-huge.cnf, my-large.cnf, and so forth. (The filename suffix might be .ini on Windows.)
Any option specified in an option file can be overridden by explicitly specifying a different value for the option 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. This means you normally must create option files yourself. You can create an option file in a word processor, but if you do, be sure to save it in plain text format, not in the word processor's native document format. Option files must be text files.
Under Unix, the following files are checked for options, in the order shown:
Filename | Contents |
|---|
/etc/my.cnf | Global options | DATADIR/my.cnf | Server-specific options | ~/.my.cnf | User-specific options |
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.) ~ represents the pathname to your home directory.
Under Windows, these option files are read in order:
Filename | Contents |
|---|
WINDIR\my.ini | Global options | C:\my.cnf | Global options |
WINDIR represents the pathname to the Windows 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 on Unix are read by programs run by that user.
Windows users should be especially careful about the following issues when using option files:
Windows pathnames often contain backslash ('\') characters, which are treated as escape characters by MySQL. For options that take pathname values, backslashes should be written as slashes ('/') or as doubled backslashes ('\\'). Windows Explorer likes to hide filename extensions. If you create an option file named my.cnf, Explorer may display it as just my. Should you observe that and attempt to change the name to my.cnf in Explorer, you may find that the option file no longer works. The reason is that you will actually have renamed the file from my.cnf to my.cnf.cnf!
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.
--defaults-extra-file=file_name Read options from this file in addition to the regular option files. The file is read after any global and server-specific option files and before the user-specific file. --defaults-file=file_name Read options from this file only. Normally, programs search for option files in several locations, but if --defaults-file is specified, only the named file is read. As of MySQL 4.1.2, the file is required to exist, and an error occurs if it does not. --no-defaults Suppress the use of any option files. In addition, this option causes other option-file-related options such as --defaults-file to be unrecognized. --print-defaults Print the option values that will be used if you invoke the program with no options on the command line. This shows the values that will be read from option files (and environment variables). --print-defaults is useful for verifying proper setup of an option file. It's also useful if MySQL programs seem to be using options that you never specified. You can use --print-defaults to determine if options are being read from some option file.
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 option.)
Options in option files are specified in groups. Here's 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.
Option group names are not case sensitive.
Any options following a group name are associated with that group. An option file may 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 use only 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 --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
max_allowed_packet=16M
Before MySQL 4.0.2, you cannot treat variable names directly as options, so the max_allowed_packet variable would be set differently:
% mysql --compress --user=sampadm --set-variable=max_allowed_packet=16M
In an option file, the setting would look like this:
[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.)
You can quote an option value with either single quotes or double quotes. This is useful if the value contains spaces.
Leading spaces in option file lines are ignored. Lines that are empty or that begin with '#' or ';' are treated as comments and ignored. You can also begin a comment in the middle of a line with a '#' character (but not with a ';' character).
Certain escape sequences can be used in option file values to indicate special characters:
Sequence | Meaning |
|---|
\b | Backspace | \n | Newline (linefeed) | \r | Carriage return | \s | Space | \t | Tab | \\ | Backslash |
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. (They will display an error message followed by a help message.) Move skip-line-numbers to the [mysql] group instead.
|
The my_print_defaults utility is useful for determining what options programs will read from option files. It searches option files and shows which options are found there for one or more option groups. For example, the mysql program uses options from the [client] and [mysql] option groups. To find out which options in your option files apply to mysql, invoke my_print_defaults like this:
% my_print_defaults client mysql
Similarly, the server mysqld uses options in the [mysqld] and [server] groups. To find out what options are present in option files, use this command:
% my_print_defaults mysqld server
|
|
Under Unix, your user-specific option file, ~/.my.cnf, should be owned by you and its mode should be set to 600 or 400 so that other users cannot read it. You don't want your MySQL username and password exposed to anyone other than yourself. To make your own option file private, issue either of the following commands in your home directory:
% chmod 600 .my.cnf
% chmod go-rwx .my.cnf
|
Environment Variables
MySQL 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:
MYSQL_DEBUG The options to use when debugging. This variable has no effect unless MySQL was built with debugging support enabled. Setting MYSQL_DEBUG is like using the --debug option. MYSQL_PWD The password to use when establishing connections to the MySQL server. Setting MYSQL_PWD is like using the --password option. Using the MYSQL_PWD variable to store a password constitutes a security risk because other users on your system can easily discover its value. For example, the ps utility shows environment variable settings for other users. MYSQL_TCP_PORT For client programs, this is the port number to use when establishing a TCP/IP connection to the server. For mysqld, this is the port on which to listen for TCP/IP connections. Setting MYSQL_TCP_PORT is like using the --port option. MYSQL_UNIX_PORT For client programs, this is the pathname of the Unix socket file to use when establishing socket file connections to the server running on localhost. For mysqld, this is the socket file on which to listen for local connections. Setting MYSQL_UNIX_PORT is like using the --socket option. TMPDIR The pathname of the directory in which to create temporary files. Setting this variable is like using the --tmpdir option. However, although myisamchk and mysqld understand a value containing a list of directories for --tmpdir, do not set TMPDIR that way: Other non-MySQL programs that are not likely to understand the list-of-directories convention also use TMPDIR. USER This is the MySQL username to use when connecting to the server. This variable is used only by client programs running under Windows or NetWare; setting it is like using the --user option.
The mysql client checks the value of three additional environment variables:
MYSQL_HISTFILE On Unix, the name of the file to use for storing command-line history during interactive use. The default value if this variable is not set is $HOME/.mysql_history, where $HOME is the location of your home directory. MYSQL_HOST The host to connect to when establishing a connection to the MySQL server. Setting this variable is like using the --host option. MYSQL_PS1 The string to use instead of mysql> for the primary prompt. The string can contain the special sequences listed in the section of this appendix that describes the mysql program.
|
