Deploying a MariaDB Database

Introduction

MariaDB is a community-developed, commercially supported fork of MySQL, created by the original developers of MySQL after concerns about Oracle’s acquisition of MySQL. Since its creation in 2009, MariaDB has evolved into a powerful, feature-rich database that maintains full compatibility with MySQL while offering enhanced performance and additional functionality.

MariaDB is distinguished by:

Full MySQL Compatibility: Drop-in replacement for MySQL with compatible APIs and protocols
Enhanced Performance: Improved query optimizer, better multi-threading, and faster complex queries
Advanced Features: Native JSON support, temporal tables, virtual columns, and more storage engines
Open Source Commitment: True open-source development with GPL, LGPL, and BSD licenses
Active Development: Regular releases with new features, performance improvements, and security patches
Storage Engine Diversity: Support for Aria, ColumnStore, InnoDB, MyRocks, Spider, and more
Enterprise Features: Built-in replication, clustering (Galera), thread pooling, and advanced security
Compatibility: Works seamlessly with existing MySQL applications and tools

MariaDB is ideal for web applications, content management systems, analytics platforms, and any application requiring a reliable relational database with MySQL compatibility.

This comprehensive guide walks you through deploying MariaDB on Klutch.sh using Docker, including detailed installation steps, sample configurations, and production-ready best practices for persistent storage and security.

Prerequisites

Before you begin, ensure you have the following:

A Klutch.sh account
A GitHub account with a repository for your MariaDB project
Docker installed locally for testing (optional but recommended)
Basic understanding of Docker and relational databases

Installation and Setup

Step 1: Create Your Project Directory

First, create a new directory for your MariaDB deployment project:

mkdir mariadb-klutch
cd mariadb-klutch
git init

Step 2: Create the Dockerfile

Create a Dockerfile in your project root directory. This will define your MariaDB container configuration:

FROM mariadb:11.4

# Set default environment variables
# These can be overridden in the Klutch.sh dashboard
ENV MYSQL_DATABASE=mydb
ENV MYSQL_USER=myuser
ENV MYSQL_PASSWORD=mypassword
ENV MYSQL_ROOT_PASSWORD=rootpassword

# Expose the default MariaDB port
EXPOSE 3306

# Optional: Copy custom MariaDB configuration
# COPY ./mariadb.cnf /etc/mysql/conf.d/custom.cnf

# Optional: Copy initialization scripts
# SQL and shell scripts in /docker-entrypoint-initdb.d/
# will be executed automatically on first startup
# COPY ./init.sql /docker-entrypoint-initdb.d/
# COPY ./init.sh /docker-entrypoint-initdb.d/

Note: MariaDB 11.4 is the latest long-term support (LTS) version with improved performance and new features.

Step 3: (Optional) Create Initialization Scripts

You can create SQL scripts that will run automatically when the database is first initialized. Create a file named init.sql:

-- init.sql - Database initialization script
-- This script runs automatically on first startup

-- Create additional databases if needed
CREATE DATABASE IF NOT EXISTS app_production;
CREATE DATABASE IF NOT EXISTS app_staging;

-- Switch to the main database
USE mydb;

-- Create a sample table with MariaDB features
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    profile JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create a table with virtual columns (MariaDB feature)
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    tax_rate DECIMAL(4, 2) DEFAULT 0.08,
    tax_amount DECIMAL(10, 2) AS (amount * tax_rate) VIRTUAL,
    total_amount DECIMAL(10, 2) AS (amount + (amount * tax_rate)) STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data (optional)
INSERT INTO users (username, email, profile) VALUES
    ('admin', 'admin@example.com', '{"role": "admin", "verified": true}'),
    ('testuser', 'test@example.com', '{"role": "user", "verified": false}')
ON DUPLICATE KEY UPDATE username=username;

Step 4: (Optional) Create Custom MariaDB Configuration

For production deployments, create a custom mariadb.cnf file with performance tuning settings:

# mariadb.cnf - Custom MariaDB Configuration

[mysqld]
# Performance tuning
max_connections = 200
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Character set configuration
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Security settings
local_infile = 0

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# MariaDB specific optimizations
aria_pagecache_buffer_size = 128M
optimizer_switch = 'mrr=on,mrr_sort_keys=on,optimize_join_buffer_size=on'

# Binary logging for replication (optional)
# server-id = 1
# log_bin = /var/log/mysql/mariadb-bin.log
# binlog_expire_logs_seconds = 604800

If you create custom configuration or initialization files, update your Dockerfile:

FROM mariadb:11.4

# Copy custom configuration and initialization scripts
COPY ./mariadb.cnf /etc/mysql/conf.d/custom.cnf
COPY ./init.sql /docker-entrypoint-initdb.d/

