Configuring MariaDB with Option Files

You can configure MariaDB to run the way you want by configuring the server with MariaDB's option files. The default MariaDB option file is called my.cnf on Unix-like operating systems and my.ini on Windows. Depending on how you've installed MariaDB, the default option file may be in a number of places, or it may not exist at all.

Global Options Related to Option Files

The following options relate to how MariaDB handles option files. These options can be used with most of MariaDB's command-line tools, not just mariadbd. They must be given as the first argument on the command-line:

Default Option File Locations

MariaDB reads option files from many different directories by default. See the sections below to find out which directories are checked for which system.

For an exact list of option files read on your system by a specific program, you can execute a command like this ($program stands for any MariaDB program, including the Server):

For example:

The option files are each scanned once, in the order given by --help --verbose. The effect of the configuration options is the same as if they would have been given as command-line options in the order they are found.

Default Option File Locations on Linux, Unix, Mac

On Linux, Unix, or Mac OS X, the default option file is called my.cnf. MariaDB looks for the MariaDB option file in the locations and orders listed below.

The locations are dependent on whether the DEFAULT_SYSCONFDIR option was defined when MariaDB was built. This option is usually defined as /etc when building , but it is usually not defined when building or .

• If the DEFAULT_SYSCONFDIR cmake option is undefined, MariaDB looks for the MariaDB option file in the following locations, and in the following order:

• If the DEFAULT_SYSCONFDIR cmake option is defined, MariaDB looks for the MariaDB option file in the following locations in the following order:

• MARIADB_HOME or MYSQL_HOME is the containing the path to the directory holding the server-specific my.cnf file. If MYSQL_HOME is not set, and the server is started with , MYSQL_HOME is set as follows:

  • If there is a my.cnf file in the MariaDB data directory, but not in the MariaDB base directory, MYSQL_HOME

Default Option File Locations on Windows

On Windows, the option file can be called either my.ini or my.cnf. MariaDB looks for option files in the following locations, and in the following order:

• The System Windows Directory is the directory returned by the function. The value is usually C:\Windows. To find its specific value on your system, open and execute:

• The Windows Directory is the directory returned by the GetWindowsDirectory function. The value may be a private Windows Directory for the application, or it may be the same as the System Windows Directory returned by the GetSystemWindowsDirectory function.

• INSTALLDIR is the parent directory of the directory where mysqld.exe is located. For example, if mysqld.exe

Default Option File Hierarchy

MariaDB looks in all of the above locations, in order, even if it has already found an option file, and it's possible for more than one option file to exist. For example, you could have an option file in /etc/my.cnf with global settings for all servers, and another option file in ~/.my.cnf (that is, your user account's home directory) which specifies additional settings (or override previously specified settings) that are specific only to that user.

Option files are usually optional. However, if the option is set, but the file does not exist, MariaDB raises an error. If the --defaults-file option is set, MariaDB only reads the option file referred to by this option.

If an option or system variable is not explicitly set, then it will be set to its default value. See for a full list of all server system variables and their default values.

If is set, there are various other log file naming options that, if set, should be placed later in the configuration file hierarchy. Later settings override earlier settings, so log-basename overrides any earlier log file name settings. See for details.

Custom Option File Locations

MariaDB can be configured to read options from custom options files with the following command-line arguments. These command-line arguments can be used with most of MariaDB's command-line tools, not just . They must be given as the first argument on the command line:

Option File Syntax

The syntax of MariaDB option files is as follows:

• Lines starting with # are comments.

• Empty lines are ignored.

• Option groups use the syntax [group-name]. See the section below for more information on available option groups.

Option Groups

A MariaDB program can read options from one or many option groups. For an exact list of option groups read on your system by a specific program, you can execute ($program is a placeholder for any MariaDB program):

For example:

Server Option Groups

MariaDB programs reads server options from the following server option groups:

X.Y in the examples above refer to the base (major.minor) version of the server. For example, MariaDB 11.4 would read from [mariadb-11.4]. By using the mariadb-X.Y syntax, you can create option files that have MariaDB-only options in the MariaDB-specific option groups. That would allow the option file to work for both MariaDB and MySQL.

Client Option Groups

MariaDB programs reads client options from the following option groups:

Tool-Specific Option Groups

Many MariaDB tools reads options from their own option groups as well:

Custom Option Group Suffixes

MariaDB can be configured to read options from option groups with a custom suffix by providing the following command-line argument. This command-line argument can be used with most of MariaDB's command-line tools, not just . It must be given as the first argument on the command line:

The default group suffix can also be specified via the MYSQL_GROUP_SUFFIX .

Example: To create a custom prompt for the mariadb command-line client via a custom group, add this to the option file (for instance, /etc/my.cnf):

Then, launch mariadb like this:

Including Option Files

It is possible to include additional option files from another option file. For example, to include /etc/mysql/dbserver1.cnf, an option file could contain:

Including Option File Directories

It is also possible to include all option files in a directory from another option file. For example, to include all option files in /etc/my.cnf.d/, an option file could contain:

The option files within the directory are read in alphabetical order.

All option file names must end in .cnf on Unix-like operating systems. On Windows, all option file names must end in .cnf or .ini.

Checking Program Options

You can check which options a given program is going to use by using the command-line argument:

This command-line argument can be used with most of MariaDB's command-line tools, not just . It must be given as the first argument on the command-line:

You can also check which options a given program is going to use by using the utility, and providing the names of the option groups that the program reads:

The my_print_defaults utility's --mariadbd command-line option provides a shortcut to refer to all of the :

Obfuscated Authentication Credential Option File

MySQL supports an obfuscated authentication credential option file called .mylogin.cnf that is created with .

MariaDB does not support this. The passwords in MySQL's .mylogin.cnf are only obfuscated, rather than encrypted, so the feature does not really add much from a security perspective. It is more likely to give users a false sense of security, rather than to seriously protect them.

Option Prefixes

MariaDB supports certain prefixes that can be used with options. The supported option prefixes are:

For example:

Options

Dashes (-) and underscores (_) in option names are interchangeable.

If an option is not explicitly set, then the server or client will simply use the default value for that option.

MariaDB Server Options

MariaDB Server options can be set in .

For a list of options that can be set for MariaDB Server, see the list of options available for .

Most of the can also be set in MariaDB's option file.

MariaDB Client Options

MariaDB client options can be set in .

See the specific page for each to determine what options are available for that program.

Example Option Files

Most MariaDB installations include a sample MariaDB option file called my-default.cnf.

In source distributions, the sample option files are usually found in the support-files directory, and in other distributions, in the share/mysql directory that is relative to the MariaDB base installation directory.

You can copy one of these sample MariaDB option files and use it as the basis for building your server's primary MariaDB option file.

Example Minimal Option File

Minimal my.cnf file that you can use to test MariaDB:

Example Hybrid Option File

The following is an extract of an option file that one can use if one wants to work with both MySQL and MariaDB.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}