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.

MariaDB Server Options

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

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

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

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 mysqld. They must be given as the first argument on the command-line:

OptionDescription
--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

Option File Locations

MariaDB reads option files from many different directories. 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:

$program --help --verbose

For example:

sh> mysqld --help --verbose

./mysqld  Ver 10.2.1-MariaDB-valgrind-max-debug for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Starts the MariaDB database server.

Usage: ./mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-10.2 mariadb mariadb-10.2 client-server galera
....

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

Option File Locations on Linux, Unix, Mac

On a Linux, Unix or Mac server, MariaDB looks for the MariaDB option file file in the following locations:

MariaDB until 10.0.12
LocationScope
/etc/my.cnfGlobal
/etc/mysql/my.cnfGlobal
SYSCONFDIR/my.cnfGlobal
$MYSQL_HOME/my.cnfServer
defaults-extra-fileFile specified with --defaults-extra-file=path, if any
~/.my.cnfUser
MariaDB starting with 10.0.13

When SYSCONFDIR is not defined (for example, in binary tarballs and DEB packages)

LocationScope
/etc/my.cnfGlobal
/etc/mysql/my.cnfGlobal
$MYSQL_HOME/my.cnfServer
defaults-extra-fileFile specified with --defaults-extra-file=path, if any
~/.my.cnfUser

When SYSCONFDIR is defined (for example, in RPM packages it is /etc)

LocationScope
SYSCONFDIR/my.cnfGlobal
$MYSQL_HOME/my.cnfServer
defaults-extra-fileFile specified with --defaults-extra-file=path, if any
~/.my.cnfUser
  • MYSQL_HOME is the environment variable 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 mysqld_safe, 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 is set to the MariaDB data directory.
    • Else, MYSQL_HOME is set to the MariaDB base directory.

Option File Locations on Windows

On Windows, my.ini can be used as well as my.cnf, and MariaDB looks in the following locations.

LocationScope
Windows System Directory Global
Windows Directory (WINDIR) Global
C:\Global
INSTALLDIR Server
INSTALLDIR\dataServer
%MYSQL_HOME%Global
defaults-extra-fileFile specified with --defaults-extra-file=path, if any
  • Windows System Directory is the Windows System Directory, usually C:\Windows\System.
  • Windows Directory is the Windows directory, usually C:\Windows. To find its specific value on your system, use:
    C:\> echo %WINDIR%
  • INSTALLDIR is a directory, one level up from mysqld.exe, for example, C:\Program Files\MariaDB 10.1.
  • MYSQL_HOME is the environment variable containing the path to the directory holding the server-specific my.cnf file.

Option File Hierarchy

MariaDB will look in all of the above locations, in order, even if has already found a MariaDB option file, and it's possible for more than one MariaDB option file to exist. For example, you could have a MariaDB option file in /etc/mysql/my.cnf with global settings for all servers, and then you could another MariaDB option file in ~/my.cnf, i.e.the user's home directory, which will specify additional settings (or override previously specified setting) that are specific only to that user.

Note that if the --defaults-file option is used, only this option file will be read for the server. If the option file specified by --defaults-file does not exist, then an error will occur.

If no MariaDB option file is found, then the default values are used for all variables. See server system variables for a full list of all server variables and their default values.

Including Additional 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:

[mysqld]
...
!include /etc/mysql/dbserver1.cnf

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:

[mysqld]
...
!includedir /etc/my.cnf.d/

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.

MySQL 5.6 Obfuscated Authentication Credential Option File

MySQL 5.6 and above support an obfuscated authentication credential option file called .mylogin.cnf that is created with mysql_config_editor.

MariaDB does not support this.

Option File Syntax

The syntax of the MariaDB option files are:

  • Lines starting with # are comments.
  • Empty lines are ignored.
  • A group starts with the syntax [group-name]
  • You may have many instances of the same group.
  • !include filename can be used to include other configuration files.
  • !includedir directory can be used to include all configuration files in a given directory. Note that the files are read in alphabetical order.
  • Options prefixed by loose will not produce an error if they don't exist.

By convention, server variables have usually been specified with an underscore in the option files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.

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 --help --verbose

For example:

sh> mysqld --help --verbose

./mysqld  Ver 10.2.1-MariaDB-valgrind-max-debug for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Starts the MariaDB database server.

Usage: ./mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-10.2 mariadb mariadb-10.2 client-server galera
....

MariaDB programs read from the following general groups:

GroupDescription
[client] Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump.
[client-server]Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.
[client-mariadb]Options read by all MariaDB client programs.
[server]Options read by MariaDB Server.
[mysqld] Options read by mysqld, which includes both MariaDB Server and MySQL Server.
[mariadb]Options read by MariaDB Server.
[mysqld-X.Y] Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example, [mysqld-5.5]
[mariadb-X.Y] Options read by a specific version of MariaDB Server. For example, [mariadb-10.3]
[galera] Options read by a galera-capable MariaDB Server. Available in MariaDB 10.1 and above as well as MariaDB Galera Cluster 5.5 and MariaDB Galera Cluster 10.0.

X.Y in the examples above refer to the base (major.minor) version of the server. For example, MariaDB 10.3.10 would read from [mariadb-10.3]. By using the mariadb-X.Y syntax, one 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.

In addition to the common groups, a program will read from the option group with the same name as the program. For example, mysqldump reads from the option group [mysqldump].

Checking Program Options

You can check which command line options a given program is going to use by starting the program with the --print-defaults option or by using the my_print_defaults program. For example:

sh> ./client/mysqldump --print-defaults
./client/mysqldump would have been started with the following arguments:
--socket=/tmp/mysql-dbug.sock --port=3307 --quick --max_allowed_packet=16M 

sh> my_print_defaults mysqldump
--quick
--max_allowed_packet=16M

Example Option Files

Most MariaDB installations include a sample MariaDB option file called my-default.cnf. On older releases, you would have also found the following option files:

  • my-small.cnf
  • my-medium.cnf
  • my-large.cnf
  • my-huge.cnf

However, these option files are now very dated for modern servers, so they were removed in MariaDB 10.3.1.

In source distributions, the sample option files are usually found in the support-files directory, and in other distributions, the option files are usually found 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

The following is a minimal my.cnf file that you can use to test MariaDB.

[client-server]
# Uncomment these if you want to use a nonstandard connection to MariaDB
#socket=/tmp/mysql.sock
#port=3306

# This will be passed to all MariaDB clients
[client]
#password=my_password

# The MariaDB server
[mysqld]
# Directory where you want to put your data
data=/usr/local/mysql/var
# Directory for the errmsg.sys file in the language you want to use
language=/usr/local/share/mysql/english

# This is the prefix name to be used for all log, error and replication files
log-basename=mysqld

# Enable logging by default to help find problems
general-log
log-slow-queries

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.

# Example mysql config file.

[client-server]
socket=/tmp/mysql-dbug.sock
port=3307

# This will be passed to all mysql clients
[client]
password=my_password

# Here are entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
temp-pool
key_buffer_size=16M
datadir=/my/mysqldata
loose-innodb_file_per_table

[mariadb]
datadir=/my/data
default-storage-engine=aria
loose-mutex-deadlock-detector
max-connections=20

[mariadb-5.5]
language=/my/maria-5.5/sql/share/english/
socket=/tmp/mysql-dbug.sock
port=3307

[mariadb-10.1]
language=/my/maria-10.1/sql/share/english/
socket=/tmp/mysql2-dbug.sock

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
loose-abort-source-on-error

See Also

Comments

Comments loading...