🎯

Why This Guide?

This guide is created from a real DBA point of view. It is not only a command list — it is a practical field reference for understanding MariaDB architecture, operating production databases, tuning performance, securing access, and automating daily DBA tasks.

💡 DBA Perspective If you are coming from an Oracle DBA, MySQL DBA, or cloud database background, MariaDB becomes easier when you understand three areas clearly: storage engines, InnoDB behavior, and replication/backup strategy.
⚖️

Oracle DBA View: Oracle vs MariaDB

For Oracle DBAs, MariaDB may look simple at first, but it has its own architecture, memory model, optimizer behavior, replication style, and storage engine flexibility. The table below helps map familiar Oracle concepts to MariaDB concepts.

AreaOracleMariaDBDBA Note
Primary storageTablespaces / datafilesInnoDB tablespaces / .ibd filesMariaDB often uses file-per-table with InnoDB.
RedoOnline redo logsInnoDB redo logCritical for crash recovery and write performance.
Undo / MVCCUndo tablespaceUndo logs in InnoDBNeeded for consistent reads and rollback.
OptimizerCBO with statsCost-based optimizer with table/index statsEXPLAIN, ANALYZE, and index design are key.
Flashback styleFlashback Query / FDASystem-versioned tablesUseful for audit/history use cases.
HA / replicationData Guard / GoldenGate / RACBinary log replication / GTID / GaleraDesign depends on RPO/RTO and workload.
BackupRMANmariabackup / mysqldumpAlways test restore, not only backup success.
⚠️ Real DBA Insight Do not treat MariaDB as “just MySQL commands.” Production stability depends on the right engine, right indexing strategy, tested backup, secure grants, and well-designed replication.
🏛

MariaDB Architecture

MariaDB is a community-driven fork of MySQL offering a fully pluggable storage engine architecture, enhanced replication (Galera, GTID), and features like temporal tables, virtual columns, and JSON functions — all production-ready for OLTP and analytical workloads.

Client Connection Layer TCP/IP · Unix Socket · Named Pipe · SSL/TLS · MaxScale Proxy Application MySQL CLI JDBC / ODBC Replication MaxScale DBeaver SQL Interface & Connection Management Thread cache · Connection pool · Auth · Privilege check · Character set Query Processing Engine Parser Lexer → AST Syntax validation Optimizer Cost-based plan Index selection Query cache Result set cache (MariaDB 10.1+) Execution engine Row operations Filesort / tmp tables Pluggable Storage Engine Layer InnoDB ACID · MVCC Row locking Aria Crash-safe MyISAM Temp tables MyRocks LSM-tree · RocksDB High write I/O MEMORY In-RAM tables No persistence ColumnStore OLAP columnar Analytical queries Spider Sharding BLACKHOLE Replication relay SEQUENCE Number gen CONNECT External data TokuDB Fractal tree CSV Plain CSV files Memory & Cache Layer InnoDB buffer pool · Key cache · Table cache · Sort buffer · Join buffer Logging & Replication Binary log · Redo log · Undo log · GTID · Galera wsrep · Semi-sync Physical Storage .ibd files · .frm · .aria · .MYD / .MYI · Binary logs · OS filesystem · Raw disk SSD · NVMe · SAN · EBS (AWS) · Persistent Disk (GCP)

Storage Engine Comparison

InnoDB

Default engine. ACID-compliant, MVCC row-level locking, foreign keys, crash recovery. Best for OLTP.

Aria

Crash-safe replacement for MyISAM. Used internally for temp tables. Good for read-heavy workloads.

MyRocks

RocksDB-based LSM tree. Excellent write amplification reduction. Ideal for high-write workloads.

ColumnStore

Columnar storage for OLAP. Parallel query execution on billions of rows. Great for data warehousing.

MEMORY

All data in RAM. No persistence. Ideal for lookup tables and temporary caches.

Spider

Built-in sharding and horizontal partitioning. Federated access across multiple MariaDB nodes.

⚡

Essential DBA Commands

Connection & Server Info

