MariaDB: The Complete Guide to Open Source Database Development

Introduction

MariaDB is an open-source relational database management system that serves as a drop-in replacement for MySQL. Created by the original developers of MySQL after Oracle’s acquisition of MySQL, MariaDB has evolved into a powerful database system used by major organizations including Wikipedia, WordPress.com, and Google.

In 2026, MariaDB continues to innovate with the release of MariaDB 11.8 LTS, featuring integrated vector search, enhanced JSON support, and temporal tables. This comprehensive guide covers MariaDB fundamentals, helping you build robust database-driven applications.

What is MariaDB?

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system. It was created by Michael Widenius (the original author of MySQL) and is named after his daughter, Maria.

Key Characteristics

Open Source: Released under GPLv2, maintained by MariaDB Foundation
MySQL Compatible: Drop-in replacement for MySQL
Multiple Storage Engines: InnoDB, XtraDB, Aria, ColumnStore, MyRocks
High Performance: Optimized queries and indexing
Scalable: From embedded systems to enterprise workloads
Vector Search: Native support for AI applications (11.7+)

MariaDB vs MySQL

Aspect	MariaDB	MySQL
License	GPLv2	GPLv2 (Community)
Development	Community + MariaDB plc	Oracle
Storage Engines	More (Aria, MyRocks, ColumnStore)	InnoDB, MyISAM
Performance	Generally faster	InnoDB optimized
Features	More built-in features	Core features
Vector Search	Native (11.7+)	Via plugins

MariaDB Data Types

Numeric Types

-- Integer types
CREATE TABLE numbers (
    tiny TINYINT,           -- -128 to 127 (or 0 to 255 unsigned)
    small SMALLINT,         -- -32768 to 32767
    medium MEDIUMINT,       -- -8388608 to 8388607
    integer INT,            -- -2147483648 to 2147483647
    bigint BIGINT,         -- Huge range
    bitint BIT(64)         -- Bit-field type
);

-- Fixed-point types
CREATE TABLE financials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10, 2),  -- Exact precision: 10 digits, 2 decimal
    rate DECIMAL(5, 4)
);

-- Floating-point types
CREATE TABLE measurements (
    temperature FLOAT,      -- 4-byte floating point
    pressure DOUBLE,        -- 8-byte floating point
    reading DECIMAL         -- Can also use DECIMAL for precision
);

String Types

-- Character types
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password CHAR(64),       -- Fixed length
    bio TEXT,                -- Variable length, up to 65535
    notes MEDIUMTEXT,       -- Up to 16MB
    data LONGTEXT           -- Up to 4GB
);

-- Binary types
CREATE TABLE files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255),
    content BLOB,           -- Binary large object
    hash BINARY(32),        -- Fixed-length binary
    uuid BINARY(16)         -- UUID storage
);

-- ENUM and SET
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('pending', 'processing', 'shipped', 'delivered'),
    tags SET('priority', 'express', 'gift', 'fragile')
);

Date and Time Types

-- Date/time types
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_date DATE,            -- YYYY-MM-DD
    event_time TIME,            -- HH:MM:SS
    event_datetime DATETIME,    -- YYYY-MM-DD HH:MM:SS
    created_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Temporal tables (MariaDB 10.11+)
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    PERIOD FOR employment (start_date, end_date)
);

-- Automatic timestamps
CREATE TABLE logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

JSON Type (MariaDB 10.2+)

-- JSON data type
CREATE TABLE configs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    settings JSON
);

INSERT INTO configs (name, settings) VALUES 
('app1', '{"theme": "dark", "notifications": true, "timeout": 30}'),
('app2', '{"theme": "light", "language": "en"}');

-- JSON functions
SELECT JSON_EXTRACT(settings, '$.theme') FROM configs;
SELECT JSON_VALUE(settings, '$.language') FROM configs;
SELECT JSON_KEYS(settings) FROM configs;

-- Update JSON
UPDATE configs 
SET settings = JSON_SET(settings, '$.theme', 'light') 
WHERE name = 'app1';

Vector Type (MariaDB 11.7+)

-- Vector data type for AI/ML applications
CREATE TABLE embeddings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT,
    content TEXT,
    embedding VECTOR(384)  -- 384-dimensional vectors
);

-- Insert vector data
INSERT INTO embeddings (document_id, content, embedding) VALUES
(1, 'Python programming language', '[0.1, 0.2, 0.3, ...]');

MariaDB Installation

Docker Installation

