MariaDB: MySQL Alternative for Self-Hosted Apps

Introduction

MariaDB is a community-developed, commercially-supported fork of MySQL. Created by MySQL’s original developers after Oracle’s acquisition, MariaDB maintains full compatibility with MySQL while adding enhanced features, better performance, and a truly open-source licensing model. For homelabbers, MariaDB is the go-to choice for applications requiring a MySQL-compatible database.

This guide provides everything you need to deploy, manage, and optimize MariaDB in your homelab environment. Whether you’re running WordPress, Nextcloud, or custom applications, you’ll learn professional database administration practices.

What You’ll Learn:

Deploy MariaDB with Docker for single and multi-container setups
Install and configure phpMyAdmin for web-based management
Implement automated backup and recovery strategies
Optimize performance for different workload types
Configure high availability with Galera Cluster
Secure your database with encryption and access controls
Monitor database health and performance
Integrate with popular homelab applications

Prerequisites:

Docker and Docker Compose installed
Basic understanding of SQL and relational databases
Command-line proficiency
Homelab server with minimum 2GB RAM, 20GB storage
Recommended: 4GB+ RAM for production workloads

Part 1: Understanding MariaDB

What is MariaDB?

MariaDB is a drop-in replacement for MySQL, meaning applications written for MySQL work with MariaDB without modification. It was created in 2009 when MySQL’s original lead developer, Michael “Monty” Widenius, forked the project to ensure it remained free and open-source.

Key Characteristics:

MySQL Compatible: Uses same protocols, APIs, and commands
GPL Licensed: Truly open-source, no Oracle commercial licensing
Enhanced Storage Engines: Aria, ColumnStore, MyRocks
Better Performance: Improved query optimizer and thread pool
Active Development: Regular releases with new features
Community-Driven: Transparent development process

Why MariaDB for Homelabs?

Technical Advantages:

Drop-In MySQL Replacement
- Same syntax and commands as MySQL
- Compatible with MySQL client tools
- Easy migration from MySQL
- Existing MySQL knowledge transfers directly
Enhanced Features
- Advanced thread pool for better concurrency
- More storage engines (Aria, ColumnStore, Spider)
- Better replication (Galera Cluster built-in)
- JSON improvements over MySQL
- Virtual columns and temporal data
Performance Improvements
- Faster query optimization
- Better subquery performance
- Improved connection handling
- Optimizations for SSDs
True Open Source
- No licensing concerns
- Transparent development
- Community-focused
- No enterprise-only features

Common Homelab Use Cases:

Web Applications: WordPress, Drupal, Joomla, MediaWiki
Self-Hosted Services: Nextcloud, Bookstack, Monica CRM
Monitoring: Zabbix, Cacti, Observium
Media Management: Kodi, Plex metadata
Development: Local development environments
IoT Data: Home automation data storage

MariaDB vs. MySQL vs. PostgreSQL

Feature	MariaDB	MySQL	PostgreSQL
License	GPL v2	GPL/Commercial	PostgreSQL
MySQL Compatible	100%	Native	No
Storage Engines	12+	9	Table AM
Replication	Built-in Galera	Traditional	Streaming
JSON Support	Yes	Better	Excellent
Performance	Excellent	Excellent	Excellent
Thread Pool	Yes (all editions)	Enterprise only	Process-based
Licensing Cost	Free	Free/Commercial	Free
Best For	MySQL apps	MySQL apps	Complex queries

MariaDB Architecture

Process Structure:

MariaDB Server
├── Connection Handler
│   ├── Thread Pool (handles connections)
│   └── Authentication Layer
├── SQL Layer
│   ├── Parser
│   ├── Optimizer
│   └── Executor
├── Storage Engine Layer
│   ├── InnoDB (default - ACID, transactions)
│   ├── Aria (crash-safe MyISAM replacement)
│   ├── MyRocks (LSM tree, compression)
│   └── ColumnStore (analytics)
└── File System
    ├── Data files (.ibd)
    ├── Redo logs (ib_logfile)
    └── Binary logs (mysql-bin)

How MariaDB Handles Queries:

Client Connection → Thread Pool → Authentication
    ↓
SQL Parser → Syntax Check → Query Optimization
    ↓
Storage Engine API → InnoDB/Aria/etc.
    ↓
Read from Buffer Pool (cache) or Disk
    ↓
Write to Transaction Log → Data Files
    ↓
Return Results to Client

Part 2: Installing MariaDB with Docker

