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.)
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.
|--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 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.
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:
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.
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.
The name of the default character set.
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.
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.
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.
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.
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.
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.)
This option tells the program to print its version information string and exit.
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.
Allow SSL connections (or disallow them if --disable-ssl is used). --ssl is implied by each of the other SSL options.
The pathname to the certificate authority file.
The pathname to a directory of trusted certificates, to be used for certificate verification.
The pathname to the certificate file.
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.
The pathname to the key file.
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 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.
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).
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.
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 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
[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.
Using my_print_defaults to Check Options
The my_print_defaults utility is useful for determining what options a given program will find in option files. It reads 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 the following command:
% my_print_defaults mysqld server
my_print_defaults is available as of MySQL 3.23.19 under UNIX and 4.0.4 under Windows.
Keep User-Specific Option Files Private
Under UNIX, make sure that each user-specific option file is owned by the proper user and that the mode is set to 600 or 400 so that other users cannot read it. You don't want MySQL username and password information exposed to anyone other than the user to whom the file applies. You can make your own option file accessible only to yourself by issuing either of the following commands in your home directory:
% chmod 600 .my.cnf % chmod go-rwx .my.cnf
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:
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.
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.
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.
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.
The pathname of the directory in which to create temporary files. Setting this variable is like using the --tmpdir option.
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:
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.
The host to connect to when establishing a connection to the server. Setting this variable is like using the --host option.
The string to use instead of mysql> for the primary prompt. The string may contain the special sequences described in the entry for mysql.