MariaDB Database — Complete Technical Guide (Architecture, Internals, Advanced Usage)

MariaDB is a high-performance, open-source relational database management system (RDBMS) designed as a drop-in replacement for MySQL while offering better performance, stronger security, more storage engines, and advanced features.

---

1. What Is MariaDB?

MariaDB was created by the original developers of MySQL after Oracle acquired MySQL.
It is:

• Fully open source (GPL)
• MySQL-compatible
• Faster and more extensible
• Used by Wikipedia, Google, Red Hat, Debian

---

2. MariaDB Architecture (High-Level)

Client
  ↓
Connection Manager
  ↓
Query Parser
  ↓
Optimizer
  ↓
Query Execution Engine
  ↓
Storage Engine (InnoDB, Aria, MyISAM, etc.)
  ↓
Disk / Memory

---

3. Client–Server Architecture

MariaDB uses a client-server model:

• Client sends SQL queries
• Server parses, optimizes, executes
• Results returned to client

Supported clients:

• mysql CLI
• PHP, Python, Java, Node.js
• GUI tools (DBeaver, HeidiSQL)

---

4. Connection Manager (Internals)

Responsibilities:

• Authentication
• Thread creation
• Connection pooling
• Session variables

SHOW PROCESSLIST;

Each connection runs in a thread.

---

5. Query Parsing (SQL Parser)

The parser:

• Validates SQL syntax
• Converts SQL into a parse tree

Example:

SELECT name FROM users WHERE age > 18;

If syntax is invalid → error before execution.

---

6. Query Optimizer (Very Important)

Optimizer decides:

• Index usage
• Join order
• Execution strategy

EXPLAIN SELECT * FROM orders WHERE user_id = 10;

Output includes:

• access type
• index used
• rows scanned

---

7. Query Execution Engine

• Executes optimized query plan
• Communicates with storage engines
• Manages result sets

---

8. Storage Engine Architecture

MariaDB is pluggable-storage-engine based.

Each table uses one engine.

SHOW ENGINES;

---

9. Core Storage Engines

9.1 InnoDB (Default)

Features:

• ACID transactions
• MVCC
• Row-level locking
• Foreign keys

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
) ENGINE=InnoDB;

---

9.2 Aria (Crash-Safe MyISAM Replacement)

• Faster than MyISAM
• Crash recovery
• Used for internal temp tables

---

9.3 MyISAM

• Table-level locking
• No transactions
• Fast reads

ENGINE=MyISAM;

---

9.4 Memory Engine

• Data stored in RAM
• Extremely fast
• Data lost on restart

ENGINE=MEMORY;

---

10. Data Files and Internals

InnoDB uses:

• .ibd files (tablespace)
• Buffer Pool
• Redo Logs
• Undo Logs

Key memory areas:

• Buffer Pool
• Query Cache (deprecated)
• Thread Cache

---

11. SQL Data Types (Complete)

Numeric

INT, BIGINT, SMALLINT, FLOAT, DOUBLE, DECIMAL

String

CHAR, VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT

Date & Time

DATE, TIME, DATETIME, TIMESTAMP, YEAR

JSON

JSON

Binary

BLOB, LONGBLOB

---

12. Database & Table Management

Create Database

CREATE DATABASE company;

Use Database

USE company;

Create Table

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);

---

13. CRUD Operations

INSERT

INSERT INTO employees (name, salary)
VALUES ('Ali', 1200.50);

SELECT

SELECT * FROM employees;

UPDATE

UPDATE employees SET salary = 1500 WHERE id = 1;

DELETE

DELETE FROM employees WHERE id = 1;

---

14. WHERE, ORDER BY, LIMIT

SELECT * FROM employees
WHERE salary > 1000
ORDER BY salary DESC
LIMIT 5;

---

15. Indexing (Internals & Usage)

Create Index

CREATE INDEX idx_salary ON employees(salary);

Types:

• PRIMARY
• UNIQUE
• FULLTEXT
• SPATIAL

Indexes reduce scan time dramatically.

---

16. Joins (All Types)

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Types:

• INNER
• LEFT
• RIGHT
• CROSS

---

17. Transactions & ACID

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ACID:

• Atomicity
• Consistency
• Isolation
• Durability

---

18. Isolation Levels

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

---

19. Views

CREATE VIEW high_salary AS
SELECT * FROM employees WHERE salary > 2000;

---

20. Stored Procedures

DELIMITER //
CREATE PROCEDURE GetEmployees()
BEGIN
  SELECT * FROM employees;
END //
DELIMITER ;

Call:

CALL GetEmployees();

---

21. Functions

CREATE FUNCTION bonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
RETURN salary * 0.10;

---

22. Triggers

CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = IFNULL(NEW.salary, 1000);

---

23. Events (Scheduler)

CREATE EVENT cleanup
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

---

24. JSON Support

INSERT INTO users (data)
VALUES ('{"age":25,"city":"Kabul"}');

Query JSON:

SELECT data->>'$.city' FROM users;

---

25. Full-Text Search

CREATE FULLTEXT INDEX idx_text ON posts(content);

SELECT * FROM posts
WHERE MATCH(content) AGAINST('database');

---

26. Security & Users

Create User

CREATE USER 'app'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

GRANT SELECT, INSERT ON company.* TO 'app'@'localhost';

---

27. Backup & Restore

Backup

mysqldump -u root company > backup.sql

Restore

mysql -u root company < backup.sql

---

28. Replication (Master–Slave)

• Asynchronous
• Semi-synchronous
• Multi-source

Used for:

• Scaling reads
• High availability

---

29. Galera Cluster (Advanced)

MariaDB supports multi-master clustering:

• Synchronous replication
• Automatic failover
• No data loss

Used in production at scale.

---

30. Performance Optimization

Key techniques:

• Proper indexing
• EXPLAIN plans
• Query cache tuning
• Buffer pool sizing

SHOW STATUS LIKE 'Threads_connected';

---

31. MariaDB vs MySQL

Feature	MariaDB	MySQL
License	GPL	Oracle
Storage Engines	More	Fewer
Performance	Faster	Slower
Community	Strong	Controlled

---

32. When to Use MariaDB

Use MariaDB when you need:

• High performance
• Open-source purity
• Advanced SQL features
• Scalability
• MySQL compatibility

---

Conclusion

MariaDB is not just a MySQL replacement — it is a powerful, enterprise-grade database system with:

• Deep internal architecture
• Rich SQL syntax
• Strong performance
• Advanced clustering & replication

If you master MariaDB, you master real-world backend databases.