Specifying Program Options

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 Processing

A 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:

• Option names can be shortened to unambiguous prefixes, which can make it easier to specify options that have very long names.

• You can set program variables from the command line or in option files by treating variable names as option names. It is no longer necessary to use the --set-variable or -O options. See the "Setting Program Variables" section later in this appendix for more information.

• mysqld supports a --maximum- prefix for specifying a maximum value to which user-modifiable variables can be set. For example, the server allows users to set the 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.

• 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 later 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.

Standard MySQL Program Options

Several 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:

• --character-sets-dir=dir_name

  The directory where character set files are stored.

• --compress, -C (boolean)

  Use compression for the protocol used for communication between the client and the server if the server supports it. This option is used only by client programs. It was introduced in MySQL 3.22.3.

• --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 file dbug/dbug.c in the MySQL source distribution for a description of all the options you can use.

• --default-character-set=charset

  The name of the default character set.

• --help, -?

  Print a help message and exit.

• --host=host_name, -h host_name

  The host to connect to (that is, the host where the server is running). This option is used only by client programs.

• --password[=pass_val], -p[pass_val]

  The password to use when connecting to the server. If pass_val is not specified after the option name, the program will ask you to enter one. If pass_val is given, it must immediately follow the option name with no space in between. (This means that the short form is given as -ppass_val, not as -p pass_val.) This option is used only by client programs.

• --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, -Pport_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.

• --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 option is unnecessary as of MySQL 4.0.2 because you can set a variable directly using just its name. The old and new syntaxes are both described in the "Setting Program Variables" section later in this appendix.

• --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 socket file to use when connecting to the server with a hostname of localhost., For client programs on Windows, this is the name of the named pipe to use when '.' is specified as the hostname.

• --user=user_name, -u user_name

  For client programs, this is the MySQL username to use when connecting to the server. The default if this option is not specified is your login name under UNIX and ODBC under Windows. For mysqld, this option indicates the name 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.

• --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 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.

• --ssl

  Allow SSL connections (or disallow them if --disable-ssl is used). --ssl is implied by each of the other SSL options.

• --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 can 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. 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 Files

Option 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:

• 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 likes to hide filename extensions. If you create an option file named my.cnf, Windows may display it as just my. Should you observe that and attempt to change the name to my.cnf in Windows 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!

• Option files must be plain text files. You can create an option file in a word processor, but if you do, be sure to save it in text format, not in the word processor's native document format.

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. It is read after the global and server-specific option files and before the user-specific file. This option was introduced in MySQL 3.23.26, except for safe_mysqld (3.23.27) and mysql_install_db (3.23.29).

• --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. This option was introduced in MySQL 3.22.23.

• --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 picked up 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 picked up 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 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:

• debug was introduced in MySQL 3.22.11.

• return-found-rows was introduced in MySQL 3.22.15.

• character-sets-dir and default-character-set were introduced in MySQL 3.23.14.

• interactive-timeout was introduced in MySQL 3.23.28.

• connect-timeout was introduced in MySQL 3.23.49. connect-timeout is a synonym for timeout, which now is deprecated.

• disable-local-infile and local-infile were introduced in MySQL 3.23.49.

• ssl-ca, ssl-capath, ssl-cert, and ssl-key were introduced in MySQL 4.0.0.

% my_print_defaults client mysql 

% my_print_defaults mysqld server 

% 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 will show environment variable settings for other users.

• MYSQL_TCP_PORT

  For clients, 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 clients, this is the pathname of the socket file to use when establishing UNIX domain socket connections to the server running on localhost. For mysqld, this is the socket 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.

• USER

  This is the MySQL username to use when connecting to the server. This variable is used only by client programs running under Windows; setting it is like using the --user option.

The mysql client checks the value of three additional environment variables:

• MYSQL_HISTFILE

  The name of the file to use for storing command-line history. 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 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 may contain the special sequences described in the entry for mysql.