MariaDB Salt State: Database Server Setup and Backups

The mariadb Salt state manages the full lifecycle of MariaDB on every db* server in the WikiOasis fleet. It installs the mariadb-server package, renders a Jinja-templated 50-server.cnf, ensures the log directory has the correct ownership, and keeps the service running and watching for config changes. Sub-states extend the base with automated backup/restore tooling, binlog streaming, and monitoring users for Icinga and Prometheus.

Base state (`mariadb`)

Package installation

Installs the mariadb-server package via pkg.installed.

Log directory

Creates /var/log/mysql owned by mysql:adm with setgid mode 2750 so log files are group-readable by monitoring agents.

Server configuration

Renders /etc/mysql/mariadb.conf.d/50-server.cnf from the Jinja template salt://mariadb/files/50-server.cnf.jinja using values from the mariadb pillar key.

Service management

Ensures the mariadb service is running and enabled, and restarts it whenever the config file changes.

# salt/mariadb/init.sls (condensed)
install_mariadb:
  pkg.installed:
    - name: mariadb-server

/var/log/mysql:
  file.directory:
    - user: mysql
    - group: adm
    - mode: '2750'
    - require:
      - pkg: install_mariadb

/etc/mysql/mariadb.conf.d/50-server.cnf:
  file.managed:
    - source: salt://mariadb/files/50-server.cnf.jinja
    - template: jinja
    - user: root
    - group: root
    - mode: '0644'
    - require:
      - pkg: install_mariadb

mariadb:
  service.running:
    - enable: True
    - require:
      - file: /var/log/mysql
    - watch:
      - file: /etc/mysql/mariadb.conf.d/50-server.cnf

Pillar reference

All keys live under the top-level mariadb mapping. The innodb sub-key groups InnoDB-specific tunables.

Top-level mariadb pillar keys

Key	Default	Description
`datadir`	`/var/lib/mysql`	Path to the MariaDB data directory
`bind_address`	`0.0.0.0`	Interface address the server listens on
`key_buffer_size`	`128M`	MyISAM index key buffer (keep small if InnoDB-only)
`max_allowed_packet`	`1G`	Maximum packet size for large `LOAD DATA` / `BLOB` operations
`thread_stack`	`192K`	Stack size per connection thread
`thread_cache_size`	`8`	Threads to cache for reuse on new connections
`max_connections`	`100`	Maximum simultaneous client connections
`table_cache`	`64`	Number of open table descriptors to cache
`expire_logs_days`	`10`	Automatic binary log expiry in days
`server_id`	(unset)	Unique replication server ID; also enables binary logging when set

innodb.* pillar keys

Key	Default	Description
`innodb.buffer_pool_size`	`1G`	InnoDB buffer pool — set to ~80 % of available RAM
`innodb.log_file_size`	`256M`	InnoDB redo log file size
`innodb.flush_log_at_trx_commit`	`1`	`1` = fully ACID; `2` = flush once per second (faster, slight risk)
`innodb.file_per_table`	`true`	Each table gets its own `.ibd` file
`innodb.open_files`	`1000`	Maximum open `.ibd` file descriptors

When server_id is set in the pillar, the config template automatically enables binary logging at /var/log/mysql/mysql-bin.log with binlog_format = ROW and a 100 MB per-file cap. Omitting server_id leaves binary logging commented out.

Server configuration template

# /etc/mysql/mariadb.conf.d/50-server.cnf.jinja (excerpt)
[mariadbd]
user                    = mysql
datadir                 = {{ cfg.get('datadir', '/var/lib/mysql') }}
bind-address            = {{ cfg.get('bind_address', '0.0.0.0') }}

key_buffer_size         = {{ cfg.get('key_buffer_size', '128M') }}
max_allowed_packet      = {{ cfg.get('max_allowed_packet', '1G') }}
thread_stack            = {{ cfg.get('thread_stack', '192K') }}
thread_cache_size       = {{ cfg.get('thread_cache_size', 8) }}
max_connections         = {{ cfg.get('max_connections', 100) }}
table_cache             = {{ cfg.get('table_cache', 64) }}
expire_logs_days        = {{ cfg.get('expire_logs_days', 10) }}

{%- if cfg.get('server_id') %}
server-id               = {{ cfg.get('server_id') }}
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
max_binlog_size         = 100M
{%- endif %}

