MariaDB Support

In-place upgrade 10.6 to 11.8 or 12.3 LTS, replicas first, with verification

# MariaDB support runbook: in-place upgrade 10.6 LTS -> 11.8 or 12.3 LTS.
# Order: replicas first, primary last. One LTS hop at a time is the safest path.
# Credentials come from ~/.my.cnf or an option file readable only by the DBA.

# 1. Record where we start.
mariadb -e "SELECT @@version, @@innodb_fast_shutdown, @@gtid_binlog_pos;"

# 2. Slow shutdown flushes undo and the change buffer, so the new server
#    does not have to process old-format InnoDB structures at startup.
mariadb -e "SET GLOBAL innodb_fast_shutdown = 0;"
systemctl stop mariadb

# 3. Replace packages (repository pinned to the target series), then start.
#    MariaDB 12.3 turns innodb_snapshot_isolation ON by default: REPEATABLE READ
#    transactions that update rows changed after their snapshot now fail with
#    error 1020 instead of silently overwriting. Test the application for it.
systemctl start mariadb

# 4. Upgrade the system tables and check every table for incompatibilities.
mariadb-upgrade

# 5. Verify: version, replication health, and any table mariadb-upgrade flagged.
mariadb -e "SELECT @@version; SHOW REPLICA STATUS\G" | grep -E "version|Running|Seconds_Behind"

MariaDB support configuration for a Galera node, with the reason for each setting

# /etc/mysql/mariadb.conf.d/60-galera.cnf -- MariaDB support baseline, 3-node Galera
# Paths are Debian/Ubuntu defaults; RHEL uses /usr/lib64/galera-4/libgalera_smm.so
[galera]
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = prod-galera
wsrep_cluster_address    = gcomm://10.0.2.11,10.0.2.12,10.0.2.13
wsrep_node_address       = 10.0.2.11
wsrep_sst_method         = mariabackup       # non-blocking state transfer for new nodes
wsrep_sst_auth           = sst_user:${GALERA_SST_PASSWORD}   # rendered from the secret store
wsrep_slave_threads      = 8                 # parallel appliers; size to CPU and write rate
wsrep_provider_options   = "gcache.size=4G; gcs.fc_limit=256; gcs.fc_factor=0.9"
binlog_format            = ROW               # required by Galera
innodb_autoinc_lock_mode = 2                 # interleaved auto-increment, required by Galera

Galera health check, run on every node

-- MariaDB DBA support check: Galera health in one query, run on each node.
SELECT variable_name, variable_value
FROM information_schema.global_status
WHERE variable_name IN (
  'WSREP_CLUSTER_STATUS',          -- must be Primary; Non-Primary means lost quorum
  'WSREP_CLUSTER_SIZE',            -- expected node count
  'WSREP_LOCAL_STATE_COMMENT',     -- Synced is healthy; Donor/Joiner during SST/IST
  'WSREP_READY',                   -- ON when the node accepts queries
  'WSREP_FLOW_CONTROL_PAUSED',     -- fraction of time paused by flow control
  'WSREP_LOCAL_RECV_QUEUE_AVG',    -- above ~1 means this node applies too slowly
  'WSREP_LOCAL_CERT_FAILURES',     -- certification conflicts since startup
  'WSREP_LOCAL_BF_ABORTS'          -- local transactions aborted by replicated ones
);

MariaDB support diagnostics: slow log, ANALYZE, live EXPLAIN and purge backlog

-- MariaDB support diagnostics. performance_schema is OFF by default in MariaDB,
-- so the slow query log with plan output is usually the first evidence source.
SET GLOBAL slow_query_log      = ON;
SET GLOBAL long_query_time     = 0.5;                      -- seconds
SET GLOBAL log_slow_verbosity  = 'query_plan,explain';     -- plan and EXPLAIN in the log

-- Prove the cause on one statement. ANALYZE executes it and reports actual rows
-- (r_rows) and filtering (r_filtered) next to the optimizer's estimates.
ANALYZE FORMAT=JSON
SELECT customer_id, SUM(amount)
FROM orders
WHERE created_at >= NOW() - INTERVAL 1 DAY
GROUP BY customer_id;

-- Live plan of a statement that is already running in another session.
SHOW EXPLAIN FOR 4711;                                     -- connection id from PROCESSLIST

-- Long transactions pin undo history and grow the purge backlog.
SELECT trx_mysql_thread_id, trx_started, trx_rows_modified,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;
SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';

Server baseline for 11.8 and 12.3: default, reason and reload behaviour per line

# /etc/mysql/mariadb.conf.d/50-minervadb.cnf -- MariaDB support baseline, MariaDB 11.8 / 12.3, 64 GB host (illustrative)
# Format: parameter = proposed      # default -> why ; dynamic or restart
[mariadbd]
innodb_buffer_pool_size        = 44G      # 128M -> ~70% of RAM for the working set ; dynamic
innodb_log_file_size           = 8G       # 96M -> fewer checkpoint stalls under bursts ; dynamic since 10.11
innodb_flush_log_at_trx_commit = 1        # 1 -> keep: full durability ; dynamic
sync_binlog                    = 1        # 0 -> binlog survives a crash, safe for replicas ; dynamic
innodb_io_capacity             = 4000     # 200 -> match sustained IOPS of the volume ; dynamic
binlog_format                  = ROW      # MIXED -> deterministic replication and CDC ; dynamic
log_slave_updates              = ON       # replicas keep binlogs for promotion ; restart
gtid_strict_mode               = ON       # OFF -> catch divergent writes early ; dynamic
thread_handling                = pool-of-threads   # one-thread-per-connection -> stable under spikes ; restart
max_connections                = 800      # 151 -> sized to pooler output, not app instances ; dynamic
performance_schema             = ON       # OFF -> wait and statement evidence ; restart

Enterprise MariaDB support baseline: ed25519 accounts, roles, the audit plugin and a privilege review

-- Enterprise MariaDB support hardening baseline (MariaDB 10.4+ syntax).
-- 1. Strong authentication: ed25519 or unix_socket, never mysql_native_password.
CREATE USER 'svc_orders'@'10.20.%'
  IDENTIFIED VIA ed25519 USING PASSWORD('${SVC_ORDERS_PASSWORD}')
  REQUIRE SSL;

-- 2. Roles carry privileges; accounts only receive roles.
CREATE ROLE app_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO app_write;
GRANT app_write TO 'svc_orders'@'10.20.%';
SET DEFAULT ROLE app_write FOR 'svc_orders'@'10.20.%';

-- 3. Audit trail with the MariaDB Audit Plugin, buffered writes in 12.3.
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_events  = 'CONNECT,QUERY_DDL,QUERY_DCL';
SET GLOBAL server_audit_logging = ON;

-- 4. Review: weak plugins, accounts open to any host, and global SUPER-style rights.
SELECT user, host, plugin FROM mysql.user
WHERE plugin IN ('mysql_native_password', 'mysql_old_password') OR host = '%';
SELECT grantee, privilege_type FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER', 'SHUTDOWN', 'FILE');