Quick Start Deployment

Create project structure:

mkdir -p ~/mariadb-homelab
cd ~/mariadb-homelab

Create docker-compose.yml:

version: '3.8'

services:
  mariadb:
    image: mariadb:11.2
    container_name: mariadb
    hostname: mariadb
    restart: unless-stopped

    environment:
      # Root password (required)
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-ChangeMe123!}

      # Optional: Create database and user on first run
      # MYSQL_DATABASE: myapp
      # MYSQL_USER: myapp_user
      # MYSQL_PASSWORD: myapp_password

      # Timezone
      TZ: America/New_York

    ports:
      - "3306:3306"

    volumes:
      # Data persistence
      - mariadb-data:/var/lib/mysql

      # Custom configuration
      - ./mariadb.cnf:/etc/mysql/conf.d/custom.cnf:ro

      # Initialization scripts
      - ./init-scripts:/docker-entrypoint-initdb.d:ro

      # Backups
      - ./backups:/backups

    networks:
      - mariadb-network

    # Custom command for performance tuning
    command: >
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --max-connections=150
      --innodb-buffer-pool-size=512M
      --innodb-log-file-size=128M
      --query-cache-size=0
      --query-cache-type=0

    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 30s

    # Resource limits
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
        reservations:
          cpus: '0.5'
          memory: 512M

volumes:
  mariadb-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /mnt/storage/mariadb-data

networks:
  mariadb-network:
    driver: bridge

Create secure credentials (.env):

# Generate strong password
MYSQL_ROOT_PASSWORD=$(openssl rand -base64 32)

# Save to .env
cat > .env <<EOF
MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD
EOF

chmod 600 .env

echo "MariaDB root password: $MYSQL_ROOT_PASSWORD"
echo "Save this password securely!"

Deploy MariaDB:

# Start MariaDB
docker-compose up -d

# View logs
docker-compose logs -f mariadb

# Verify status
docker-compose ps
docker exec mariadb healthcheck.sh --connect --innodb_initialized

Connect to MariaDB:

# Using mysql client in container
docker exec -it mariadb mysql -u root -p

# Once connected:
SHOW DATABASES;
SHOW VARIABLES LIKE 'version%';
STATUS;
EXIT;

Creating Initialization Scripts

Scripts in /docker-entrypoint-initdb.d run automatically on first startup.

Create init-scripts/01-create-databases.sql:

-- Create application databases
CREATE DATABASE IF NOT EXISTS wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS bookstack CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create application users with strong passwords
CREATE USER IF NOT EXISTS 'wordpress_user'@'%' IDENTIFIED BY 'wp_password_change_me';
CREATE USER IF NOT EXISTS 'nextcloud_user'@'%' IDENTIFIED BY 'nc_password_change_me';
CREATE USER IF NOT EXISTS 'bookstack_user'@'%' IDENTIFIED BY 'bs_password_change_me';
CREATE USER IF NOT EXISTS 'zabbix_user'@'%' IDENTIFIED BY 'zabbix_password_change_me';

-- Grant privileges
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress_user'@'%';
GRANT ALL PRIVILEGES ON nextcloud.* TO 'nextcloud_user'@'%';
GRANT ALL PRIVILEGES ON bookstack.* TO 'bookstack_user'@'%';
GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix_user'@'%';

-- Create read-only backup user
CREATE USER IF NOT EXISTS 'backup_user'@'localhost' IDENTIFIED BY 'backup_password_change_me';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

-- Create monitoring user for Prometheus
CREATE USER IF NOT EXISTS 'exporter'@'%' IDENTIFIED BY 'exporter_password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

-- Flush privileges
FLUSH PRIVILEGES;

Create init-scripts/02-configure-settings.sql:

-- Optimize settings for common applications
SET GLOBAL max_allowed_packet=256M;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL sync_binlog=0;

-- Show configuration
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'innodb%';

Rebuild to apply initialization:

docker-compose down
docker volume rm mariadb-data  # Warning: Deletes data!
docker-compose up -d

MariaDB with phpMyAdmin

Extended docker-compose.yml:

version: '3.8'

services:
  mariadb:
    # ... (previous mariadb config)

  phpmyadmin:
    image: phpmyadmin:latest
    container_name: phpmyadmin
    restart: unless-stopped

    environment:
      PMA_HOST: mariadb
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
      UPLOAD_LIMIT: 300M
      MEMORY_LIMIT: 512M
      MAX_EXECUTION_TIME: 300

    ports:
      - "8080:80"

    networks:
      - mariadb-network

    depends_on:
      mariadb:
        condition: service_healthy