innodb_buffer_pool_size = {{ innodb.get('buffer_pool_size', '1G') }}
innodb_log_file_size    = {{ innodb.get('log_file_size', '256M') }}
innodb_flush_log_at_trx_commit = {{ innodb.get('flush_log_at_trx_commit', 1) }}
innodb_file_per_table   = {{ '1' if innodb.get('file_per_table', true) else '0' }}
innodb_open_files       = {{ innodb.get('open_files', 1000) }}

Pillar examples

db-c1-us-east-021 (primary C1, 20 GB pool)
db-pc-us-east-011 (PC cluster, 8 GB pool)
Default pillar (init.sls)

# pillar/mariadb/db-c1-us-east-021.sls
mariadb:
  server_id: 1021
  datadir: /var/lib/mysql
  bind_address: 0.0.0.0
  key_buffer_size: 128M
  max_allowed_packet: 1G
  thread_stack: 192K
  thread_cache_size: 8
  max_connections: 1000
  table_cache: 64
  expire_logs_days: 10
  innodb:
    buffer_pool_size: 20G
    log_file_size: 256M
    flush_log_at_trx_commit: 1
    file_per_table: true

# pillar/mariadb/db-pc-us-east-011.sls
mariadb:
  server_id: 1012
  datadir: /var/lib/mysql
  bind_address: 0.0.0.0
  key_buffer_size: 128M
  max_allowed_packet: 1G
  thread_stack: 192K
  thread_cache_size: 8
  max_connections: 1000
  table_cache: 64
  expire_logs_days: 10
  innodb:
    buffer_pool_size: 8G
    log_file_size: 256M
    flush_log_at_trx_commit: 1
    file_per_table: true

# pillar/mariadb/init.sls — baseline applied to all db* minions
mariadb:
  datadir: /var/lib/mysql
  bind_address: 0.0.0.0
  key_buffer_size: 128M
  max_allowed_packet: 1G
  thread_stack: 192K
  thread_cache_size: 8
  max_connections: 100
  table_cache: 64
  expire_logs_days: 10
  innodb:
    buffer_pool_size: 1G
    log_file_size: 256M
    flush_log_at_trx_commit: 1
    file_per_table: true

Backup sub-state (`mariadb.backup`)

The mariadb.backup sub-state is conditionally applied — all resources are skipped unless the pillar key mariadb:backup:destination:host is set. When enabled it installs the backup toolchain, creates a dedicated DB user, schedules cron jobs, and runs a persistent systemd service for real-time binlog streaming.

Full backup

Weekly on Sunday at 01:00 using mariadb-backup-run.sh full. Streams an xbstream archive directly to the remote host over SSH — no local disk usage.

Incremental backup

Mon–Sat at 02:00 using mariadb-backup-run.sh incremental. Uses the LSN checkpoints from the previous run; falls back to a full backup if checkpoints are missing or stale (> 7 days).

Binlog streaming

mariadb-binlog-stream.service runs mariadb-binlog with --stop-never to continuously stream binary logs to /var/backups/mariadb/binlogs/. Runs as a systemd service with Restart=always.

Binlog sync

Every 5 minutes via cron, mariadb-binlog-sync.sh rsyncs the local binlog directory to the remote backup server over SSH.

Installed files

Path	Purpose
`/usr/local/bin/mariadb-backup-run.sh`	Full / incremental backup runner
`/usr/local/bin/mariadb-binlog-stream.sh`	Continuous binlog streaming daemon
`/usr/local/bin/mariadb-binlog-sync.sh`	Rsync binlogs to remote
`/etc/mariadb-backup/ssh_key`	SSH private key (from pillar, mode `0600`)
`/var/backups/mariadb/`	Local state files: LSN checkpoints, last backup timestamps
`/var/backups/mariadb/binlogs/`	Locally streamed binlog files

Backup user grants

The mariabackup_db_user state creates and maintains the mariadb_backup@localhost user with the minimum grants required:

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE
  ON *.* TO 'mariadb_backup'@'localhost';

Backup pillar keys

mariadb:
  backup:
    user: mariadb_backup          # DB user for backup operations
    password: "secret"            # DB user password
    ssh_private_key: |            # Private key for rsync/SSH to backup host
      -----BEGIN OPENSSH PRIVATE KEY-----
      ...
      -----END OPENSSH PRIVATE KEY-----
    destination:
      host: backup.example.com    # Remote backup server hostname/IP
      user: backup                # Remote SSH user
      path: /backup/mariadb       # Remote base path (hostname subdirs created automatically)

