Specifying Program Options

Most MySQL programs understand several options that affect how the program operates. 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.

% mysqlimport --help
						

% mysqlimport -?
mysqlimport: No match.

% mysqlimport -?
						

Most options have both a short (single-letter) form and a long (full-word) form. The -? and --help options just shown are an example of this. 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, -upaul is equivalent to -u paul. 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.)

Standard MySQL Program Options

Several options have a standard meaning across multiple MySQL programs. Rather than listing these in the description of each program that understands them, they are shown here once, and the "Standard Options Supported" section in each program description 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 standard options are:

• -C, --compress
  

  Enables 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_options, --debug=debug_options
  								

  Turns on debugging output. This option has no effect 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.

• --defaults-file=path_name
  								

  Specifies the pathname to an option file. Normally, programs search for option files in several locations, but if --defaults-file is specified, only the named file is read. If you use this option, it must be the first one on the command line. This option was introduced in MySQL 3.22.23.

• -?, --
  

  Tells the program to print a help message and exit.

• -h host_name, --host=host_name
  								

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

• --no-defaults
  

  Suppresses the use of any option files. If you use this option, it must be the first one on the command line.

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

  Specifies the password to use when connecting to the server. If password is not specified after the option name, the program will ask you to enter one. If password is given, it must immediately follow the option name with no space in between. This option is used only by client programs.

• -W, --pipe
  

  Uses a named pipe to connect to the server. This option is used only for client programs running under Windows.

• -P port_num, --port=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). For mysqld, this option specifies the port on which to listen for TCP/IP connections.

• --print-defaults
  

  Prints 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 use this option, it must be the first one on the command line.

• -O var=value, --set-variable var=value
  								

  Several programs have variables (operating parameters) that you can set. The --set-variable option allows you to set these variables. var is the variable name, and value is the value to assign to the variable. Values are in bytes for variables that represent buffer sizes or lengths. Values may be specified as an exact number, or as a number with the suffix 'K' or 'M' to indicate kilobytes or megabytes. Suffixes are not case sensitive; 'k' and 'm' are equivalent to 'K' and 'M'.

  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.

• -s, --silent
  

  Specifies 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).

• -S path_name, --socket=path_name
  								

  For client programs, this is the full pathname of the socket file to use when connecting to the server. A socket file is used for connections to localhost.

• -u user_name, --user=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. (The server must be started as root for this option to be effective.)

• -v, --verbose
  

  Specify 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.)

• -V, --version
  

  Tells the program to print its version information string and exit.

Option Files

Option files were introduced in MySQL 3.22. They allow you to store MySQL program options so that you don't have to type them on the command line each time you invoke a program. Option files are read by isamchk, myisamchk, myisampack, mysql, mysqladmin, mysqld, mysqldump, mysqlimport, mysql.server, and pack_isam. You can find an example option file, my-example.cnf, 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 may be overridden by specifying a different value for the option explicitly on the command line.

MySQL programs consult several files looking for options; 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 given:

Under Windows, these files are read:

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:mysqldata. SYSTEMDIR represents the pathname to the Windows system directory (usually something like C:Windows or C:WinNT). Although the Windows option file names are specified using '' as the pathname component separator, pathnames specified within the files should be written using '/' as the pathname separator. If you want to use '' as the separator, use '\' because '' is treated as an escape character.

Global option files are used by all programs that are option file-aware. A file in a server's data directory is used only by the corresponding server. User-specific files are used by programs run by that user.

Options are specified in groups. Here's an example:

[client]
user=paul
password=secret

[mysql]
no-auto-rehash

[mysqlshow]
status

Group names are given inside square brackets and usually correspond to a program name. In the preceding example, [mysql] indicates the option group for the mysql client and [mysqlshow] indicates the option group for mysqlshow. The special group name [client] allows you to specify options that apply to all client programs. 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 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 only use compress.) Any long-format option supported by a program may 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=paul --set-variable max_allowed_packet=24M

To specify the same information in an option file using the [mysql] group, you'd do so as follows:

[mysql]
compress
user=paul
set-variable=max_allowed_packet=24M

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.

Option file lines that are empty or that begin with '#' or ';' are treated as comments and ignored.

Certain escape sequences may be used in option 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.

The options understood by all MySQL programs that read the [client] group are compress, debug, help, host, password, pipe, port, select, user, and version. (It doesn't make much sense to put help or version in an option file, of course.)

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

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