volumes:
  mariadb-data:
    driver: local

networks:
  mariadb-network:
    driver: bridge

Start both services:

docker-compose up -d

Access phpMyAdmin at http://your-server-ip:8080

Part 3: Configuration and Optimization

Custom Configuration File

Create mariadb.cnf:

[mysqld]
# Basic Settings
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
tmpdir = /tmp

# Character Set and Collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'

# Connection Settings
max_connections = 150
max_connect_errors = 100000
max_allowed_packet = 256M
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600

# Thread and Connection Handling
thread_cache_size = 50
thread_stack = 256K

# Query Cache (disabled in MariaDB 10.5+, use buffer pool)
query_cache_type = 0
query_cache_size = 0

# Table Cache
table_open_cache = 4000
table_definition_cache = 2000

# InnoDB Settings (adjust for your RAM)
# Rule of thumb: 50-70% of available RAM for buffer pool
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# MyISAM Settings (legacy, avoid if possible)
key_buffer_size = 32M
myisam_sort_buffer_size = 64M

# Temporary Tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Sorting and Grouping
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M

# Binary Logging (for replication and PITR)
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1

# Error Log
log_error = /var/lib/mysql/error.log
log_warnings = 2

# General Log (disable in production)
general_log = 0
general_log_file = /var/lib/mysql/general.log

# Performance Schema
performance_schema = ON
performance_schema_max_table_instances = 400

# Network and Security
bind-address = 0.0.0.0
skip_name_resolve = 1
local_infile = 0

[mysql]
default-character-set = utf8mb4

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4

[mysqldump]
quick
quote-names
max_allowed_packet = 256M

Apply configuration:

docker-compose restart mariadb

Performance Tuning

Calculate optimal buffer pool size:

# Get total RAM
TOTAL_RAM_GB=4

# Calculate buffer pool (60% of RAM)
BUFFER_POOL_MB=$((TOTAL_RAM_GB * 1024 * 60 / 100))

echo "Recommended innodb_buffer_pool_size = ${BUFFER_POOL_MB}M"

Workload-specific tuning:

For WordPress/CMS (many small queries):

[mysqld]
max_connections = 200
innodb_buffer_pool_size = 1G
tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 5000

For Nextcloud (large file operations):

[mysqld]
max_connections = 100
max_allowed_packet = 512M
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M

For Analytics (complex queries):

[mysqld]
max_connections = 50
innodb_buffer_pool_size = 2G
sort_buffer_size = 8M
join_buffer_size = 8M
tmp_table_size = 256M
max_heap_table_size = 256M

Monitoring Performance

Check current settings:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

Analyze buffer pool usage:

SELECT
    FORMAT_BYTES(TOTAL_MEMORY) as total_memory,
    FORMAT_BYTES(ALLOCATED_MEMORY) as allocated_memory,
    ROUND(ALLOCATED_MEMORY / TOTAL_MEMORY * 100, 2) as usage_percent
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/buf_buf_pool';

Check for slow queries:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1000000000000 AS avg_seconds,
    SUM_TIMER_WAIT / 1000000000000 AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Part 4: Backup and Recovery

Backup Strategies

1. Logical Backups with mysqldump:

# Single database
docker exec mariadb mysqldump -u root -p'${MYSQL_ROOT_PASSWORD}' wordpress > wordpress_backup.sql

# All databases
docker exec mariadb mysqldump -u root -p'${MYSQL_ROOT_PASSWORD}' --all-databases > all_databases.sql

# With compression
docker exec mariadb mysqldump -u root -p'${MYSQL_ROOT_PASSWORD}' --all-databases | gzip > all_databases.sql.gz

# Include routines and triggers
docker exec mariadb mysqldump -u root -p'${MYSQL_ROOT_PASSWORD}'     --all-databases     --routines     --triggers     --events     --single-transaction     | gzip > full_backup.sql.gz

2. Automated Backup Script:

#!/bin/bash
# MariaDB Backup Script

BACKUP_DIR="/mnt/backups/mariadb"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
MYSQL_ROOT_PASSWORD="your-password-here"

# Create backup directory
mkdir -p "$BACKUP_DIR"

echo "Starting MariaDB backup at $TIMESTAMP"