Connection & StatusSQL
-- Connect from command line
mariadb -u root -p -h 127.0.0.1 -P 3306

-- Server version and status
SELECT VERSION();
SHOW STATUS;
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW VARIABLES LIKE '%innodb%';
SHOW VARIABLES LIKE 'max_connections';

-- Current connections
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- Kill a specific connection
KILL 12345;
KILL QUERY 12345;  -- kill only the query, keep connection

Database & Schema Management

DDL OperationsSQL
-- Database operations
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE myapp;
DROP DATABASE IF EXISTS myapp;

-- Table operations
SHOW TABLES;
SHOW TABLE STATUS;
DESCRIBE employees;
SHOW CREATE TABLE employees\G
SHOW FULL COLUMNS FROM employees;

-- Table size info
SELECT
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
  table_rows,
  engine
FROM information_schema.TABLES
WHERE table_schema = 'myapp'
ORDER BY (data_length + index_length) DESC;

-- Online DDL (MariaDB 10.3+)
ALTER TABLE employees
  ADD COLUMN dept_id INT,
  ADD INDEX idx_dept (dept_id),
  ALGORITHM=INPLACE, LOCK=NONE;

User & Privilege Management

Security & GrantsSQL
-- Create users
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss2024!';
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly@123';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'192.168.1.%';
GRANT SELECT ON myapp.* TO 'readonly'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%' IDENTIFIED BY 'ReplPass!';
FLUSH PRIVILEGES;

-- Show users and grants
SELECT user, host, password_expired, account_locked
FROM mysql.user ORDER BY user;
SHOW GRANTS FOR 'appuser'@'192.168.1.%';

-- Revoke and drop
REVOKE ALL PRIVILEGES ON myapp.* FROM 'appuser'@'192.168.1.%';
DROP USER 'appuser'@'192.168.1.%';

-- Password policy (MariaDB 10.4+)
SET GLOBAL strict_password_validation = 1;
ALTER USER 'appuser'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

Replication Commands

Replication ManagementSQL
-- Check replication status (replica side)
SHOW REPLICA STATUS\G        -- MariaDB 10.5+
SHOW SLAVE STATUS\G          -- older syntax

-- Setup replica
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.1',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'ReplPass!',
  MASTER_USE_GTID = slave_pos;
START REPLICA;

-- Primary side binlog info
SHOW MASTER STATUS\G
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mariadb-bin.000010' LIMIT 20;

-- GTID-based operations
SELECT @@gtid_current_pos;
SELECT @@gtid_binlog_pos;

-- Skip one errant transaction
STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;
🔥

Performance Tuning

EXPLAIN & Query Analysis

Query AnalysisSQL
-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

-- Extended EXPLAIN (shows filtered %)
EXPLAIN EXTENDED SELECT * FROM orders WHERE customer_id = 1001;

-- JSON format (detailed optimizer info)
EXPLAIN FORMAT=JSON
SELECT o.id, c.name, SUM(oi.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at > '2025-01-01'
GROUP BY o.id;

-- Analyze (executes + returns real row counts)
ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- Slow query log check
SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- seconds

-- Performance schema: top slow queries
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(AVG_TIMER_WAIT/1000000000000, 3) AS avg_sec,
  ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Index Management

Index OperationsSQL
-- Show indexes
SHOW INDEX FROM orders;
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'myapp' AND TABLE_NAME = 'orders';

-- Create indexes
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
CREATE UNIQUE INDEX idx_email ON customers (email);
CREATE FULLTEXT INDEX idx_ft_desc ON products (description);

-- Invisible indexes (test impact without dropping)
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- Find unused indexes (no seeks/scans)
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'information_schema')
ORDER BY object_schema, object_name;

-- Update table statistics
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;        -- reclaims space, rebuilds indexes
mysqlcheck -u root -p --all-databases --analyze

InnoDB Buffer Pool & Memory

