MariaDB Database per user, on demand (aka systemd multi-instance socket activated) (LCA2022 Miniconf talk)

gistfile1.txt

MariaDB Database per user, on demand (aka systemd multi-instance socket activated)

This is to provide attendee's the notes needed related to the talk without
needing to transcribe from script or video:


Attendee notes:


My.cnf template to be copied into user home directories:
/etc/my.cnf.templ:


[client-server]
user=USER
port=PORT
socket=/home/USER/mariadb.sock

[mariadb]
datadir=/home/USER/mariadb-datadir

[mariadb-10.8]
# At time of writing this is MDEV-25282
# which hopes to be in 10.8, but maybe 10.9
max-idle-execution=10


Configuration of MariaDB multi-instance (from 10.6) for a per
user data directory:

systemctl edit mariadb@.service
/etc/systemd/system/mariadb@.service.d/override.conf:

[Service]
User=%I
Group=%I
ProtectHome=false

PermissionsStartOnly=false

Environment=MYSQLD_MULTI_INSTANCE="--defaults-file=/home/%I/.my.cnf

ExecStartPre=
ExecStartPre=/bin/bash -c "[ -f /home/%I/.my.cnf ] || sed -e \"s/USER/%I/g\" -e \"s/PORT/$(( $(id -u %I) + 3000 ))/g\" /etc/my.cnf.templ > /home/%I/.my.cnf"
ExecStartPre=mkdir -p /home/%I/mariadb-datadir
ExecStartPre=/usr/local/mysql/scripts/mariadb-install-db $MYSQLD_MULTI_INSTANCE --rpm \
   --auth-root-authentication-method=socket --auth-root-socket-user=%I --skip-test-db

## Restart=always was there for multi-instance, but was removed once socket activation was enabled.
#Restart=always


Note: /usr/local in the above scripts may not correspond to your distribution installed location of scripts.


Configuration of systemd socket activiation (from MariaDB-10.6):

Note about SELinux permissions that I didn't mention in talk. By default systemd
(global, rather than per user) under init_t context cannot write socket
files to a users home directory. The SELinux rule in the config below needs
to be made into a module and loaded.

systemctl edit mariadb-extra@.service
/etc/systemd/system/mariadb@.socket.d/override.conf:

# Requires SELinux permission:
# allow init_t user_home_t:sock_file { create setattr write };

[Socket]
SocketUser=%I
SocketGroup=%I
SocketMode=770

ProtectHome=false
ListenStream=
ListenStream=@mariadb-%I
ListenStream=/home/%I/mariadb.sock


Configuration of systemd socket activation extra socket (from MariaDB-10.6):

systemctl edit mariadb-extra@.socket
/etc/systemd/system/mariadb-extra@.socket.d/override.conf

# Requires SELinux permission:
# allow init_t user_home_t:sock_file { create setattr write };

[Socket]
SocketUser=%I
SocketGroup=%I
SocketMode=770

ProtectHome=false
ListenStream=
ListenStream=@mariadb-extra-%I
ListenStream=/home/%I/mariadb-extra.sock




Reference:

Talks: https://lca2022.linux.org.au/schedule/presentation/18/
MariaDB system documentation: https://mariadb.com/kb/en/systemd/