Using Option Files

Most MariaDB 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 MariaDB server, MariaDB 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, MariaDB programs read startup options from the following files, in the specified order (top items are used first).

File Name	Purpose
`WINDIR\my.ini`, `WINDIR\my.cnf`	Global options
`C:\my.ini`, `C:\my.cnf`	Global options
`INSTALLDIR\my.ini`, `INSTALLDIR\my.cnf`	Global options
`defaults-extra-file`	The file specified with `--defaults-extra-file=path`, if any

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 MariaDB installation directory. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.6 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MariaDB 5.6 has been installed using the installation and configuration wizards. See , "Starting the MariaDB Server Instance Configuration Wizard".

On Unix, Linux and Mac OS X, MariaDB programs read startup options from the following files, in the specified order (top items are used first).

File Name	Purpose
`/etc/my.cnf`	Global options
`/etc/mysql/my.cnf`	Global options
`SYSCONFDIR/my.cnf`	Global options
`$MYSQL_HOME/my.cnf`	Server-specific options
`defaults-extra-file`	The file specified with `--defaults-extra-file=path`, if any
`~/.my.cnf`	User-specific options

~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MariaDB was built. By default, this is the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. 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:

Let BASEDIR and DATADIR represent the path names of the MariaDB base directory and data directory, respectively.
If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MariaDB 5.6, use of DATADIR as the location for my.cnf is deprecated.

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

On Unix platforms, MariaDB ignores configuration files that are world-writable. This is intentional as a security measure.

Any long option that may be given on the command line when running a MariaDB 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 (see , "Using Options on the Command Line"). However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines 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. Nonempty lines can take any of the following forms:

#comment, ;comment
Comment lines start with "#" or ";". A "#" comment can start in the middle of a line as well.
[group]
group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given.
opt_name
This is equivalent to --opt_name on the command line.
opt_name=value
This is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the "=" character, something that is not true on the command line. You can optionally enclose the value within single quotation marks or double quotation marks, which is useful if the value contains a "#" comment character.

Leading and trailing spaces are automatically deleted from option names and values.

You can 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. The escaping rules in option files are:

If a backslash is followed by a valid escape sequence character, the sequence is converted to the character represented by the sequence. For example, "\s" is converted to a space.
If a backslash is not followed by a valid escape sequence character, it remains unchanged. For example, "\S" is retained as is.

The preceding rules mean that a literal backslash can be given as "\\", or as "\" if it is not followed by a valid escape sequence character.

The rules for escape sequences in option files differ slightly from the rules for escape sequences in string literals in SQL statements. In the latter context, if "x" is not a value escape sequence character, "\x" becomes "x" rather than "\x". See , "String Literals".

The escaping rules for option file values are especially pertinent for Windows path names, which use "\" as a path name separator. A separator in a Windows path name must be written as "\\" if it is followed by an escape sequence character. It can be written as "\\" or "\" if it is not. Alternatively, "/" may be used in Windows path names and will be treated as "\". Suppose that you want to specify a base directory of C:\Program Files\MySQL\MySQL Server 5.6 in an option file. This can be done several ways. Some examples:

basedir='C:\Program Files\MySQL\MySQL Server 5.6'
basedir='C:\\Program Files\\MySQL\\MySQL Server 5.6'
basedir='C:/Program Files/MySQL/MySQL Server 5.6'
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.6

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 enables 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 MariaDB 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 MariaDB release series only, you can do this by using groups with names of [mysqld-5.5], [mysqld-5.6], and so forth. The following group indicates that the --new option should be used only by MariaDB servers with 5.6.x version numbers:

[mysqld-5.6]
new

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

Currently, any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

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 [mysqladmin] 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.

Using Option Files

Retornar