# Backup all databases
docker exec mariadb mysqldump     -u root     -p"${MYSQL_ROOT_PASSWORD}"     --all-databases     --single-transaction     --routines     --triggers     --events     --hex-blob     | gzip > "$BACKUP_DIR/all_databases_${TIMESTAMP}.sql.gz"

# Backup individual databases
for DB in wordpress nextcloud bookstack zabbix; do
    echo "Backing up database: $DB"
    docker exec mariadb mysqldump         -u root         -p"${MYSQL_ROOT_PASSWORD}"         --single-transaction         --routines         --triggers         "$DB"         | gzip > "$BACKUP_DIR/${DB}_${TIMESTAMP}.sql.gz"
done

# Calculate checksums
cd "$BACKUP_DIR"
sha256sum *${TIMESTAMP}*.sql.gz > checksums_${TIMESTAMP}.txt

# Cleanup old backups
echo "Removing backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "checksums_*.txt" -mtime +$RETENTION_DAYS -delete

# Show backup sizes
echo "Backup completed!"
ls -lh "$BACKUP_DIR"/*${TIMESTAMP}*

Schedule with cron:

chmod +x backup-mariadb.sh

# Daily backup at 3 AM
crontab -e
0 3 * * * /home/user/mariadb-homelab/backup-mariadb.sh >> /var/log/mariadb-backup.log 2>&1

3. Physical Backup with Mariabackup:

# Install mariabackup in container
docker exec -it mariadb bash
apt-get update && apt-get install -y mariadb-backup

# Create full backup
mariabackup --backup     --target-dir=/backups/full_backup_$(date +%Y%m%d)     --user=root     --password='your-password'

# Prepare backup for restore
mariabackup --prepare --target-dir=/backups/full_backup_20241216

Restore from Backup

Restore single database:

# Gunzip and restore
gunzip < wordpress_backup.sql.gz | docker exec -i mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}' wordpress

# Or restore from plain SQL
docker exec -i mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}' wordpress < wordpress_backup.sql

Restore all databases:

gunzip < all_databases.sql.gz | docker exec -i mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}'

Clean restore (drop and recreate):

# Drop database
docker exec mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}' -e "DROP DATABASE IF EXISTS wordpress;"

# Create fresh database
docker exec mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}' -e "CREATE DATABASE wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Restore
gunzip < wordpress_backup.sql.gz | docker exec -i mariadb mysql -u root -p'${MYSQL_ROOT_PASSWORD}' wordpress

Part 5: High Availability with Galera Cluster

Understanding Galera Cluster

Galera Cluster provides synchronous multi-master replication for MariaDB:

Multi-Master: All nodes can accept writes
Synchronous Replication: No data loss on node failure
Automatic Node Provisioning: New nodes sync automatically
Built into MariaDB: No additional software needed

Galera Cluster Architecture:

MariaDB Node 1 (Primary)
        ↕ (wsrep replication)
MariaDB Node 2 (Primary)
        ↕ (wsrep replication)
MariaDB Node 3 (Primary)
        ↓
All nodes are active and can handle reads/writes

Deploying 3-Node Galera Cluster

Create galera-compose.yml:

version: '3.8'

services:
  mariadb-node1:
    image: mariadb:11.2
    container_name: mariadb-node1
    hostname: mariadb-node1
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    command: >
      --wsrep-on=ON
      --wsrep-provider=/usr/lib/galera/libgalera_smm.so
      --wsrep-cluster-name=homelab_cluster
      --wsrep-cluster-address=gcomm://mariadb-node1,mariadb-node2,mariadb-node3
      --wsrep-node-name=node1
      --wsrep-node-address=mariadb-node1
      --binlog-format=row
      --default-storage-engine=InnoDB
      --innodb-autoinc-lock-mode=2
    ports:
      - "3306:3306"
    volumes:
      - mariadb-node1-data:/var/lib/mysql
    networks:
      galera-network:
        ipv4_address: 172.25.0.11

  mariadb-node2:
    image: mariadb:11.2
    container_name: mariadb-node2
    hostname: mariadb-node2
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    command: >
      --wsrep-on=ON
      --wsrep-provider=/usr/lib/galera/libgalera_smm.so
      --wsrep-cluster-name=homelab_cluster
      --wsrep-cluster-address=gcomm://mariadb-node1,mariadb-node2,mariadb-node3
      --wsrep-node-name=node2
      --wsrep-node-address=mariadb-node2
      --binlog-format=row
      --default-storage-engine=InnoDB
      --innodb-autoinc-lock-mode=2
    ports:
      - "3307:3306"
    volumes:
      - mariadb-node2-data:/var/lib/mysql
    networks:
      galera-network:
        ipv4_address: 172.25.0.12
    depends_on:
      - mariadb-node1

  mariadb-node3:
    image: mariadb:11.2
    container_name: mariadb-node3
    hostname: mariadb-node3
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    command: >
      --wsrep-on=ON
      --wsrep-provider=/usr/lib/galera/libgalera_smm.so
      --wsrep-cluster-name=homelab_cluster
      --wsrep-cluster-address=gcomm://mariadb-node1,mariadb-node2,mariadb-node3
      --wsrep-node-name=node3
      --wsrep-node-address=mariadb-node3
      --binlog-format=row
      --default-storage-engine=InnoDB
      --innodb-autoinc-lock-mode=2
    ports:
      - "3308:3306"
    volumes:
      - mariadb-node3-data:/var/lib/mysql
    networks:
      galera-network:
        ipv4_address: 172.25.0.13
    depends_on:
      - mariadb-node1

volumes:
  mariadb-node1-data:
  mariadb-node2-data:
  mariadb-node3-data:

networks:
  galera-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.25.0.0/16

Deploy cluster:

docker-compose -f galera-compose.yml up -d

Verify cluster status:

-- Connect to any node
docker exec -it mariadb-node1 mysql -u root -p

-- Check cluster size
SHOW STATUS LIKE 'wsrep_cluster_size';

-- Check node status
SHOW STATUS LIKE 'wsrep_local_state_comment';

-- View all cluster status
SHOW STATUS LIKE 'wsrep%';

Part 6: Security Best Practices

User Management

Create application-specific users:

-- Create user with specific host
CREATE USER 'app_user'@'172.20.%.%' IDENTIFIED BY 'strong_password';

-- Grant only necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'172.20.%.%';

-- Create read-only user
CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON app_database.* TO 'readonly'@'%';

-- Flush privileges
FLUSH PRIVILEGES;

Audit users and permissions:

-- List all users
SELECT User, Host FROM mysql.user;

-- Show user privileges
SHOW GRANTS FOR 'app_user'@'172.20.%.%';

-- Remove user
DROP USER 'old_user'@'localhost';

Securing MariaDB

1. Disable remote root login:

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

2. Remove anonymous users:

DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

3. Remove test database:

DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;

4. Enable SSL/TLS:

# Generate certificates
openssl req -x509 -newkey rsa:4096 -keyout server-key.pem -out server-cert.pem -days 365 -nodes -subj "/CN=mariadb.homelab.local"
openssl req -x509 -newkey rsa:4096 -keyout client-key.pem -out client-cert.pem -days 365 -nodes -subj "/CN=client"

# Copy to container
docker cp server-cert.pem mariadb:/etc/mysql/ssl/
docker cp server-key.pem mariadb:/etc/mysql/ssl/
docker cp client-cert.pem mariadb:/etc/mysql/ssl/
docker cp client-key.pem mariadb:/etc/mysql/ssl/

Add to mariadb.cnf:

[mysqld]
ssl-ca=/etc/mysql/ssl/server-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON

Part 7: Monitoring

Prometheus mysqld_exporter

Add to docker-compose.yml:

services:
  mysqld-exporter:
    image: prom/mysqld-exporter:latest
    container_name: mysqld-exporter
    environment:
      DATA_SOURCE_NAME: "exporter:exporter_password@(mariadb:3306)/"
    ports:
      - "9104:9104"
    networks:
      - mariadb-network
    depends_on:
      - mariadb

Grafana Dashboard:

Import Dashboard ID: 7362 (MySQL Overview)

Conclusion

You now have a production-ready MariaDB deployment with comprehensive management, backup, and monitoring capabilities.

Key Takeaways:

Compatibility: Drop-in MySQL replacement with enhanced features
Performance: Optimized for modern hardware and workloads
High Availability: Galera Cluster for multi-master replication
Management: phpMyAdmin for easy administration
Security: Proper user management and encryption

Next Steps:

Integrate with homelab applications
Set up automated backup verification
Implement connection pooling with ProxySQL
Explore ColumnStore for analytics workloads

Resources:

Happy database administration!

Guide Version: 1.0 Last Updated: December 2024 Target Audience: Homelab administrators, web developers Difficulty Level: Intermediate Estimated Time: 50 minutes