Configuring MariaDB for systemd
Configuring MariaDB under systemd: drop-in files, timeouts, open-file and core-size limits, LimitMEMLOCK (io_uring, aio), syslog, home access, umask, data directory, socket activation, and converting
This page covers configuration for MariaDB running under systemd. For starting, stopping, and inspecting the service, see Starting MariaDB on systemd.
Configuring systemd for MariaDB Development
When developing MariaDB, you typically run mariadbd directly from a build directory rather than installing it as a service. In that case, the per-service drop-in files described below (under /etc/systemd/system/mariadb.service.d/) have no effect, because no mariadb.service is involved. Instead, the limits need to be set in systemd's system-wide manager configuration (/etc/systemd/system.conf.d/), sysctl, and PAM.
The following script applies the settings that are typically needed to run mariadbd and mysql-test-run (mtr) on a developer workstation, including support for io_uring asynchronous I/O in InnoDB, a sufficient number of open files, large core dumps, the aio limit fix for mtr, predictable core file locations, and perf / rr access. Run it as root:
#!/bin/bash
#
# Fixes needed for openSUSE (systemd) to be able to run and develop MariaDB
# without having to install it as a service
# This script needs to be run as root
set -e
# ── systemd limits ────────────────────────────────────────────────────────────
mkdir -p /etc/systemd/system.conf.d
cat > /etc/systemd/system.conf.d/memlock.conf << 'EOF'
# Needed for io_uring asynchronous IO used by InnoDB
[Manager]
DefaultLimitMEMLOCK=infinity
EOF
cat > /etc/systemd/system.conf.d/nofile.conf << 'EOF'
# Databases need a lot of open files
[Manager]
DefaultLimitNOFILE=65535
EOF
cat > /etc/systemd/system.conf.d/bigcore.conf << 'EOF'
# Allow big cores (needed to be able to debug MariaDB core files)
[Manager]
LimitCORE=infinity
EOF
# Apply systemd changes
systemctl daemon-reexec
# ── sysctl ────────────────────────────────────────────────────────────────────
mkdir -p /etc/sysctl.d
cat > /etc/sysctl.d/50-aio.conf << 'EOF'
# Fix "io_setup(1024) returned Unknown error -11 when running mtr tests"
fs.aio-max-nr=1048576
EOF
cat > /etc/sysctl.d/50-coredump.conf << 'EOF'
# Store core files in the current directory
kernel.core_pattern=core.%p
EOF
cat > /etc/sysctl.d/50-perf_event_paranoid.conf << 'EOF'
# Allow usage of perf/RR
kernel.perf_event_paranoid=1
EOF
# Apply sysctl changes
sysctl --system
# ── PAM limits ────────────────────────────────────────────────────────────────
mkdir -p /etc/security/limits.d
cat > /etc/security/limits.d/nofile.conf << 'EOF'
# Takes effect on next login
* soft nofile 65535
* hard nofile 65535
root soft nofile 65535
root hard nofile 65535
EOF
echo ""
echo "Done. PAM (number of open files) limits (/etc/security/limits.d/nofile.conf) will take effect on next login."The PAM limit change in /etc/security/limits.d/nofile.conf only takes effect on your next login. Log out and back in (or open a new shell session) before running mtr.
Configuring the Systemd Service
You can configure MariaDB's systemd service by creating a "drop-in" configuration file for the systemd service. On most systems, the systemd service's directory for "drop-in" configuration files is /etc/systemd/system/mariadb.service.d/. You can confirm the directory and see what "drop-in" configuration files are currently loaded by executing:
If you want to configure the systemd service, you can create a file with the .conf extension in that directory. The configuration options you would like to change need to be placed in an appropriate section within the file, usually [Service]. If a systemd option is a list, you may need to set the option to empty before you set the replacement values:
After any configuration change, you need to execute the following for the change to take effect:
Useful Systemd Options
Useful systemd options are listed below. If an option is equivalent to a common mariadbd-safe option, then that is also listed. Use systemctl edit mariadb.service to create the systemd option under a [Service] section header.
no option
Service startup timeout. See Configuring the Systemd Service Timeout.
no option (see MDEV-9264)
For instance, -600 to lower priority of OOM killer for mariadbd
Limit on number of open files. See Configuring the Open Files Limit.
Limit on core file size. Useful when enabling core dumps. See Configuring the Core File Size.
LimitMEMLOCK={size} or infinity
Limit on how much can be locked in memory. Useful when large-pages or memlock is used
mariadbd-safe --syslog pipes the server's output through logger -p daemon.error, so this is its exact equivalent. It tags every line as an error, notes included — see the warning in Configuring MariaDB to Write the Error Log to Syslog.
ExecStartPre=/usr/sbin/sysctl -q -w vm.drop_caches=3
or: ExecStart=/usr/bin/numactl --interleave=all /usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
prepending ExecStart=/usr/bin/numactl --interleave=all to existing ExecStart setting
There are other options and the mariadb-service-convert script attempts to convert these as accurately as possible.
Configuring the Systemd Service Timeout
MariaDB's systemd unit file has a default startup timeout of about 90 seconds on most systems. If a service startup takes longer than this default startup timeout, then systemd assumes that mariadbd has failed to startup, which causes systemd to kill the mariadbd process. To work around this, it can be changed by configuring the TimeoutStartSec option for the systemd service.
A similar problem can happen when stopping the MariaDB service. Therefore, it may also be a good idea to set TimeoutStopSec.
For example, you can reconfigure the MariaDB systemd service to have an infinite timeout by executing one of the following commands:
If you are using systemd 228 or older, then you can execute the following to set an infinite timeout:
Systemd 229 added the infinity option, so if you are using systemd 229 or later, you can execute the following to set an infinite timeout:
Note that systemd 236 added the EXTEND_TIMEOUT_USEC environment variable that allows services to extend the startup timeout during long-running processes. On systems with systemd versions that support it, MariaDB uses this feature to extend the startup timeout during certain startup processes that can run long, like when upgrading MariaDB to a newer main version or recovering after a crash with a very big transaction that needs to roll back.
Configuring the Open Files Limit
When using systemd, rather than setting the open files limit by setting the open-files-limit option for mariadbd-safe or the open_files_limit system variable, the limit can be changed by configuring the LimitNOFILE option for the MariaDB systemd service. The default is set to LimitNOFILE=16364 in mariadb.service.
For example, you can reconfigure the MariaDB systemd service to have a larger limit for open files by executing the following command – then restart the server for the changes to take effect:
In systemd 234 and later, setting LimitNOFILE=infinity actually sets the open file limit to the value of the kernel's fs.nr_open parameter. Therefore, in these systemd versions, you may have to change this parameter's value.
The value of the fs.nr_open parameter can be changed permanently by setting the value in /etc/sysctl.conf and restarting the server.
The value of the fs.nr_open parameter can be changed temporarily by executing the sysctl utility:
In systemd 233 and before, setting LimitNOFILE=infinity actually sets the open file limit to 65536. See systemd issue #6559 for more information. Therefore, in these systemd versions, it is not generally recommended to set LimitNOFILE=infinity. Instead, it is generally better to set LimitNOFILE to a very large integer. For example:
Configuring the Core File Size
When using systemd, if you would like to enable core dumps, rather than setting the core file size by setting the core-file-size option for mariadbd-safe, the limit can be changed by configuring the LimitCORE option for the MariaDB systemd service. For example, you can reconfigure the MariaDB systemd service to have an infinite size for core files by executing the following commands:
Configuring MariaDB to Write the Error Log to Syslog
When using systemd, MariaDB's error log is written to the journal, and the journal is where a traditional syslog daemon reads its data from. To send the error log to syslog, do the following:
Ensure that log_error system variable is not set, so that the server writes to
stderrrather than to a file.Set StandardOutput=journal and StandardError=journal. This is already the effective default —
StandardOutput=defaults tojournal, andStandardError=defaults toinherit, which duplicates it — but stating it explicitly documents why theSyslog*settings below apply, since they only take effect when the output goes to the journal.Set SyslogFacility=daemon to choose the syslog facility the messages are tagged with.
daemonissystemd's default.Ensure that
journaldforwards to the syslog daemon by setting ForwardToSyslog=yes injournald.conf. Some distributions enable this already, and on othersrsyslogreads the journal directly through itsimjournalmodule, in which case no forwarding is needed.
Only the forwarding setting differs from the defaults, so it is usually the only unit-side change needed — assuming log_error is unset, and unless rsyslog already reads the journal directly, in which case no change is needed at all. journald.conf is a configuration file rather than a unit, so it takes a drop-in of its own rather than a systemctl edit:
If you have multiple instances of MariaDB, then set SyslogIdentifier with a different tag for each instance, so that the messages from each can be told apart:
SyslogLevel= does not filter the error log. It sets the priority assigned to output lines that do not carry a priority prefix of their own, and MariaDB does not add one — it writes plain lines such as 2025-11-19 12:50:03 0 [Note] .... Setting SyslogLevel=err therefore tags every line as an error, notes and warnings included. This is also what mariadbd-safe --syslog has always done, since it pipes the server's output through logger -p daemon.error.
LogLevelMax= is not a way around this, because it filters on that same priority: with every line tagged alike, it keeps all of them or discards all of them, real errors included. To reduce what the server writes in the first place, use log_warnings, which defaults to 2 and accepts values from 0 to 11, with higher values meaning more verbosity. To sort messages by severity after the fact, match on the [Note], [Warning] and [ERROR] labels in the syslog daemon's own rules.
Configuring LimitMEMLOCK
Configuring Support for io_uring Asynchronous IO in InnoDB
If using --memlock, or the io_uring asynchronous IO in InnoDB in MariaDB 10.6 or above, with a Linux Kernel version < 5.12, you need to raise the LimitMEMLOCK limit.
Increasing the Limit for aio
When running the server with lots of users, the default aio value is too small. When running mysql-test-run, you could see an error like this:
The fix is to configure a higher aio value:
Create this file:
/etc/sysctl.d/50-aio.confAdd the following to that file:
Configuring Access to Home Directories
MariaDB's systemd unit file restricts access to /home, /root, and /run/user by default. This restriction can be overridden by setting the ProtectHome option to false for the MariaDB systemd service. This is done by creating a "drop-in" directory /etc/systemd/system/mariadb.service.d/ and in it a file with a .conf suffix that contains the ProtectHome=false directive.
You can reconfigure the MariaDB systemd service to allow access to /home by executing the following commands:
Configuring the umask
When using systemd, the default file permissions of mariadbd can be set by setting the UMASK and UMASK_DIR environment variables for the systemd service. For example, you can configure the MariaDB systemd service's umask by executing the following commands:
These environment variables do not set the umask. They set the default file system permissions. See MDEV-23058 for more information.
Keep in mind that configuring the umask this way only affects the permissions of files created by the mariadbd process that is managed by systemd. The permissions of files created by components that are not managed by systemd, such as mariadb-install-db, are not affected.
See Specifying Permissions for Schema (Data) Directories and Tables for more information.
Configuring the Data Directory
When doing a standard binary tarball installation, the datadir is under /usr/local/data. However, the default systemd service file makes the whole /usr directory tree write protected.
When just copying the distributed service file a tarball install does not start up, complaining for instance about this:
So when using a data directory under /usr/local that specific directory needs to be made writable for the service using the ReadWritePaths setting:
Systemd Socket Activation
MariaDB can use systemd's socket activation. This is an on-demand service for MariaDB that activates when required.
Systemd socket activation uses a mariadb.socket definition file to define a set of UNIX and TCP sockets. Systemd listens on these sockets, and when they are connected to, systemd starts the mariadb.service and hand over the socket file descriptors for MariaDB to process the connection.
MariaDB remains running at this point, and has all sockets available and process connections exactly like it did before 10.6.
When MariaDB is shut down, the systemd mariadb.socket remains active, and a new connection restarts the mariadb.service.
Using Systemd Socket Activation
To use MariaDB systemd socket activation, instead of enabling/starting mariadb.service, mariadb.socket is used instead.
So the following commands work exactly like the mariadb.service equivalents.
These files alone only contain the UNIX and TCP sockets and basic network connection information to which the server is listening for connections. @mariadb is a UNIX abstract socket, which means it doesn't appear on the filesystem. Connectors based on MariaDB Connector/C is able to connect with these by using the socket name directly, provided the higher level implementation doesn't try to test for the file's existence first. Some connectors like PHP use mysqlnd that is a pure PHP implementation and as such are only able to connect to on filesystem UNIX sockets.
With systemd activated sockets there is only a file descriptor limit on the number of listening sockets that can be created.
When to Use Systemd Socket Activation
A common use case for systemd socket activated MariaDB is when there needs to be a quick boot up time. MariaDB needs to be ready to run, but it doesn't need to be running.
The ideal use case for systemd socket activation for MariaDB is for infrastructure providers running many multiple instances of MariaDB, where each instance is dedicated for a user.
Downsides to Using Systemd Socket Activation
From the time the connection occurs, the client is going to be waiting until MariaDB has fully initialized before MariaDB can process the awaiting connection. If MariaDB was previously hard shutdown and needs to perform an extensive InnoDB rollback, then the activation time may be larger than the desired wait time of the client connection.
Configuring Systemd Socket Activation
When MariaDB is run under systemd socket activation, the usual socket, port, and backlog system variables are ignored, as these settings are contained within the systemd socket definition file.
There is no configuration required in MariaDB to use MariaDB under socket activation.
The systemd options available are from the systemd documentation, however ListenStream and BackLog would be the most common configuration options.
As MariaDB isn't creating these sockets, the sockets don't need to be created with a mysql user. The sockets MariaDB may end up listening to under systemd socket activation, it may have not had the privileges to create itself.
Changes to the default mariadb.socket can be made in the same way as services, systemctl edit mariadb.socket, or using /etc/systemd/system/mariadb.socket.d/someconfig.conf files.
Extra Port
A systemd socket can be configured as an extra_port, by using [FileDescriptorName=extra] in the .socket file.
The mariadb-extra.socket file is already packaged and ready for use.
Multi-Instance Socket Activation
mariadb@.socket is MariaDB's packaged multi-instance definition. It creates multiple UNIX sockets based on the socket file started.
Starting mariadb@bob.socket uses the mariadb@.socket definition with %I within the definition replaced with "bob".
When something connects to a socket defined there, the mariadb@bob.service are started.
Systemd Socket Activation for Hosting Service Providers
A systemd socket activation service with multi-instance can provide an on-demand per user access to a hosting service provider's dedicated database. Here, "user" refers to the customer of the hosting service provider.
End User Benefits
This provides the following benefits for the user:
Each user has their own dedicated instance with the following benefits:
The instance is free from the database contention of neighbors on MariaDB shared resources (table cache, connections, etc)
The user is free to change their own configuration of MariaDB, within the limits and permissions of the service provider.
Database service level backups, like mariadb-backup, are now directly available.
A user can install their own plugins.
The user can run a different database version to their neighbors.
If a user's neighbor triggers a fault in the server, the uder's instance isn't affected.
The database runs as their unix user in the server facilitating:
User can directly migrate their MariaDB data directory to a different provider.
The user's data is protected from other users on a kernel level.
Hosting Service Provider Benefits
In addition to providing user benefits as a sales item, the following are additional benefits for the hosting service provider compared to a monolith service:
Without passwords for the database, while still having security, support may be easier.
When a user's database isn't active, there is no resource usage, only listening file descriptors by systemd.
The socket activation transparently, with a minor startup time, starts the service as required.
When the user's database hasn't had any activity after a time, it deactivates (MDEV-25282).
Planned enhancements in InnoDB provide:
an on-demand consumption of memory (MDEV-25340 .
a proactive reduction in memory (MDEV-25341).
a memory resource pressure reduction in memory use (MDEV-24670).
The service provider can still cap the user's database memory usage in a ulimit way that a user cannot override in settings.
The service provider may choose a CPU/memory/IO based billing to the user on Linux
cgroupaccounting rather than the available comprared to the rather limited options in CREATE USER.Because a user's database shuts down when inactive, a database upgrade on the server does not take effect for the user until it passively shuts down, restarts, and then gets reactivated hence reducing user downtime..
Downsides to the Hosting Service Provider
The extra memory used by more instances. This is mitigated by the on-demand activation. The deactivation when idle, and improved InnoDB memory management.
With plenty of medium size database servers running, the Linux OOM kill has the opportunity to kill off only a small number of database servers running rather than everyones.
Configuration Items for a Per-User systemd Socket-Activitated Multi-Instance Service
From a server perspective the operation are as follows. To make the socket ready to connect and systemd listening to the socket, run these commands:
To enable this on reboot (the same way as a systemd service):
A MariaDB Template File
A global template file. Once installed as a user's $HOME/.my.cnf file, it becomes the default for many applications and for the MariaDB server itself.
Custom Configuration for the Multi-Instance Service
This extends/modifies the MariaDB multi-instance service.
The features of this extension are:
It automatically creates configuration file for user applications.
It installs the database on first service start.
auth-root-*in mariadb-install-db means that the user is their own privileged user with unix socket authentication active. This means non-that user cannot access another users service, even with access to the unix socket(s). For more information, see unix socket authentication security.If the MariaDB version was upgraded, the upgrade changes are made automatically.
LimitDataplaces a hard upper limit so the user doesn't exceed a portion of the server resources.
Custom Configuration for the Multi-Instance Socket
This extends/modifies the MariaDB socket definition to be per user.
Create sockets based on the user of the instance (%I). Permissions are only necessary in the sense that the user can connect to them. It won't matter to the server. Access control is enforced within the server, however if the user web services are run as the user, Mode=777 can be reduced. @mariadb-%I is a abstract unix socket not on the filesystem. It may help if a user is in a chroot. Not all applications can connect to abstract sockets.
The extra socket provides the user the ability to access the server when all max-connections are used:
Converting mariadbd-safe Options to Systemd Options
mariadb-service-convert is a script included in many MariaDB packages that is used by the package manager to convert mariadbd-safe options to systemd options. It reads any explicit settings in the [mariadbd-safe] option group from option files, and its output is directed to /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf. This helps to keep the configuration the same when upgrading from a version of MariaDB that does not use systemd to one that does.
Implicitly high defaults of open-files-limit may be missed by the conversion script and require explicit configuration. See Configuring the Open Files Limit.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?