Program options specified on the command line follow these rules:

Another option that may occasionally be useful with mysql is the --execute or -e option, which can be used to pass SQL statements to the server. When this option is used, mysql executes the statements and exits. The statements must be enclosed by quotation marks. For example, you can use the following command to obtain a list of user accounts:

shell> mysql -u root -p --execute="SELECT User, Host FROM user" mysql
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>

Note that the long form (--execute) is followed by an equals sign (=).

If you wish to use quoted values within a statement, you will either need to escape the inner quotes, or use a different type of quotes within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.

In the preceding example, the name of the mysql database was passed as a separate argument. However, the same statement could have been executed using this command, which specifies no default database:

mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

Multiple SQL statements may be passed on the command line, separated by semicolons:

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------+
| VERSION()  |
+------------+
| 5.0.19-log |
+------------+
+---------------------+
| NOW()               |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

The --execute or -e option may also be used to pass commands in an analogous fashion to the ndb_mgm management client for MySQL Cluster. See Section 16.2.6, “Safe Shutdown and Restart”, for an example.

--disable-column-names
--skip-column-names
--column-names=0

--column-names
--enable-column-names
--column-names=1

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

Most MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MySQL server, MySQL provides a number of preconfigured option files.

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

On Windows, MySQL programs read startup options from the following files:

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the installation directory of MySQL. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MySQL 5.0 has been installed using the installation and configuration wizards. See Section 2.4.8.4.1.1, “The MySQL Server Configuration Wizard on Windows”.

On Unix, MySQL programs read startup options from the following files:

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. (This was DATADIR prior to MySQL version 5.0.3.)

If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:

In MySQL 5.0, use of DATADIR as the location for my.cnf is deprecated. BASEDIR is a better location.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes. For example, --quick or --host=localhost on the command line should be specified as quick or host=localhost in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

For options that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024² or 1024³. For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:

mysql> mysqladmin --count=1K --sleep=10 ping

Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences “\b”, “\t”, “\n”, “\r”, “\\”, and “\s” in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters.

Because the “\\” escape sequence represents a single backslash, you must write each “\” as “\\”. Alternatively, you can specify the value using “/” rather than “\” as the pathname separator.

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M

[mysqldump]
quick

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

Here is a typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password="my_password"

[mysql]
no-auto-rehash
connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of [mysqld-4.1], [mysqld-5.0], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.0.x version numbers:

[mysqld-5.0]
new

In shell scripts, you can use the my_print_defaults program to parse option files and see what options would be used by a given program. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.

Beginning with MySQL 5.0.4, it is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, use the following directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

There is no guarantee about the order in which the option files in the directory will be read.

Write the contents of an included option file like any other option file. That is, it should contain groups of options, each preceded by a [group] line that indicates the program to which the options apply.

While an included file is being processed, only those options in groups that the current program is looking for are used. Other groups are ignored. Suppose that a my.cnf file contains this line:

!include /home/mydir/myopt.cnf

And suppose that /home/mydir/myopt.cnf looks like this:

[mysqladmin]
force

[mysqld]
key_buffer_size=16M

If my.cnf is processed by mysqld, only the [mysqld] group in /home/mydir/myopt.cnf is used. If the file is processed by mysqladmin, only the [mysqldamin] group is used. If the file is processed by any other program, no options in /home/mydir/myopt.cnf are used.

The !includedir directive is processed similarly except that all option files in the named directory are read.

Many MySQL programs have internal variables that can be set at runtime. Program variables are set the same way as any other long option that takes a value. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024² or 1024³. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes:

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the max_buffer_length variable can be set for mysql as --max_a, but not as --max because the latter is ambiguous:

shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)

Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambigious now might become ambiguous in the future.

Many server system variables can also be set at runtime. For details, see Section 5.2.4.2, “Dynamic System Variables”.