This may seem like a question that should have only one answer, but in an effort to be flexible, MariaDB looks for the my.cnf or the my.ini configuration file in several different locations.

As mentioned previously, on Windows, the MariaDB configuration file is named my.ini by default and is found in the data directory (see the section The MariaDB filesystem layout on Windows to learn where the data directory is located on Windows). The file can also be named my.cnf, just as it is in Linux, and MariaDB will also look in the following additional locations for it:

C:WINDOWSmy.ini
C:WINDOWSmy.cnf
C:my.ini
C:my.cnf

On Linux, the MariaDB configuration file is always named my.cnf and is almost always found at one of the following two locations:

/etc/my.cnf
/etc/mysql/my.cnf

MariaDB will look for the file at both locations, but if the files exist at both locations, the options in the file that MariaDB reads last will override the options that it read in the first file. So, to avoid confusion, we should only have one or the other and if we discover we have both for some reason, we should combine them into one file.

Comment lines are lines that begin with # (the hash character) or ; (a semicolon). Comments are ignored by MariaDB. They often contain useful information and are a great place to keep notes when we make changes to the file. Comments can also start in the middle of the line. Just think of anything from the initial comment character to the end of a line as a comment. Here are some examples:

# Here is a comment
; This is also a comment
port = 3306 # This is a comment about the 'port' option

Groups are sections or parts in a configuration file. A typical MariaDB installation is composed of a server program, one or more client programs, and several utility programs. Each of these have their own individual configuration options and they can all be set in our my.cnf or my.ini file. Even the individual series of MariaDB have their own group identifiers (these are useful if we are testing a development version and want to enable a new feature without affecting older servers that use the same configuration file).

A group begins with a name enclosed in square brackets ([]) on a line, by itself. The group continues to the end of the file or to the beginning of the next group, whichever comes first. The following is an example of the often used mysqld group:

[mysqld]
# Configuration options for the mysqld program go here

Incidentally, mysqld is the name of the MariaDB server program binary. The group is named after the binary's file name. In addition to [mysqld], other common groups include the following:

[server]
  # the same as [mysqld]
[mysql]
  # configuration options for the mysql command-line client
[client]
  # the same as [mysql]
[client-server]
  # configuration options for both clients and the server
[mysqladmin]
  # configuration options for the mysqladmin program
[mysqlcheck]
  # configuration options for the mysqlcheck utility
[mariadb-10.1]
  # configuration options just for MariaDB 10.1 series servers

There are many other possible groups, but these are enough to get the idea. We just use the ones we want and can ignore the others.

In each group, we set options. There are two types, those which don't require a corresponding value and those that do.

Configuration options either take a value or not. Those that do not need a value appear on a line by themselves with no equals sign (=). They are used for options that are either on or off, so there is no need for arguments. If it exists in the configuration file (and isn't commented out), the feature is considered on. If it doesn't exist (or it is commented out), the feature is set to whatever the default is (ON or OFF). An example would be as follows:

no-auto-rehash

To turn OFF a feature that is ON by default, just add =OFF to it as follows:

no-auto-rehash=OFF

We can also be more explicit about turning a feature on by appending =ON to an option. It's not necessary, though.

As mentioned in the previous section, some configuration options require a value of some sort to be set. For example, the default [client] section in the Ubuntu version of the MariaDB my.cnf file contains the following two options:

port = 3306
socket = /var/run/mysqld/mysqld.sock

Setting options such as port or socket, or any other settings that require a value, without giving a value, will cause an error and MariaDB may refuse to start.

Option names are not case sensitive and we can vary the number of spaces around the equals (=) sign. We can also choose to use dashes (-) or underscores (_) in the names. For example, the following two options are the same:

max_allowed_packet = 1M
MAX-Allowed-Packet    =    1M

One exception to this is with options that have values (described in the Options that require values section). If the value is a file or location on a case-sensitive filesystem, like those used on Linux, that value will be case sensitive. The option name itself is not case-sensitive, but the value is. For example, the first two of the following three examples work the same but the third one does not (and on Linux, it will almost assuredly not work):

socket = /var/run/mysqld/mysqld.sock
SOCKET = /var/run/mysqld/mysqld.sock
socket = /VAR/run/MySQLd/mysqld.sock

Even though MariaDB will accept UPPER or mIxEd case, to keep our my.cnf or my.ini file readable, it is best to keep option names lowercase.

Each individual program and utility included with MariaDB has its own set of configuration options. Run one from the command line with --help and we'll get a list of all the options that the program has and what they are currently set to.

Run the command with --print-defaults and we'll see the values that we've set.

For example, here's the output of mysql --print-defaults on my local machine:

shell> mysql --print-defaults

mysql would have been started with the following arguments:

--port=3306 --socket=/var/run/mysqld/mysqld.sock

Another method to view what the variables are set to is to use the SHOW VARIABLES and SHOW STATUS commands when connected to MariaDB using the mysql client program. More information on these two commands is available in the MariaDB Knowledge Base at the following links:

If we want to see all the default values for a command (what they would be if we didn't have a config file), use --no-defaults --help –verbose, as follows:

shell> mysqld --no-defaults --help –verbose

The list that gets printed by the preceding command is quite long, so we won't show it here. And it shows more information than just the default values of the options. The actual variables and options we're interested in are in a table towards the end of the output that begins with the following:

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}

Putting all of the above information into practice, I've created a fairly generic and heavily commented example my.cnf file. It is available in the code bundle given away with this book.

There isn't space here to go into detail on the many options available for configuring MariaDB. If you want to learn more, a good place to start is in the Optimization and Tuning section of the MariaDB Knowledge Base, which is available from:

https://mariadb.com/kb/en/optimization-and-tuning/