Developer's Catalog

Database

MariaDB

Overview

MariaDB is an open-source relational database management system derived from MySQL. It maintains high compatibility with MySQL while providing more storage engines and extended features, achieving performance and stability suitable for enterprise-level use.

Details

MariaDB development began in 2009 by Michael "Monty" Widenius, the founder of MySQL. After MySQL was acquired by Oracle, it emerged as a community-driven alternative with the following characteristics:

Key Features

• MySQL Compatibility: Existing MySQL applications can be migrated as-is
• Diverse Storage Engines: InnoDB, MyISAM, Aria, ColumnStore, Spider, and more
• High Performance: Parallel replication, query optimization, enhanced indexing
• Security: Authentication plugins, encryption, audit capabilities
• Clustering: Synchronous replication with Galera Cluster
• JSON Support: JSON functions and Virtual Columns support
• Window Functions: SQL standard-compliant analytical features

Architecture

• Multi-layer Structure: Connection management, SQL parsing, storage engines
• Plugin Design: Easy addition of storage engines and features
• Replication: Master-slave, multi-master support
• Partitioning: Horizontal partitioning for large-volume data handling

Advantages and Disadvantages

Advantages

• Easy Migration from MySQL: Complete compatibility allows existing systems to be used as-is
• Rich Storage Engines: Optimal engines can be selected according to use cases
• Active Development: Regular updates and new feature additions
• Enterprise Support: Commercial support provided by MariaDB Corporation
• High Availability: Automatic failover with Galera Cluster
• Transparent Encryption: Data-at-rest encryption capabilities
• Excellent Performance: Faster query processing than MySQL

Disadvantages

• Learning Cost for New Features: Need to understand differences from MySQL-specific features
• Ecosystem: Third-party tool support may not be as extensive as MySQL in some cases
• Memory Usage: May consume more memory than MySQL for certain workloads
• Complex Configuration: Configuration can become complex when using advanced features

Key Links

• Official Website
• Official Documentation
• GitHub
• MariaDB Foundation

Code Examples

Installation & Setup

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

# CentOS/RHEL
sudo yum install mariadb-server mariadb

# Docker
docker run --name mariadb -e MYSQL_ROOT_PASSWORD=mypassword -d mariadb:latest

# Initial setup
sudo mysql_secure_installation

Basic Operations (CRUD)

-- Create database
CREATE DATABASE company_db;
USE company_db;

-- Create table
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO employees (name, email, department, salary) VALUES
('John Doe', '[email protected]', 'Engineering', 75000.00),
('Jane Smith', '[email protected]', 'Marketing', 65000.00);

-- Select data
SELECT * FROM employees WHERE department = 'Engineering';

-- Update data
UPDATE employees SET salary = 80000.00 WHERE id = 1;

-- Delete data
DELETE FROM employees WHERE id = 2;

Data Modeling

-- Table design with foreign key constraints
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget DECIMAL(15,2)
);

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

-- Using JSON columns
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    profile_data JSON,
    preferences JSON
);

INSERT INTO user_profiles (user_id, profile_data) VALUES 
(1, '{"age": 30, "city": "Tokyo", "skills": ["Java", "MySQL"]}');

-- Leveraging Virtual Columns
ALTER TABLE user_profiles 
ADD COLUMN age INT AS (JSON_EXTRACT(profile_data, '$.age')) VIRTUAL;

Indexing & Optimization

-- Create indexes
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_email ON employees(email);
CREATE INDEX idx_name_dept ON employees(name, department);

-- Composite index
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

-- Full-text index
ALTER TABLE employees ADD FULLTEXT(name);
SELECT * FROM employees WHERE MATCH(name) AGAINST('John');

-- Query analysis
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- Performance settings
SET innodb_buffer_pool_size = 2G;
SET query_cache_size = 128M;
SET max_connections = 500;

Practical Examples

-- Transaction processing
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

-- Stored procedure
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT e.*, d.name as department_name 
    FROM employees e 
    JOIN departments d ON e.department_id = d.id 
    WHERE d.name = dept_name;
END //
DELIMITER ;

CALL GetEmployeesByDepartment('Engineering');

-- Replication setup (Master)
SET GLOBAL binlog_format = 'ROW';
CREATE USER 'repl'@'%' IDENTIFIED BY 'replpassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Best Practices

-- Data type optimization
-- Choose appropriate sizes over INT
CREATE TABLE optimized_table (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- Up to 16M
    status TINYINT UNSIGNED,  -- 0-255
    price DECIMAL(8,2),  -- For currency
    created_date DATE,  -- Lighter than TIMESTAMP
    description TEXT  -- For long text
);

-- Partitioning
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Security settings
-- Database encryption
ALTER TABLE sensitive_data ENCRYPTED=YES;

-- User privilege management
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;