MariaDB Cheat Sheet

mariadb -u root -pSHOW DATABASES;USE mydb;SHOW TABLES;DESCRIBE users;

CREATE TABLE users (  id INT AUTO_INCREMENT PRIMARY KEY,  email VARCHAR(255) NOT NULL UNIQUE,  created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO users (email) VALUES ('[email protected]');SELECT * FROM users WHERE email LIKE '%example%';REPLACE INTO users (id, email) VALUES (1, '[email protected]');

-- Aria: MariaDB's crash-safe MyISAM replacementCREATE TABLE logs (id INT PRIMARY KEY, msg TEXT) ENGINE=Aria;CREATE INDEX idx_email ON users(email);EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- MariaDB's JSON type is LONGTEXT + CHECK(JSON_VALID(col))CREATE TABLE events (  id INT PRIMARY KEY AUTO_INCREMENT,  payload LONGTEXT CHECK (JSON_VALID(payload)));INSERT INTO events (payload) VALUES  ('{"type":"login","user":{"id":42,"tags":["vip","beta"]}}');SELECT JSON_EXTRACT(payload, '$.user.id') AS user_id,       JSON_VALUE(payload, '$.type') AS type,       JSON_LENGTH(payload, '$.user.tags') AS tag_countFROM events;-- Shred a JSON array into rows (MariaDB 10.6+)SELECT jt.id, jt.tagFROM events,     JSON_TABLE(payload, '$.user.tags[*]' COLUMNS (       id FOR ORDINALITY,       tag VARCHAR(50) PATH '$'     )) AS jt;UPDATE events SET payload = JSON_SET(payload, '$.user.verified', TRUE) WHERE id = 1;

SELECT  dept_id,  salary,  RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,  LAG(salary)  OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_salary,  LEAD(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS next_salary,  SUM(salary)  OVER (    PARTITION BY dept_id ORDER BY salary DESC    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ) AS running_total,  NTILE(4) OVER (ORDER BY salary) AS salary_quartileFROM employees;-- Named windows reduce repetitionSELECT emp_id, salary,       AVG(salary) OVER w AS dept_avg,       salary - AVG(salary) OVER w AS diff_from_avgFROM employeesWINDOW w AS (PARTITION BY dept_id);

CREATE TABLE accounts (  id INT PRIMARY KEY,  balance DECIMAL(12,2)) WITH SYSTEM VERSIONING;UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- Current state (default)SELECT * FROM accounts WHERE id = 1;-- Point-in-time querySELECT * FROM accounts FOR SYSTEM_TIME AS OF '2026-07-01 00:00:00' WHERE id = 1;-- Full history including deleted rowsSELECT *, ROW_START, ROW_END FROM accounts FOR SYSTEM_TIME ALL WHERE id = 1;-- Auto-purge history older than 90 daysALTER TABLE accounts ADD SYSTEM VERSIONING PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK  (PARTITION p_hist HISTORY, PARTITION p_cur CURRENT);

-- my.cnf on each node[galera]wsrep_on=ONwsrep_provider=/usr/lib/galera/libgalera_smm.sowsrep_cluster_address="gcomm://node1,node2,node3"wsrep_cluster_name="my_cluster"wsrep_sst_method=mariabackupbinlog_format=ROWdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2-- Bootstrap the very first node only-- galera_new_cluster-- Check cluster health from any nodeSHOW STATUS LIKE 'wsrep_cluster_size';SHOW STATUS LIKE 'wsrep_local_state_comment';SHOW STATUS LIKE 'wsrep_flow_control_paused';