ENV MYSQL_DATABASE=mydb
ENV MYSQL_USER=myuser
ENV MYSQL_PASSWORD=mypassword
ENV MYSQL_ROOT_PASSWORD=rootpassword

EXPOSE 3306

Step 5: Test Locally (Optional)

Before deploying to Klutch.sh, you can test your MariaDB setup locally:

# Build the Docker image
docker build -t my-mariadb .

# Run the container
docker run -d \
  --name mariadb-test \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=supersecret \
  my-mariadb

# Wait a moment for MariaDB to start, then connect
docker exec -it mariadb-test mariadb -uroot -psupersecret

# Inside MariaDB shell:
# SHOW DATABASES;
# USE mydb;
# SHOW TABLES;
# SELECT * FROM users;
# EXIT;

# Stop and remove the test container when done
docker stop mariadb-test
docker rm mariadb-test

Step 6: Push to GitHub

Commit your Dockerfile and any configuration files to your GitHub repository:

git add Dockerfile init.sql mariadb.cnf
git commit -m "Add MariaDB Dockerfile and configuration"
git remote add origin https://github.com/yourusername/mariadb-klutch.git
git push -u origin main

Connecting to MariaDB

Once deployed, you can connect to your MariaDB database from any application using a connection string. Since Klutch.sh routes TCP traffic through port 8000, use the following format:

Connection String

mysql://myuser:mypassword@example-app.klutch.sh:8000/mydb

Or using the MariaDB protocol:

mariadb://myuser:mypassword@example-app.klutch.sh:8000/mydb

Replace:

myuser with your database username
mypassword with your database password
example-app.klutch.sh with your actual Klutch.sh app URL
mydb with your database name

Note: MariaDB is fully MySQL-compatible, so you can use MySQL clients and drivers to connect.

Example Connection Code

Node.js (using mysql2):

const mysql = require('mysql2/promise');

async function connectMariaDB() {
  const connection = await mysql.createConnection({
    host: 'example-app.klutch.sh',
    port: 8000,
    user: 'myuser',
    password: 'mypassword',
    database: 'mydb'
  });

  const [rows] = await connection.execute('SELECT VERSION() as version');
  console.log('MariaDB Version:', rows[0].version);

  const [users] = await connection.execute('SELECT * FROM users');
  console.log('Users:', users);

  await connection.end();
}

connectMariaDB();

Python (using mariadb connector):

import mariadb

# Connect to MariaDB
conn = mariadb.connect(
    host="example-app.klutch.sh",
    port=8000,
    user="myuser",
    password="mypassword",
    database="mydb"
)

cursor = conn.cursor()

# Execute a query
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

Python (using mysql-connector-python - also compatible):

import mysql.connector

conn = mysql.connector.connect(
    host="example-app.klutch.sh",
    port=8000,
    user="myuser",
    password="mypassword",
    database="mydb"
)

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()

for user in users:
    print(f"User: {user['username']}, Email: {user['email']}")

cursor.close()
conn.close()

PHP (using mysqli):

<?php
$mysqli = new mysqli(
    "example-app.klutch.sh",
    "myuser",
    "mypassword",
    "mydb",
    8000
);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$result = $mysqli->query("SELECT * FROM users");

while ($row = $result->fetch_assoc()) {
    echo "Username: " . $row['username'] . "\n";
}

$mysqli->close();
?>

Java (using MariaDB JDBC Driver):

import java.sql.*;