Notification webhooks

The backup runner sends Discord and/or Slack notifications on completion or failure. Configure via the notifications pillar:

notifications:
  discord_webhook_url: "https://discord.com/api/webhooks/..."
  slack_webhook_url: "https://hooks.slack.com/services/..."

Cron schedule

# Weekly full backup — Sunday 01:00
0 1 * * 0  root  /usr/local/bin/mariadb-backup-run.sh full >> /var/log/mariadb-backup.log 2>&1

# Daily incremental — Mon-Sat 02:00
0 2 * * 1-6  root  /usr/local/bin/mariadb-backup-run.sh incremental >> /var/log/mariadb-backup.log 2>&1

# Binlog sync — every 5 minutes
*/5 * * * *  root  /usr/local/bin/mariadb-binlog-sync.sh >> /var/log/mariadb-backup.log 2>&1

Monitoring sub-states

`mariadb.monitoring_user`

Creates the icinga_monitor@'%' user used by Icinga/NRPE health checks. The password is read from monitoring:monitoring_db_password in the pillar. The user is granted only USAGE (no data access).

# monitoring_user.sls
icinga_monitor_user:
  cmd.run:
    - name: >
        mysql -e
        "CREATE USER IF NOT EXISTS 'icinga_monitor'@'%' IDENTIFIED BY '{{ password }}';
         ALTER USER 'icinga_monitor'@'%' IDENTIFIED BY '{{ password }}';"

icinga_monitor_grant:
  cmd.run:
    - name: mysql -e "GRANT USAGE ON *.* TO 'icinga_monitor'@'%';"

`mariadb.prometheus_user`

Creates the prom_exporter@'127.0.0.1' user for the mysqld_exporter Prometheus agent. Password is read from monitoring:mysqld_exporter_password. Grants allow the exporter to read process list, replication status, and performance schema metrics.

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'prom_exporter'@'127.0.0.1';
GRANT SELECT ON performance_schema.* TO 'prom_exporter'@'127.0.0.1';

`mariadb.nrpe_backup`

Installs an NRPE plugin and configuration to monitor backup freshness:

Path	Description
`/usr/lib/nagios/plugins/check_mariadb_backup.sh`	Custom check script (mode `0755`)
`/etc/nagios/nrpe.d/mariadb_backup.cfg`	NRPE command definition; triggers `nagios-nrpe-server` reload on change

Applying the state

# Apply full mariadb state (install + config + service)
salt 'db*' state.apply mariadb

# Apply only the backup sub-state
salt 'db*' state.apply mariadb.backup

# Apply monitoring user sub-states
salt 'db*' state.apply mariadb.monitoring_user
salt 'db*' state.apply mariadb.prometheus_user

# Apply NRPE backup check
salt 'db*' state.apply mariadb.nrpe_backup

Run salt 'db*' pillar.get mariadb first to verify pillar data is rendered correctly before applying state changes.

Core

Web Tier

Data & Search

Infrastructure

Monitoring

MariaDB Salt State: Database Server Setup and Backups

Base state (`mariadb`)

Pillar reference

Server configuration template

Pillar examples

Backup sub-state (`mariadb.backup`)

Full backup

Incremental backup

Binlog streaming

Binlog sync

Installed files

Backup user grants

Backup pillar keys

Notification webhooks

Cron schedule

Monitoring sub-states

`mariadb.monitoring_user`

`mariadb.prometheus_user`

`mariadb.nrpe_backup`

Applying the state

Build docs developers (and LLMs) love

Core

Web Tier

Data & Search

Infrastructure

Monitoring

Documentation Index

​Base state (mariadb)

​Pillar reference

​Server configuration template

​Pillar examples

​Backup sub-state (mariadb.backup)

Full backup

Incremental backup

Binlog streaming

Binlog sync

​Installed files

​Backup user grants

​Backup pillar keys

​Notification webhooks

​Cron schedule

​Monitoring sub-states

​mariadb.monitoring_user

​mariadb.prometheus_user

​mariadb.nrpe_backup

​Applying the state

Build docs developers (and LLMs) love

Base state (`mariadb`)

Pillar reference

Server configuration template

Pillar examples

Backup sub-state (`mariadb.backup`)

Installed files

Backup user grants

Backup pillar keys

Notification webhooks

Cron schedule

Monitoring sub-states

`mariadb.monitoring_user`

`mariadb.prometheus_user`

`mariadb.nrpe_backup`

Applying the state