# Start MariaDB container
docker run --name mariadb -e MYSQL_ROOT_PASSWORD=secret \
    -p 3306:3306 -d mariadb:latest

# Start with custom config
docker run --name mariadb \
    -v /my/custom.cnf:/etc/mysql/conf.d/custom.cnf \
    -e MYSQL_ROOT_PASSWORD=secret -d mariadb:latest

# MariaDB with persistent storage
docker run --name mariadb \
    -v mariadb-data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=secret -d mariadb:latest

Linux Installation

# Ubuntu/Debian
sudo apt update
sudo apt install mariadb-server

# Start MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Secure installation
sudo mysql_secure_installation

# RHEL/CentOS
sudo yum install mariadb-server
sudo systemctl start mariadb

Python Integration

import mariadb

# Connect to MariaDB
conn = mariadb.connect(
    user="root",
    password="secret",
    host="localhost",
    database="mydb"
)

cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# Insert data
cursor.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("john_doe", "[email protected]")
)

# Query data
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

conn.commit()
conn.close()

Node.js Integration

const mariadb = require('mariadb');

const pool = mariadb.createPool({
    host: 'localhost',
    user: 'root',
    password: 'secret',
    database: 'mydb',
    connectionLimit: 5
});

async function main() {
    const conn = await pool.getConnection();
    
    // Create table
    await conn.query(`
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            price DECIMAL(10,2) NOT NULL,
            stock INT DEFAULT 0
        )
    `);
    
    // Insert
    const result = await conn.query(
        "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
        ["Laptop", 999.99, 10]
    );
    
    // Query
    const rows = await conn.query("SELECT * FROM products");
    console.log(rows);
    
    conn.end();
}

main();

Basic SQL Operations

Data Definition Language (DDL)

-- Create database
CREATE DATABASE IF NOT EXISTS myapp;
USE myapp;

-- Create table with constraints
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50) DEFAULT 'General',
    salary DECIMAL(10,2) CHECK (salary > 0),
    hire_date DATE DEFAULT (CURRENT_DATE),
    INDEX idx_dept (department),
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- Create table with foreign key
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Alter table
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ADD INDEX idx_salary (salary);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME TO staff;

-- Drop table
DROP TABLE IF EXISTS old_table;

Data Manipulation Language (DML)

-- Insert single row
INSERT INTO employees (name, email, salary, department_id)
VALUES ('John Doe', '[email protected]', 75000.00, 1);

-- Insert multiple rows
INSERT INTO employees (name, email, salary) VALUES 
    ('Jane Smith', '[email protected]', 80000),
    ('Bob Wilson', '[email protected]', 65000),
    ('Alice Brown', '[email protected]', 70000);

-- Insert from select
INSERT INTO employees (name, email, salary, department_id)
SELECT name, email, salary, 1
FROM contractors WHERE status = 'hired';

-- Update rows
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 1;

-- Update with join
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.05
WHERE d.name = 'Engineering';

-- Delete rows
DELETE FROM employees WHERE salary < 50000;

-- Upsert (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO users (id, username, email) VALUES (1, 'john', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- Replace
REPLACE INTO users (id, username, email) VALUES (1, 'john', '[email protected]');

Querying Data

-- Basic SELECT
SELECT * FROM employees;
SELECT name, salary FROM employees;

-- WHERE clause
SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE department_id = 1 AND salary > 60000;

-- ORDER BY
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id, salary DESC;

-- LIMIT and OFFSET
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20;

-- DISTINCT
SELECT DISTINCT department_id FROM employees;

-- LIKE pattern matching
SELECT * FROM employees WHERE name LIKE 'J%';       -- Starts with J
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- IN and BETWEEN
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;

-- Aggregate functions
SELECT 
    COUNT(*) as total,
    AVG(salary) as avg_salary,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary,
    SUM(salary) as total_salary
FROM employees;

-- GROUP BY
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

-- Window functions
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

JOIN Operations

-- Create tables for examples
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10,2)
);

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- INNER JOIN
SELECT o.id, c.name, o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN
SELECT c.name, o.id as order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- RIGHT JOIN
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Multiple joins
SELECT c.name, p.name as product, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

-- Self join
SELECT 
    e1.name as employee,
    e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Storage Engines

InnoDB (Default)

-- InnoDB is the default engine since MariaDB 10.2
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

-- InnoDB features:
-- - ACID transactions
-- - Row-level locking
-- - Foreign key support
-- - Crash recovery
-- - MVCC (Multi-Version Concurrency Control)

Aria Storage Engine

