MariaDB Essentials

MariaDB is a community-developed fork of MySQL, created in 2009 by the original MySQL developers after Oracle acquired Sun Microsystems (and with it, MySQL). It stays wire- and SQL-compatible with MySQL for most workloads — the same drivers, the same client protocol, mysqldump files load fine — but it has diverged with its own storage engines, its own optimizer improvements, and features MySQL doesn't have (like system-versioned tables and a genuinely open governance model with no single vendor behind it). It's the default MySQL replacement on most Linux distros (Red Hat, Debian) and a common choice when you want MySQL-shaped SQL without depending on Oracle's roadmap.

Storage Engines: InnoDB, Aria, ColumnStore

MariaDB ships several storage engines and you pick per-table. InnoDB (the default for transactional tables) is the same lineage MySQL uses — row-level locking, MVCC, foreign keys, crash-safe via redo/undo logs. Aria is MariaDB's own replacement for MyISAM: it's crash-safe (unlike MyISAM) and used internally for temporary tables and the system schema, but like MyISAM it doesn't support transactions, so it's a fit for read-heavy, non-transactional tables (logs, static lookup data). ColumnStore is a columnar engine bolted on for analytical (OLAP) workloads — it stores data column-by-column instead of row-by-row, which is dramatically faster for aggregate queries over millions of rows but a poor fit for single-row lookups or frequent updates.

-- Transactional table (default choice for app data)
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending',
  total_cents INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

-- Read-mostly, non-transactional table — Aria is lighter weight than InnoDB
CREATE TABLE audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(64) NOT NULL,
  payload JSON,
  logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=Aria;

-- Check which engine a table actually uses
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE();

-- Convert an existing table's engine (rewrites the whole table — heavy on large tables)
ALTER TABLE audit_log ENGINE=InnoDB;

Galera Cluster: Synchronous Multi-Master Replication

MariaDB's flagship high-availability story is Galera Cluster, which ships built in as of 10.1+ (mariadb-server includes wsrep support). Unlike classic MySQL/MariaDB replication — which is asynchronous and has one writable primary with read replicas trailing behind — Galera gives you synchronous, multi-master replication: every node can accept writes, and a transaction only commits once it has been certified across the whole cluster. That eliminates replication lag and lets you write to any node, but it comes at a real cost: every write pays the latency of the slowest node in the cluster, and rows that are updated concurrently on two different nodes can conflict and cause one transaction to be rolled back at commit time (certification-based conflict resolution, not lock-based).

-- galera.cnf (typically /etc/mysql/conf.d/galera.cnf on each node)
-- [galera]
-- wsrep_on=ON
-- wsrep_provider=/usr/lib/galera/libgalera_smm.so
-- wsrep_cluster_address=gcomm://node1,node2,node3
-- wsrep_cluster_name='my_cluster'
-- binlog_format=ROW
-- default_storage_engine=InnoDB   -- Galera requires InnoDB; MyISAM/Aria are not replicated
-- innodb_autoinc_lock_mode=2      -- required for multi-master auto_increment safety

-- Bootstrap the very first node of a fresh cluster (only once, only on one node)
-- galera_new_cluster

-- Check cluster health from any node
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';   -- expect 'Primary'
SHOW STATUS LIKE 'wsrep_local_state_comment';  -- expect 'Synced'
SHOW STATUS LIKE 'wsrep_ready';

MariaDB-Specific SQL Features

A handful of features exist in MariaDB that either don't exist in MySQL at all, or diverged from MySQL's later implementation. System-versioned tables give you built-in temporal history (like a lightweight audit trail) without hand-rolling triggers. Sequences are standalone auto-incrementing objects (closer to Postgres/Oracle sequences) instead of being tied to a single column. Invisible columns let you hide a column from SELECT * without breaking application code that isn't aware of it. And the RETURNING clause on INSERT/UPDATE/DELETE — which MySQL still lacks — lets you get modified rows back in one round trip.

-- System-versioned table: MariaDB automatically tracks row history
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10,2),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH SYSTEM VERSIONING;

UPDATE products SET price = 29.99 WHERE id = 1;

-- See the row as it looked before the update
SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '2026-08-01 00:00:00' WHERE id = 1;
SELECT * FROM products FOR SYSTEM_TIME ALL WHERE id = 1;  -- full history

-- RETURNING — get affected rows back without a follow-up SELECT (MySQL has no equivalent)
INSERT INTO orders (customer_id, status, total_cents)
VALUES (42, 'pending', 5000)
RETURNING id, created_at;

DELETE FROM audit_log WHERE logged_at < NOW() - INTERVAL 90 DAY
RETURNING id;

-- Sequences — independent of any single table/column
CREATE SEQUENCE order_ref_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXTVAL(order_ref_seq);

-- Invisible column — excluded from SELECT * and INSERT without an explicit column list
ALTER TABLE customers ADD COLUMN internal_notes TEXT INVISIBLE;

Indexing, Query Tuning & EXPLAIN

As with MySQL, most performance problems trace back to missing or wrong indexes. Composite indexes should put the most selective/equality-filtered column first, and the leftmost-prefix rule means an index on (customer_id, status) can serve queries filtering on customer_id alone or on both columns, but not on status alone. MariaDB's optimizer trace and ANALYZE (a newer addition that actually runs the query and compares estimated vs actual row counts) are more useful than plain EXPLAIN for tracking down bad cardinality estimates.

-- Composite index matching a common WHERE + ORDER BY pattern
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status, created_at DESC);

-- ANALYZE actually executes the query and reports estimated vs real row counts
ANALYZE FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- Find unused indexes eating write throughput for nothing
SELECT object_schema, object_name, index_name
FROM information_schema.INNODB_INDEX_STATS
WHERE stat_name = 'size' AND index_name != 'PRIMARY';

-- Force a specific index when the optimizer picks a poor plan
SELECT * FROM orders USE INDEX (idx_orders_customer_status)
WHERE customer_id = 42 AND status = 'pending';

Gotchas & Practical Tips

• MariaDB and MySQL have diverged on JSON: MariaDB's JSON type is really just LONGTEXT with a CHECK constraint, not a native binary type like MySQL 8's JSON. It works fine for storage and MariaDB's JSON_* functions, but don't expect the storage-level optimizations MySQL's binary JSON gives you.

• Galera does not replicate MyISAM or Aria tables — if you're running a Galera cluster, every table that needs to be consistent across nodes must be InnoDB. Mixing engines silently breaks consistency, it doesn't error loudly.

• Client libraries and drivers are largely interchangeable with MySQL (the wire protocol is compatible), but always check version-specific SQL mode defaults — MariaDB's STRICT_TRANS_TABLES behavior and reserved word list aren't identical to MySQL's, and a query that works on one can throw a syntax error on the other.

• In a Galera cluster, large transactions (bulk inserts/updates touching many rows) increase the chance of certification conflicts and can stall the whole cluster during replication — batch big writes into smaller transactions rather than one giant one.

• Don't assume feature parity with MySQL just because the SQL usually runs unmodified — window functions, CTEs, and JSON functions all exist in both but were added in different versions with sometimes different syntax edge cases, so check the target version, not just "MySQL-compatible."