InnoDB TuningSQL
-- Buffer pool hit ratio (should be > 99%)
SELECT
  ROUND(
    (1 - (
      (SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = 'Innodb_buffer_pool_reads') /
      (SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100, 2
  ) AS buffer_pool_hit_ratio;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Check buffer pool usage
SELECT
  pool_id,
  pool_size,
  free_buffers,
  database_pages,
  ROUND(database_pages / pool_size * 100, 1) AS pct_used
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- Memory usage overview
SELECT * FROM sys.memory_global_total;
SELECT event_name, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes
LIMIT 10;

Key Configuration Parameters

ParameterRecommended ValueDescription
innodb_buffer_pool_size70–80% of RAMMost critical setting. Caches data and index pages.
innodb_buffer_pool_instances8–16Reduce contention on multi-core systems.
innodb_log_file_size1–4 GBRedo log size. Larger = fewer checkpoints, faster writes.
innodb_flush_log_at_trx_commit1 (ACID) / 2 (perf)1 = full durability. 2 = 1s data loss risk, faster.
innodb_io_capacity200–2000 (SSD: 2000+)InnoDB background I/O operations per second.
max_connections150–500Max simultaneous connections. Use connection pooling.
query_cache_type0 (OFF)Query cache is deprecated; use ProxySQL/application cache.
tmp_table_size64M–256MIn-memory temp table size before disk spill.
thread_pool_sizeCPU coresMariaDB thread pool for high-concurrency workloads.
binlog_formatROWMost reliable for replication. MIXED for storage savings.
my.cnf — Production TemplateCONFIG
# /etc/mysql/mariadb.conf.d/99-custom.cnf
[mysqld]
# ── Engine ──────────────────────────────────────────
default_storage_engine     = InnoDB
innodb_buffer_pool_size    = 12G      # 75% of 16G RAM
innodb_buffer_pool_instances = 8
innodb_log_file_size       = 2G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method        = O_DIRECT
innodb_io_capacity         = 2000     # NVMe SSD
innodb_io_capacity_max     = 4000
innodb_read_io_threads     = 8
innodb_write_io_threads    = 8

# ── Connections ────────────────────────────────────
max_connections            = 300
thread_pool_size           = 16
thread_cache_size          = 64
wait_timeout               = 300
interactive_timeout        = 300

# ── Memory ─────────────────────────────────────────
tmp_table_size             = 128M
max_heap_table_size        = 128M
sort_buffer_size           = 4M
join_buffer_size           = 4M
read_buffer_size           = 2M
read_rnd_buffer_size       = 4M

# ── Logging ────────────────────────────────────────
slow_query_log             = 1
slow_query_log_file        = /var/log/mysql/slow.log
long_query_time            = 1
log_queries_not_using_indexes = 1
general_log                = 0       # enable only for debugging

# ── Replication ────────────────────────────────────
server_id                  = 1
log_bin                    = /var/log/mysql/mariadb-bin
binlog_format              = ROW
expire_logs_days           = 7
gtid_strict_mode           = 1
sync_binlog                = 1

# ── Character Set ──────────────────────────────────
character_set_server       = utf8mb4
collation_server           = utf8mb4_unicode_ci
💾

Backup & Recovery

mariabackup (Physical Backup)

mariabackup — Full & IncrementalBASH
# Full backup
mariabackup --backup \
  --user=root \
  --password='yourpassword' \
  --target-dir=/backup/full/$(date +%Y%m%d)

# Prepare (apply redo logs)
mariabackup --prepare \
  --target-dir=/backup/full/20260509

# Incremental backup (after full)
mariabackup --backup \
  --user=root --password='yourpassword' \
  --target-dir=/backup/incr/$(date +%Y%m%d_%H) \
  --incremental-basedir=/backup/full/20260509

# Prepare incremental
mariabackup --prepare \
  --target-dir=/backup/full/20260509 \
  --incremental-dir=/backup/incr/20260509_02

# Restore
systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/full/20260509
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

mysqldump (Logical Backup)

Logical BackupBASH
# Full dump with GTID info
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  | gzip > /backup/full_$(date +%Y%m%d).sql.gz

# Single database
mysqldump -u root -p --single-transaction myapp \
  | gzip > /backup/myapp_$(date +%Y%m%d).sql.gz

# Restore
zcat /backup/myapp_20260509.sql.gz | mysql -u root -p myapp

# Dump only structure
mysqldump -u root -p --no-data myapp > myapp_schema.sql

# Dump only data (no create statements)
mysqldump -u root -p --no-create-info myapp > myapp_data.sql
💡 DBA Tip Always use --single-transaction with InnoDB for a consistent snapshot without locking. Never use --lock-all-tables in production — it blocks all writes.
⚠️ Critical — Test Your Restores A backup you've never restored is not a backup. Schedule monthly restore drills to a separate environment and verify row counts and application functionality.
🔐

Security Hardening

Security Audit Queries

Security ChecksSQL
-- Users with no password
SELECT user, host FROM mysql.user
WHERE (authentication_string = '' OR authentication_string IS NULL)
  AND plugin NOT IN ('unix_socket', 'gssapi');

-- Users with global privileges (risky)
SELECT user, host, Super_priv, Grant_priv, File_priv, Process_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

-- Anonymous users
SELECT user, host FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

-- Wildcard host access
SELECT user, host FROM mysql.user WHERE host = '%';

-- Audit plugin status
SHOW PLUGINS;
SELECT * FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE '%audit%';

-- Enable MariaDB Audit Plugin
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = 'ON';
SET GLOBAL server_audit_events = 'QUERY,CONNECT';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

SSL Configuration

SSL/TLS SetupBASH
# Generate self-signed CA (production: use real CA)
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# Server certificate
openssl req -newkey rsa:2048 -days 3650 \
  -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 3650 \
  -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial > server-cert.pem

# Add to my.cnf [mysqld]
# ssl-ca   = /etc/mysql/ssl/ca-cert.pem
# ssl-cert = /etc/mysql/ssl/server-cert.pem
# ssl-key  = /etc/mysql/ssl/server-key.pem
# require_secure_transport = ON
🛠

DBA Automation Scripts

Health Check Script

mariadb_health_check.shBASH
#!/bin/bash
# MariaDB Health Check Script
# Usage: ./mariadb_health_check.sh

DB_USER="root"
DB_PASS="yourpassword"
DB_HOST="127.0.0.1"
LOG_FILE="/var/log/mariadb_health_$(date +%Y%m%d).log"
ALERT_EMAIL="dba@yourcompany.com"

MYSQL="mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -e"
ISSUES=0

log() { echo "[$(date '+%F %T')] $1" | tee -a $LOG_FILE; }

log "=== MariaDB Health Check Start ==="

# 1. Uptime
UPTIME=$($MYSQL "SHOW GLOBAL STATUS LIKE 'Uptime';" | awk 'NR==2{print $2}')
log "Uptime: ${UPTIME}s ($(( UPTIME / 86400 )) days)"

# 2. Connections (warn if > 80%)
MAX_CONN=$($MYSQL "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2{print $2}')
CURR_CONN=$($MYSQL "SHOW GLOBAL STATUS LIKE 'Threads_connected';" | awk 'NR==2{print $2}')
PCT=$(( CURR_CONN * 100 / MAX_CONN ))
if [ $PCT -gt 80 ]; then
  log "WARN: Connections at ${PCT}% (${CURR_CONN}/${MAX_CONN})"
  ISSUES=$(( ISSUES + 1 ))
else
  log "OK: Connections ${CURR_CONN}/${MAX_CONN} (${PCT}%)"
fi

# 3. Buffer pool hit ratio
READS=$($MYSQL "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';" | awk 'NR==2{print $2}')
REQS=$($MYSQL "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';" | awk 'NR==2{print $2}')
HIT_RATIO=$(echo "scale=2; (1 - $READS / $REQS) * 100" | bc)
log "Buffer pool hit ratio: ${HIT_RATIO}%"

# 4. Replication lag
LAG=$($MYSQL "SHOW REPLICA STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master:" | awk '{print $2}')
if [[ "$LAG" != "NULL" ]] && [[ $LAG -gt 30 ]]; then
  log "WARN: Replication lag = ${LAG}s"
  ISSUES=$(( ISSUES + 1 ))
else
  log "OK: Replication lag = ${LAG:-N/A}s"
fi

# 5. Long running queries (> 60s)
LONG_QUERIES=$($MYSQL "SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE TIME > 60 AND COMMAND != 'Sleep';" | awk 'NR==2{print $1}')
if [ $LONG_QUERIES -gt 0 ]; then
  log "WARN: ${LONG_QUERIES} long-running queries detected"
  ISSUES=$(( ISSUES + 1 ))
fi

# 6. Summary
if [ $ISSUES -gt 0 ]; then
  log "ALERT: ${ISSUES} issue(s) found. Sending email..."
  mail -s "MariaDB Health Alert - $(hostname)" $ALERT_EMAIL < $LOG_FILE
else
  log "All checks passed."
fi
log "=== Health Check End ==="

Automated Backup Script

mariadb_backup.shBASH
#!/bin/bash
# MariaDB Full + Incremental Backup with Retention

BACKUP_ROOT="/backup/mariadb"
FULL_DIR="${BACKUP_ROOT}/full"
INCR_DIR="${BACKUP_ROOT}/incr"
LOG="/var/log/mariadb_backup.log"
RETENTION_DAYS=7
DB_USER="root"
DB_PASS="yourpassword"
TODAY=$(date +%Y%m%d)
DOW=$(date +%u)  # 1=Mon, 7=Sun

mkdir -p $FULL_DIR $INCR_DIR

log() { echo "[$(date '+%F %T')] $*" | tee -a $LOG; }

if [ "$DOW" -eq 7 ]; then
  # Sunday: Full backup
  log "Starting FULL backup..."
  mariabackup --backup \
    --user=$DB_USER --password="${DB_PASS}" \
    --target-dir=${FULL_DIR}/$TODAY 2>>$LOG
  mariabackup --prepare --target-dir=${FULL_DIR}/$TODAY 2>>$LOG
  # Store the latest full dir path
  echo ${FULL_DIR}/$TODAY > /tmp/last_full_backup.txt
  log "Full backup complete."
else
  # Other days: incremental
  LAST_FULL=$(cat /tmp/last_full_backup.txt)
  log "Starting INCREMENTAL backup based on ${LAST_FULL}..."
  mariabackup --backup \
    --user=$DB_USER --password="${DB_PASS}" \
    --target-dir=${INCR_DIR}/$TODAY \
    --incremental-basedir=$LAST_FULL 2>>$LOG
  log "Incremental backup complete."
fi

# Cleanup old backups
log "Cleaning up backups older than ${RETENTION_DAYS} days..."
find $FULL_DIR -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
find $INCR_DIR -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

log "Backup job finished. Size: $(du -sh ${BACKUP_ROOT} | cut -f1)"

Top Tables & Bloat Finder

Table Bloat & StatisticsSQL
-- Top 20 largest tables across all databases
SELECT
  table_schema AS 'Database',
  table_name   AS 'Table',
  engine,
  FORMAT(table_rows, 0)                                   AS rows,
  ROUND(data_length  / 1024 / 1024, 2)                    AS data_mb,
  ROUND(index_length / 1024 / 1024, 2)                    AS index_mb,
  ROUND((data_length + index_length) / 1024 / 1024, 2)    AS total_mb,
  ROUND(data_free   / 1024 / 1024, 2)                    AS free_mb,
  ROUND(data_free / (data_length + 1) * 100, 1)           AS bloat_pct
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql',
                            'performance_schema', 'sys')
  AND table_type = 'BASE TABLE'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Tables needing OPTIMIZE (>20% bloat)
SELECT
  CONCAT(table_schema, '.', table_name) AS full_name,
  ROUND(data_free / 1024 / 1024, 1) AS wasted_mb
FROM information_schema.TABLES
WHERE data_free / (data_length + 1) > 0.2
  AND data_free > 100 * 1024 * 1024
  AND engine = 'InnoDB'
ORDER BY data_free DESC;

Galera Cluster Status

Galera / Cluster MonitoringSQL
-- Cluster membership and sync state
SHOW STATUS LIKE 'wsrep_%';

-- Quick health summary
SELECT
  variable_name,
  variable_value
FROM information_schema.GLOBAL_STATUS
WHERE variable_name IN (
  'wsrep_cluster_size',
  'wsrep_cluster_status',
  'wsrep_connected',
  'wsrep_local_state_comment',
  'wsrep_ready',
  'wsrep_flow_control_paused'
);

-- Flow control check (pause > 0.1 = bottleneck)
SHOW STATUS LIKE 'wsrep_flow_control_paused';
⚡ Performance Reminder wsrep_flow_control_paused consistently above 0.1 indicates a lagging node is slowing the entire cluster. Check for long-running transactions, high write volume, or undersized nodes.

Useful Monitoring Queries (Daily DBA)

Daily Monitoring DashboardSQL
-- Active transactions with lock waits
SELECT
  r.trx_id waiting_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- Open transactions (running > 60s)
SELECT
  trx_id, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
  trx_query, trx_rows_modified
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

-- Deadlock info
SHOW ENGINE INNODB STATUS\G

-- Per-database size summary
SELECT
  table_schema,
  COUNT(*) AS tables,
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 3) AS total_gb
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql',
                            'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY total_gb DESC;

-- sys schema: top wait events
SELECT * FROM sys.waits_global_by_latency LIMIT 10;

-- sys schema: queries causing full table scans
SELECT query, exec_count, total_latency, no_index_used_count
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 10;
⏱

MariaDB-Specific Features

System Versioned (Temporal) Tables

Temporal TablesSQL
-- Create a versioned table (audit history built-in)
CREATE TABLE employee_salary (
  id       INT PRIMARY KEY,
  emp_id   INT,
  salary   DECIMAL(10,2),
  dept     VARCHAR(50)
) WITH SYSTEM VERSIONING;

-- Query historical data (as of a point in time)
SELECT * FROM employee_salary
FOR SYSTEM_TIME AS OF '2025-01-01 00:00:00'
WHERE emp_id = 42;

-- Query a time range
SELECT * FROM employee_salary
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2025-01-01'
WHERE emp_id = 42;

-- Full history
SELECT * FROM employee_salary FOR SYSTEM_TIME ALL
WHERE emp_id = 42
ORDER BY row_start;

-- Dynamic columns (JSON-like, MariaDB native)
CREATE TABLE inventory (
  id   INT PRIMARY KEY,
  name VARCHAR(100),
  attrs BLOB
);
INSERT INTO inventory VALUES (1, 'Widget',
  COLUMN_CREATE('color', 'blue', 'weight_kg', 1.5));
SELECT COLUMN_GET(attrs, 'color' AS CHAR) FROM inventory;
💡 Oracle DBA Note System versioned tables are MariaDB's equivalent of Oracle Flashback Data Archive (FDA/Total Recall). Unlike Oracle, no extra licensing is required — it's built into every MariaDB installation.
🏁

Final Thoughts

MariaDB is not only a MySQL alternative. It is a serious database platform for OLTP, analytics, cloud workloads, and enterprise applications when it is configured and operated correctly.

As a DBA, always focus on four pillars:

Performance

Use the right indexes, review execution plans, size InnoDB memory correctly, and monitor slow queries.

Stability

Control connections, manage logs, tune I/O, and avoid risky production changes without testing.

Security

Use least privilege, remove anonymous users, avoid wildcard access, enforce SSL/TLS, and enable auditing.

Automation

Automate health checks, backups, replication checks, and daily monitoring to reduce manual errors.

💬 Connect With Me If you found this guide useful, connect with me and follow my DBA learning journey for more Oracle, MariaDB, AWS, AI, and real-world database engineering content.