Configuring MariaDB

The MariaDB behavior is affected by a number of server variables. Before discussing the important variables, it is necessary to understand how to set such variables.

The values of the server variables are set in the following ways:

• For each variable, there exists a default value.
• One or more configuration files can be read by MariaDB at startup. Each file sets some variables, overriding the default values.
• mysqld can be called with some options. In this case, each option overrides the value of a server variable.
• At runtime, it is still possible to change the values of some variables. These variables are called dynamic variables. The values of static variables cannot be changed at runtime; thus, modifying such variables requires a server restart.
• Some variables exist in the global and session context. This means that every session can have a specific value for those variables. In this case, the global value serves as the default: when a new session is created, its value is copied from the global value. The session value can be changed later. Changing the global value does not affect existing connections but only new ones.

Configuration files

As explained earlier, MariaDB can read more than one configuration system. On startup, MariaDB accesses some paths in a predetermined order, seeking configuration files. Each file overrides the settings that were previously read from other files.

The paths in which MariaDB looks for configuration files are system-dependent. On Linux, it reads the following paths, in the given order:

• /etc
• /etc/mysql
• SYSCONFDIR
• $MYSQL_HOME
• The file indicated with the --defaults-extra-file option
• ~/

On Windows, the following paths are read:

• %PROGRAMDATA%\MariaDB\MariaDB Server 10.0
• %WINDIR%
• C:\
• MariaDB installation directory
• The file indicated with the --defaults-extra-file option

On Linux, configuration files are expected to be called my.cnf. On Windows, they can be called my.ini or my.cnf.

Note that installers create a default configuration file with values that are usually acceptable if we want to develop applications with MariaDB on our local machine only. On Linux, this file is usually located in /etc.

For each file, MariaDB reads the following option groups in the same order (replace X.X with the proper version number, for example 10.0):

• [mysqld]
• [server]
• [mysqld-X.X]
• [mariadb]
• [mariadb-X.X]
• [client-server]

The client-server group is very useful, because it is read by both the client and the server. This allows specifying the parameters that need to be used by both (for example, the port number or the path for the socket file) only once.

The following minimal example shows the syntax to be used in the configuration files:

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

Passing options to mysqld

As mentioned before, it is possible to pass options to mysqld at startup. These options override the settings in the configuration files. On Linux, the same options can also be passed to mysqld_safe, which will pass them to mysqld.

The names of the command-line options are very similar to the configuration files options, except that they generally begin with a double dash (-), and use dashes as word separators instead of underscores (_).

For example, the innodb_buffer_pool_size server variable determines the size of the main MariaDB cache in bytes. It can be set in the configuration files with this syntax:

innodb_buffer_pool_size = 134217728

This setting can be overridden with a startup option using the following syntax:

mysqld --innodb-buffer-pool-size=134217728

Or by using mysqld_safe:

mysqld_safe --innodb-buffer-pool-size=134217728

Setting server variables at runtime

A server variable is a setting whose value somehow affects the behavior of MariaDB. If a variable is only global, its value applies to all the connections or to some internal mechanism of the server. If a corresponding session variable exists, each session value affects a particular connection. The global value is still important, because it represents the initial value for the session variable. However, modifying a global variable affects only new connections; the corresponding session variables will remain untouched for the existing connections.

Also note that we can only modify dynamic variables. We can read the value of static variables, but trying to modify them will cause an error.

Server variables can be read and modified using the SELECT and SET SQL statements.

If we need to read or modify a session variable, we can use the following syntax:

MariaDB [(none)]> SET @@session.sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @@session.sql_mode;
+---------------------+
| @@session.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

Similarly, to read or modify a global variable, we will use the following syntax:

MariaDB [(none)]> SET @@global.sql_mode = 'no_zero_date';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| NO_ZERO_DATE      |
+-------------------+
1 row in set (0.00 sec)

Some global variables, but not all, can be set to their default value by specifying DEFAULT instead of a value. It is possible to set a session variable to the value of the corresponding global variable with the same syntax.