-- Aria: MariaDB's improved MyISAM replacement
CREATE TABLE logs (
    id INT PRIMARY KEY,
    message TEXT
) ENGINE=Aria;

-- Aria features:
-- - Crash-safe tables
-- - Transaction support (since MariaDB 10.4)
-- - Better performance than MyISAM
-- - Full-text indexing support

MyRocks Storage Engine

-- MyRocks: Facebook's RocksDB-based engine
CREATE TABLE analytics (
    id INT PRIMARY KEY,
    data JSON
) ENGINE=MyRocks;

-- MyRocks features:
-- - compression
-- - write optimization
-- - Lower storage footprint
-- - Good for write-heavy workloads

ColumnStore (Analytical)

-- ColumnStore for OLAP workloads
CREATE TABLE factsales (
    id INT PRIMARY KEY,
    date DATE,
    product_id INT,
    customer_id INT,
    amount DECIMAL(10,2)
) ENGINE=ColumnStore;

-- ColumnStore features:
-- - Columnar storage
-- - Distributed processing (MPP)
-- - Data compression
-- - Ideal for analytics

Choosing Storage Engine

-- Transactional (OLTP): InnoDB
CREATE TABLE transactions (...) ENGINE=InnoDB;

-- Analytical (OLAP): ColumnStore
CREATE TABLE analytics (...) ENGINE=ColumnStore;

-- Write-heavy: MyRocks
CREATE TABLE events (...) ENGINE=MyRocks;

-- Logging/archival: Aria
CREATE TABLE logs (...) ENGINE=Aria;

Indexing

Creating Indexes

-- Single column index
CREATE INDEX idx_employees_department ON employees(department_id);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- Spatial index (GIS)
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

Index Usage

-- Check query execution plan
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- Force index usage
SELECT * FROM employees FORCE INDEX (idx_employees_department)
WHERE department_id = 1;

-- Skip index
SELECT * FROM employees IGNORE INDEX (idx_employees_department)
WHERE department_id = 1;

Transactions and Locking

Transaction Control

-- Start transaction
START TRANSACTION;

INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 500);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- Rollback on error
START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
-- If error occurs:
-- ROLLBACK;
COMMIT;

Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current level
SELECT @@transaction_isolation;

Locking

-- Explicit locking
LOCK TABLES employees READ;
LOCK TABLES employees WRITE;

UNLOCK TABLES;

-- Row-level locks
SELECT * FROM employees WHERE id = 1 FOR UPDATE;  -- Exclusive lock
SELECT * FROM employees WHERE id = 1 LOCK IN SHARE MODE;  -- Shared lock

-- Deadlock detection
SHOW ENGINE INNODB STATUS;

Best Practices

Database Design

-- Use appropriate data types
-- VARCHAR vs CHAR: Use VARCHAR for variable length
-- DECIMAL vs FLOAT: Use DECIMAL for precise values
-- INT: Choose appropriate size

-- Primary keys
CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- Or use UUID
    uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID()))
);

-- Normalization
-- 1NF: Atomic values, no repeating groups
-- 2NF: No partial dependencies
-- 3NF: No transitive dependencies

-- Denormalization for reads
-- Add redundant data for frequently accessed joins

Query Optimization

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- Avoid SELECT *
SELECT name, salary FROM employees;

-- Use parameterized queries
PREPARE stmt FROM 'SELECT * FROM employees WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;

-- Use EXISTS instead of IN for correlated subqueries
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

Common Pitfalls

Pitfall 1: Using MyISAM for Critical Data

MyISAM doesn’t support transactions or foreign keys. Use InnoDB.

-- Bad
CREATE TABLE critical_data (...) ENGINE=MyISAM;

-- Good
CREATE TABLE critical_data (...) ENGINE=InnoDB;

Pitfall 2: Not Using Proper Indexes

-- Check for missing indexes
EXPLAIN SELECT * FROM employees WHERE name = 'John';

-- Add appropriate index
CREATE INDEX idx_emp_name ON employees(name);

Pitfall 3: Not Backing Up

# Regular backups
mysqldump -u root -p myapp > backup_$(date +%Y%m%d).sql

# Point-in-time recovery
mariabackup --backup --target-dir=/backup --user=root --password=secret

Resources

Conclusion

MariaDB provides a powerful, flexible, and open-source database solution for modern applications. With its multiple storage engines, excellent MySQL compatibility, and innovative features like vector search, MariaDB is well-suited for a wide range of use cases.

In the next article, we’ll explore MariaDB operations including backup strategies, replication, performance tuning, and high availability configurations.