MariaDB Guide — MySQL Replacement with Advanced Features

# On Ubuntu/Debian
sudo apt update
sudo apt install mariadb-server mariadb-client -y

# On RHEL/Fedora
sudo dnf install mariadb-server -y

# Start the service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Secure installation
sudo mysql_secure_installation

# Verify version
mysql --version
# Expected: mysql  Ver 15.1 Distrib 10.11.x-MariaDB

-- Create a table with Aria engine (faster for temporary data)
CREATE TABLE cache_entries (
    cache_id INT AUTO_INCREMENT PRIMARY KEY,
    cache_key VARCHAR(255) NOT NULL,
    cache_value LONGBLOB NOT NULL,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cache_key (cache_key),
    INDEX idx_expires (expires_at)
) ENGINE=Aria TRANSACTIONAL=1;

-- Create a fast non-transactional log table
CREATE TABLE access_logs (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    url VARCHAR(2048) NOT NULL,
    status_code INT,
    response_time_ms INT,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logged_at (logged_at)
) ENGINE=Aria PAGE_CHECKSUM=1;

-- Create a sequence (MySQL doesn't have these!)
CREATE SEQUENCE order_seq
    START WITH 10000
    INCREMENT BY 1
    MINVALUE 10000
    MAXVALUE 99999;

-- Use it like in Oracle
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (NEXT VALUE FOR order_seq, 1, 150.00);

-- Check current value
SELECT PREVIOUS VALUE FOR order_seq;

-- Create a table with JSON column
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSON
) ENGINE=InnoDB;

-- Insert JSON data
INSERT INTO products (name, attributes) VALUES
    ('Laptop', JSON_OBJECT('cpu', 'Intel i7', 'ram', '16GB', 'storage', '512GB SSD')),
    ('Monitor', '{"size": "27 inch", "resolution": "4K", "ports": ["HDMI", "USB-C"]}');

-- Query JSON with MariaDB-specific functions
SELECT
    name,
    JSON_EXISTS(attributes, '$.cpu') AS has_cpu,
    JSON_VALUE(attributes, '$.resolution') AS resolution,
    attributes
FROM products;

-- Output:
-- name    | has_cpu | resolution | attributes
-- Laptop  | 1       | NULL       | {"cpu":"Intel i7","ram":"16GB","storage":"512GB SSD"}
-- Monitor | 0       | 4K         | {"size":"27 inch","resolution":"4K","ports":["HDMI","USB-C"]}

-- JSON_TABLE (convert JSON to relational rows)
SELECT *
FROM JSON_TABLE(
    '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]',
    '$[*]' COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(50) PATH '$.name'
    )
) AS jt;

# /etc/mysql/mariadb.conf.d/galera.cnf
[galera]
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "dodatech_cluster"
wsrep_cluster_address = "gcomm://192.168.1.10,192.168.1.11,192.168.1.12"
wsrep_node_name = "node1"
wsrep_node_address = "192.168.1.10"
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2

# Start cluster (first node)
galera_new_cluster

# Join other nodes
systemctl start mariadb

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- Find slow queries
SELECT
    digest_text AS query,
    count_star AS executions,
    ROUND(avg_timer_wait / 1000000000, 2) AS avg_ms,
    ROUND(max_timer_wait / 1000000000, 2) AS max_ms,
    sum_rows_examined AS total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text NOT LIKE '%performance_schema%'
ORDER BY avg_timer_wait DESC
LIMIT 10;

-- Output:
-- query                                        | executions | avg_ms | max_ms | total_rows_examined
-- SELECT * FROM products WHERE name LIKE ?     | 150        | 45.20  | 234.10 | 150000
-- INSERT INTO access_logs ...                  | 50000      | 0.50   | 5.20   | 0

-- Analyze a specific query
EXPLAIN FORMAT=JSON
SELECT p.name, COUNT(o.order_id) AS orders
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_id;

-- Galera with innodb_autoinc_lock_mode=2 causes gaps
INSERT INTO products (name) VALUES ('Product A');
-- ID might be 1, next INSERT gets 3, not 2

CREATE SEQUENCE invoice_seq START WITH 100000;

CREATE TABLE invoices (
    invoice_number INT DEFAULT (NEXT VALUE FOR invoice_seq) PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO invoices (customer_id, total) VALUES (1, 250.00);
SELECT * FROM invoices;

-- Create database
CREATE DATABASE marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE marketplace;

-- Products table (InnoDB for transactions)
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(12,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_price (price),
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- High-speed access log (Aria for performance)
CREATE TABLE request_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    method VARCHAR(10),
    path VARCHAR(500),
    status_code INT,
    duration_ms INT,
    ip_address VARCHAR(45),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_path (path(100)),
    INDEX idx_logged_at (logged_at)
) ENGINE=Aria;

-- Insert sample data
INSERT INTO products (name, price, stock_quantity) VALUES
    ('Smart Watch', 199.99, 50),
    ('Wireless Earbuds', 79.99, 200),
    ('Laptop Stand', 34.99, 150);

-- Monitor Galera cluster status
SHOW STATUS LIKE 'wsrep_%';

-- List available storage engines
SHOW ENGINES;

-- Output includes: InnoDB, Aria, MyISAM, MEMORY, CSV

-- Check MariaDB version-specific features
SELECT @@version, @@version_comment;

-- Test sequence (only in MariaDB 10.3+)
CREATE SEQUENCE test_seq;
SELECT NEXT VALUE FOR test_seq;  -- 1
SELECT NEXT VALUE FOR test_seq;  -- 2

-- Use INVISIBLE columns (MariaDB 10.3+)
CREATE TABLE config (
    config_id INT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE,
    config_value TEXT,
    internal_note VARCHAR(500) INVISIBLE
);

-- The INVISIBLE column won't appear in SELECT * queries
INSERT INTO config VALUES (1, 'app_name', 'MyApp', 'Set during installation');
SELECT * FROM config;
-- Only shows: config_id, config_key, config_value