General Configuration

The configuration presented here differs considerably from what software packages normally install as default. This is in preparation for the task the server has to fulfill.

Systemd Service 

On Ubuntu systems MariaDB is started and controlled by systemd as service mariadb.service unit.

To start, stop or restart the server you can use the systemctl command:

$ systemctl start mariadb.service
$ systemctl restart mariadb.service
$ systemctl stop mariadb.service

The “reload” command is not supported on MariaDB services.

Status and error messages are written to systemd journal. To monitor the server you can use the journalctl command:

$ journalctl -u mariadb.service
$ journalctl -f -u mariadb.service

Service Customization 

The service unit file is installed in /lib/systemd/system/mariab.service. Custom options should be applied using overrides:

$ sudo edit mariab.service

# etc/systemd/system/mariadb.service.d/override.conf
[Unit]
After=sys-devices-virtual-net-wg0.device unbound.service
BindsTo=sys-devices-virtual-net-wg0.device

Before MariaDB version 10.1.8 the server was started by init-scripts or as upstart service on most UNIX systems. These scripts also applied options found in MySQL configuration files in the [mysqld_safe] or [safe_mysqld] sections. These sections are no longer relevant.

See https://mariadb.com/kb/en/library/systemd/

Main Configuration 

Note

The convention used is that variable names are listed with ‘_’ and options with ‘-‘.

Always use a ‘⁻’ (dash) when setting options in configuration files or command-line options. Like key-buffer-size = 64K

Always use ‘_’ (underscore) in SQL queries on the server. Like SHOW VARIABLES LIKE 'key_buffer_size';

The main configuration file is /etc/mysql/mariadb.cnf:

#
# MariaDB database server version 10.10.2 configuration file.
#
# For explanations see:
#  * https://roll.urown.net/server/mariadb/
#  * https://mariadb.com/kb/en/library/server-system-variables/
#


[client]
#
# Character-Set
# Default: Latin1
default-character-set = utf8mb4

#
# UNIX Sockets & TCP/IP
port = 3306
socket = /run/mysqld/mysqld.sock


[mysqld]
#
# Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL

# The default storage engine. The default storage engine must be enabled at
# server startup or the server won't start.
# Default: InnoDB
default_storage_engine = InnoDB

#
# Character-Set
# Default: Latin1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

#
# UNIX Sockets & TCP/IP
#
socket = /run/mysqld/mysqld.sock

# By default, the MariaDB server listens for TCP/IP connections on a network
# socket bound to a single address, 0.0.0.0. You can specify an alternative when
# the server starts using this option; either a host name, an IPv4 or an IPv6
# address. In Debian and Ubuntu, the default bind_address is 127.0.0.1, which
# binds the server to listen on localhost only.
# Debian-Default: 127.0.0.1
# Default: 0.0.0.0 / :: (All available IPv4 and IPv6 interfaces)
bind-address = ::
port = 3306

# If set to ON, only IP addresses are used for connections. Host names are not
# resolved. All host values in the GRANT tables must be IP addresses (or
# localhost).
# Default: OFF
skip-name-resolve = ON

# Set longer periods to avoid timeout errors
connect_timeout = 600
wait_timeout = 28800


# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /run/mysqld/mysqld.sock
nice = 0


[mysqldump]
quick
quote-names
max_allowed_packet = 16M


[mysql]
#no-auto-rehash # faster start of mysql but no tab completion


[isamchk]
key_buffer = 16M

#
# Additional settings will override anything in this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

# -*- mode: ini; tab-width: 4; indent-tabs-mode: nil -*-

Notable settings 

skip-name-resolve is set to ON, as our DNS server will get its data form its database, and we don’t want to to create a chicken and egg problem here.

bind-address is set to listening on all interfaces. As we need network access for the replication with other servers. Unfortunately you can set only one interface (usually localhost) or all of them. Since we need localhost AND external access, as not all our clients can use UNIX sockets, we need to open up all of them. This means we will have to manage the access rights of our database server users extra carefully and we need our firewall to block unwanted remote access. More on this later.

We don’t change the debian.cnf and debian-start files.

Additional files are then loaded from /etc/mysql/conf.d/ and /etc/mysql/mariadb.conf.d/.

The idea here is to remain compatible with Oracle MySQL server.

Configuration settings compatible with both products should be stored in /etc/mysql/conf.d/, while settings which only work on MariaDB should be saved in /etc/mysql/mariadb.conf.d/. The file /etc/mysql/conf.d/mariadb.cnf will load these, but only for MariaDB products compatible.

Character Sets 

Use UTF-8 instead of Latin1 as default for the server and clients.

Open /etc/mysql/my.cnf and uncomment the three UTF-8 lines.

[client]
#
# Character-Set
# Default: Latin1
default-character-set = utf8mb4

[mysqld]
#
# Character-Set
# Default: Latin1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

When done with the configuration changes, restart the MariaDB server:

$ sudo systemctl restart mariadb.service

General Configuration

Systemd Service

Service Customization

mysqld_safe

Main Configuration

Notable settings

Character Sets

Systemd Service 

Service Customization 

mysqld_safe 

Main Configuration 

Notable settings 

Character Sets 