📘 DBA Reference Guide · May 2026
MariaDB for DBAs: Architecture, Performance Tuning & Real-World Scripts
A field-tested MariaDB playbook for Oracle, MySQL, and cloud DBAs covering architecture internals, essential commands, performance tuning, security hardening, backup strategy, and automation scripts.
Written by Punit Kumar — Oracle EBS DBA · Cloud DBA · AWS · AI Learner
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.
| Area | Oracle | MariaDB | DBA Note |
|---|---|---|---|
| Primary storage | Tablespaces / datafiles | InnoDB tablespaces / .ibd files | MariaDB often uses file-per-table with InnoDB. |
| Redo | Online redo logs | InnoDB redo log | Critical for crash recovery and write performance. |
| Undo / MVCC | Undo tablespace | Undo logs in InnoDB | Needed for consistent reads and rollback. |
| Optimizer | CBO with stats | Cost-based optimizer with table/index stats | EXPLAIN, ANALYZE, and index design are key. |
| Flashback style | Flashback Query / FDA | System-versioned tables | Useful for audit/history use cases. |
| HA / replication | Data Guard / GoldenGate / RAC | Binary log replication / GTID / Galera | Design depends on RPO/RTO and workload. |
| Backup | RMAN | mariabackup / mysqldump | Always 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.
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
| Parameter | Recommended Value | Description |
|---|---|---|
innodb_buffer_pool_size | 70–80% of RAM | Most critical setting. Caches data and index pages. |
innodb_buffer_pool_instances | 8–16 | Reduce contention on multi-core systems. |
innodb_log_file_size | 1–4 GB | Redo log size. Larger = fewer checkpoints, faster writes. |
innodb_flush_log_at_trx_commit | 1 (ACID) / 2 (perf) | 1 = full durability. 2 = 1s data loss risk, faster. |
innodb_io_capacity | 200–2000 (SSD: 2000+) | InnoDB background I/O operations per second. |
max_connections | 150–500 | Max simultaneous connections. Use connection pooling. |
query_cache_type | 0 (OFF) | Query cache is deprecated; use ProxySQL/application cache. |
tmp_table_size | 64M–256M | In-memory temp table size before disk spill. |
thread_pool_size | CPU cores | MariaDB thread pool for high-concurrency workloads. |
binlog_format | ROW | Most 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.