public class MariaDBConnection {
    public static void main(String[] args) {
        String url = "jdbc:mariadb://example-app.klutch.sh:8000/mydb";
        String user = "myuser";
        String password = "mypassword";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");

            while (rs.next()) {
                System.out.println("Username: " + rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Deploying to Klutch.sh

Now that your MariaDB project is ready and pushed to GitHub, follow these steps to deploy it on Klutch.sh with persistent storage.

Deployment Steps

Log in to Klutch.sh

Navigate to klutch.sh/app and sign in to your account.
Create a New Project

Go to Create Project and give your project a meaningful name (e.g., “MariaDB Database”).
Create a New App

Navigate to Create App and configure the following settings:
Select Your Repository
- Choose GitHub as your Git source
- Select the repository containing your Dockerfile
- Choose the branch you want to deploy (usually main or master)
Configure Traffic Type
- Traffic Type: Select TCP (MariaDB requires TCP traffic for database connections)
- Internal Port: Set to 3306 (the default MariaDB port that your container listens on)
Set Environment Variables

Add the following environment variables for your MariaDB configuration:
- MYSQL_ROOT_PASSWORD: A strong password for the root user (required - use a secure password generator)
- MYSQL_DATABASE: The name of your default database (e.g., mydb)
- MYSQL_USER: A non-root database username (e.g., myuser)
- MYSQL_PASSWORD: A strong password for your database user
Security Note: Always use strong, unique passwords for production databases. MariaDB uses the same environment variable names as MySQL for compatibility.
Attach a Persistent Volume

This is critical for ensuring your database data persists across deployments and restarts:
- In the Volumes section, click “Add Volume”
- Mount Path: Enter /var/lib/mysql (this is where MariaDB stores all database files)
- Size: Choose an appropriate size based on your expected data volume (e.g., 10GB, 20GB, 50GB)
Important: MariaDB requires persistent storage to maintain your data between container restarts.
Configure Additional Settings
- Region: Select the region closest to your users for optimal latency
- Compute Resources: Choose CPU and memory based on your workload (minimum 512MB RAM recommended, 1GB+ for production)
- Instances: Start with 1 instance (single instance deployment for simplicity)
Deploy Your Database

Click “Create” to start the deployment. Klutch.sh will:
- Automatically detect your Dockerfile in the repository root
- Build the Docker image
- Attach the persistent volume
- Start your MariaDB container
- Execute any initialization scripts in /docker-entrypoint-initdb.d/
- Assign a URL for external connections
Access Your Database

Once deployment is complete, you’ll receive a URL like example-app.klutch.sh. You can connect to your MariaDB database using this URL on port 8000:
```
mysql://myuser:mypassword@example-app.klutch.sh:8000/mydb
```

Production Best Practices

Security Recommendations

Strong Passwords: Use complex, randomly generated passwords for both root and application users
Least Privilege: Create database users with only the permissions they need
Environment Variables: Store all credentials as environment variables in Klutch.sh, never in code
Regular Updates: Keep your MariaDB version up to date with security patches
Disable Remote Root: In production, consider creating dedicated application users instead of using root
SSL/TLS: For sensitive data, configure SSL/TLS encryption for database connections

Performance Optimization

InnoDB Buffer Pool: Allocate 70-80% of available RAM to innodb_buffer_pool_size
Aria Cache: Configure aria_pagecache_buffer_size for Aria storage engine performance
Query Optimization: Use EXPLAIN and ANALYZE to optimize queries
Proper Indexing: Create indexes on frequently queried columns
Connection Pooling: Use connection pooling in your application
Thread Pool: Enable thread pool plugin for better handling of many concurrent connections

MariaDB-Specific Features

Take advantage of MariaDB’s enhanced features:

Virtual Columns: Computed columns that don’t take up storage
Temporal Tables: System-versioned tables for historical data tracking
JSON Support: Native JSON data type with indexing support
Advanced Optimizer: Better query optimization than MySQL
Multiple Storage Engines: Choose the right engine for your workload (InnoDB, Aria, ColumnStore)

Backup Strategy

Implement a comprehensive backup strategy:

# Backup using mariadb-dump (compatible with mysqldump)
mariadb-dump -h example-app.klutch.sh -P 8000 -u myuser -p mydb > backup.sql

# Restore from backup
mariadb -h example-app.klutch.sh -P 8000 -u myuser -p mydb < backup.sql

Consider:

Automated daily backups
Multiple retention periods (daily, weekly, monthly)
Offsite backup storage
Regular restore testing
Point-in-time recovery using binary logs

Monitoring

Monitor your MariaDB database for:

Query performance and slow queries
Connection count and connection errors
Disk usage and I/O patterns
CPU and memory utilization
InnoDB buffer pool efficiency
Replication lag (if using replication)
Table sizes and growth rates

Troubleshooting

Cannot Connect to Database

Verify you’re using the correct connection string with port 8000
Ensure environment variables (username, password) are set correctly in Klutch.sh
Check that the internal port is set to 3306
Verify that TCP traffic is selected
Try using both MySQL and MariaDB clients (both should work)

Database Not Persisting Data

Verify the persistent volume is correctly attached at /var/lib/mysql
Check that the volume has sufficient space
Ensure initialization scripts ran successfully on first startup

Performance Issues

Review slow query log to identify problematic queries
Check if innodb_buffer_pool_size is properly configured
Ensure proper indexes exist on frequently queried columns
Consider using MariaDB’s optimizer features
Increase compute resources if needed

Authentication Errors

Verify MYSQL_ROOT_PASSWORD is set in environment variables
Ensure username and password match configuration
Check that the user has been created during initialization
MariaDB uses same authentication as MySQL

Migration from MySQL

If migrating from MySQL:

MariaDB is a drop-in replacement for most MySQL versions
Export data from MySQL using mysqldump
Import into MariaDB using mariadb or mysql client
Test thoroughly as some MySQL features may behave slightly differently

Additional Resources

Conclusion

Deploying MariaDB to Klutch.sh with Docker provides a powerful, MySQL-compatible relational database with enhanced performance and modern features. By following this guide, you’ve set up a MariaDB database that’s secure, performant, and ready to power your applications with reliable, enterprise-grade data management capabilities.