Skip to content

Instantly share code, notes, and snippets.

@grooverdan
Created January 13, 2022 21:43
Show Gist options
  • Save grooverdan/ad68b0161aa2ba9860b769a5304e83ab to your computer and use it in GitHub Desktop.
Save grooverdan/ad68b0161aa2ba9860b769a5304e83ab to your computer and use it in GitHub Desktop.
MariaDB Database per user, on demand (aka systemd multi-instance socket activated) (LCA2022 Miniconf talk)
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/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment