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.
On Ubuntu systems MariaDB is started and controlled by systemd as service
To start, stop or restart the server you can use the
$ 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 -u mariadb.service $ journalctl -f -u mariadb.service
The service unit file is installed in
Custom options should be applied by copying the original unit file to
$ sudo cp /lib/systemd/system/mariab.service /etc/systemd/system/ $ sudo nano /etc/systemd/system/mariab.service $ systemctl dameon-reload $ systemctl restart mariadb.service
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
sections. These sections are no longer relevant.
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.
SHOW VARIABLES LIKE 'key_buffer_size';
The main configuration file is
# # MariaDB database server version 10.2.11 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 -*-
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
Additional files are then loaded from
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
/etc/mysql/mariadb.conf.d/. The file
/etc/mysql/conf.d/mariadb.cnf will load these, but only for MariaDB
Use UTF-8 instead of Latin1 as default for the server and clients.
/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