Configuring MariaDB with my.cnf
Contents
The my.cnf
file allows you to configure MariaDB to run the way you want it. Most of the server system variables can be set in the my.cnf file, although usually only a few are, and the rest simply take their default values.
Depending on how you've installed MariaDB, the my.cnf may be in a number of places, or it may not exist at all.
Location in Linux, Unix, Mac
On a Linux, Unix or Mac server, MariaDB looks for the my.cnf file in the following locations:
MariaDB until 10.0.12
Location | Scope |
---|---|
/etc/my.cnf | Global |
/etc/mysql/my.cnf | Global |
SYSCONFDIR/my.cnf | Global |
$MYSQL_HOME/my.cnf | Server |
defaults-extra-file | File specified with --defaults-extra-file=path, if any |
~/.my.cnf | User |
MariaDB starting with 10.0.13
When SYSCONFDIR
is not defined (for example, in binary tarballs and DEB packages)
Location | Scope |
---|---|
/etc/my.cnf | Global |
/etc/mysql/my.cnf | Global |
$MYSQL_HOME/my.cnf | Server |
defaults-extra-file | File specified with --defaults-extra-file=path, if any |
~/.my.cnf | User |
When SYSCONFDIR
is defined (for example, in RPM packages it is /etc
)
Location | Scope |
---|---|
SYSCONFDIR/my.cnf | Global |
$MYSQL_HOME/my.cnf | Server |
defaults-extra-file | File specified with --defaults-extra-file=path, if any |
~/.my.cnf | User |
MYSQL_HOME
is the environment variable containing the path to the directory holding the server-specificmy.cnf
file. IfMYSQL_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.
- If there is a
Location in Windows
On Windows, my.ini
can be used as well as my.cnf
, and MariaDB looks in the following locations.
Location | Scope |
---|---|
Windows System Directory | Global |
Windows Directory (WINDIR) | Global |
C:\ | Global |
INSTALLDIR | Server |
INSTALLDIR\data | Server |
%MYSQL_HOME% | Global |
defaults-extra-file | File specified with --defaults-extra-file=path, if any |
Windows System Directory
is the Windows System Directory, usuallyC:\Windows\System
.Windows Directory
is the Windows directory, usuallyC:\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-specificmy.cnf
file.
my.cnf hierarchy
MariaDB will look in all of the above locations, in order, even if has already found a my.cnf
file, and it's possible for more than one my.cnf
file to exist. For example, you can have a my.cnf
file in /etc/mysql/my.cnf
with global settings for all servers, and then another my.cnf
file in the user's home directory, ~/my.cnf
, which will add further (or overwrite) settings specific only to that user.
Note that if the --defaults-file option is used, only this file will be read (client programs will also read .mylogin.cnf
when using the --defaults-file
option). If the file does not exist, an error will occur.
If no my.cnf
file is found, the default values are used for all variables. See server system variables for a full list of all server variables and their default values.
You will most likely also find a sample my.cnf file called my-default.cnf
, or, on older releases, my-huge.cnf.sh
, my-large.cnf.sh
, my-medium.cnf.sh
, and my-small.cnf.sh
(these are now very dated for modern servers). You can choose one of these appropriate for your type of installation, and copy and use as the basis for your my.cnf
file.
Configuration syntax and groups
The syntax of the MariaDB and MySQL configuration 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.
A MariaDB / MySQL program can read one or many groups. You can see for each program which configuration files and groups it reads by executing
program-name
--help --verbose
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 configuration files are 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.
You can check which command line optiond a given program is going to use by starting it with the option --print-defaults
or using the my_print_defaults
program.
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
All MariaDB programs read, in addition to the common groups, the group with the same name as the program. For example mysqldump
reads the group [mysqldump]
.
The following general groups are recognized:
Group | Description |
---|---|
client | Options read by all clients, like mysqldump |
client-server | Options read by all MariaDB clients and the MariaDB server. This option is MariaDB specific. 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 clients |
mysqld | Read by the mysqld server, both by MariaDB and MySQL |
mariadb | Read by the MariaDB mysqld server |
mysqld-VERSION | Read by a specific main version of the mysqld server, both by MariaDB and MySQL. For example mysqld-5.5 |
mariadb-VERSION | Read by a specific main version of the MariaDB mysqld server. For example mariadb-10.1 |
PROGRAMNAME | Read by the program programname. Could be for example mysqldump . |
By using the mariadb-VERSION
syntax, one can create configuration files that will work for both MariaDB and MySQL!
Example of configuration file
The following is an extract of a configuration 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
You can find some example configuration files, like my-large.cnf
, in the support-files
or /usr/share/mysql/mysql
directory, depending on your installation.