Documentation

MariaDB Managed Database

MariaDB is a community-developed, commercially supported fork of MySQL. DanubeData offers fully managed MariaDB databases with automatic backups, high availability, and easy scaling.

Overview

MariaDB provides enhanced performance, improved replication, and additional storage engines compared to MySQL. It's fully compatible with MySQL, making migration straightforward.

Why Choose MariaDB?

  • MySQL Compatibility: Drop-in replacement for MySQL with better performance
  • Open Source: Truly open-source with no vendor lock-in
  • Advanced Features: Improved optimizer, parallel replication, and more storage engines
  • Active Development: Regular updates and security patches
  • Enterprise Features: Features like Galera Cluster, Thread Pool, and more

Supported Versions

DanubeData supports the following MariaDB versions:

  • MariaDB 11.4 (Latest LTS - Recommended)
  • MariaDB 10.11 (LTS)
  • MariaDB 10.6 (LTS)
  • MariaDB 10.5 (LTS - EOL soon)

Recommendation: Use MariaDB 11.4 for new databases. It offers the best performance and latest features.

Creating a MariaDB Database

Via Dashboard

  1. Navigate to Databases in the main menu
  2. Click Create Database
  3. Select MariaDB as the engine
  4. Choose your preferred version
  5. Select a resource profile (see below)
  6. Choose a data center location
  7. Configure optional settings:
    • Database name
    • User credentials
    • Enable replicas
    • Backup schedule
  8. Click Create Database

Your database will be provisioned within 3-5 minutes.

Configuration Options

Resource Profiles

MariaDB databases are available in multiple resource profiles:

Profile vCPUs RAM Storage Max Connections Price/Month
DB-Small 2 4 GB 80 GB 100 $40
DB-Medium 4 8 GB 160 GB 250 $80
DB-Large 4 16 GB 320 GB 500 $160
DB-XLarge 8 32 GB 640 GB 1000 $320
DB-2XLarge 16 64 GB 1.2 TB 2000 $640

High Availability (Replicas)

Enable high availability by creating read replicas:

  • Primary-Replica Setup: Automatic failover
  • Asynchronous Replication: Low-latency replication
  • Read Scaling: Distribute read queries across replicas
  • Automatic Promotion: Replica promotion on primary failure

Connecting to MariaDB

Connection Details

After creation, you'll receive connection details:

Host: db-mariadb-123456.danubedata.com
Port: 3306
Database: defaultdb
User: doadmin
Password: [secure_password]

Connection String

mysql -h db-mariadb-123456.danubedata.com -P 3306 -u doadmin -p defaultdb

SSL/TLS Connection

All connections support SSL/TLS encryption for security.

Modern MySQL/MariaDB clients (5.7.11+):

mysql -h db-mariadb-123456.danubedata.com \
  -P 3306 \
  -u doadmin \
  -p \
  --ssl-mode=REQUIRED \
  --ssl-verify-server-cert=OFF \
  defaultdb

Older MySQL/MariaDB clients (pre-5.7.11):

mysql -h db-mariadb-123456.danubedata.com \
  -P 3306 \
  -u doadmin \
  -p \
  --ssl \
  --ssl-verify-server-cert=0 \
  defaultdb

Check your client version:

mysql --version

Connection from Application

PHP (PDO)

<?php
$host = 'db-mariadb-123456.danubedata.com';
$port = 3306;
$dbname = 'defaultdb';
$user = 'doadmin';
$password = 'your_password';

$dsn = "mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_SSL_CA       => '/path/to/ca-certificate.crt',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
];

try {
    $pdo = new PDO($dsn, $user, $password, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Python (PyMySQL)

import pymysql

connection = pymysql.connect(
    host='db-mariadb-123456.danubedata.com',
    port=3306,
    user='doadmin',
    password='your_password',
    database='defaultdb',
    charset='utf8mb4',
    ssl={'ca': '/path/to/ca-certificate.crt'},
    ssl_verify_cert=True,
    ssl_verify_identity=True
)

Node.js (mysql2)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'db-mariadb-123456.danubedata.com',
  port: 3306,
  user: 'doadmin',
  password: 'your_password',
  database: 'defaultdb',
  ssl: {
    ca: fs.readFileSync('/path/to/ca-certificate.crt')
  }
});

Database Management

Creating Additional Databases

You can create multiple databases within a single MariaDB instance:

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Creating Users

Create additional database users with specific permissions:

-- Create user
CREATE USER 'myapp_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myapp_user'@'%';

-- Flush privileges
FLUSH PRIVILEGES;

User Permissions

Grant appropriate permissions based on requirements:

-- Read-only user
GRANT SELECT ON myapp.* TO 'readonly_user'@'%';

-- Read-write user
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'readwrite_user'@'%';

-- Full access (including schema changes)
GRANT ALL PRIVILEGES ON myapp.* TO 'admin_user'@'%';

-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'%';

MariaDB-Specific Features

Storage Engines

MariaDB supports multiple storage engines:

InnoDB (Default)

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

Aria (Crash-safe MyISAM replacement)

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=Aria;

ColumnStore (Analytics)

-- Create a columnstore table for analytics
CREATE TABLE analytics_events (
    event_id BIGINT,
    user_id INT,
    event_type VARCHAR(50),
    event_data JSON,
    created_at TIMESTAMP
) ENGINE=ColumnStore;

Sequences

MariaDB has native sequence support:

-- Create a sequence
CREATE SEQUENCE user_id_seq START WITH 1000 INCREMENT BY 1;

-- Use in INSERT
INSERT INTO users (id, name) VALUES (NEXT VALUE FOR user_id_seq, 'John Doe');

-- Get current value
SELECT LASTVAL(user_id_seq);

JSON Functions

MariaDB provides powerful JSON functions:

-- Create table with JSON column
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON
);

-- Insert JSON data
INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": 16}');

-- Query JSON data
SELECT name, JSON_VALUE(attributes, '$.brand') as brand
FROM products
WHERE JSON_VALUE(attributes, '$.ram') > 8;

-- Update JSON data
UPDATE products
SET attributes = JSON_SET(attributes, '$.ram', 32)
WHERE id = 1;

Virtual Columns

MariaDB supports virtual (computed) columns:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quantity INT,
    price DECIMAL(10, 2),
    total DECIMAL(10, 2) AS (quantity * price) VIRTUAL,
    INDEX (total)
);

Performance Optimization

Query Cache

MariaDB includes query cache (deprecated in MySQL 8.0):

-- Enable query cache (configured by default)
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 268435456; -- 256 MB

-- Check query cache status
SHOW STATUS LIKE 'Qcache%';

Note: Query cache is most effective for read-heavy workloads with repetitive queries.

Indexes

Create appropriate indexes for your queries:

-- Single column index
CREATE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name_email ON users(name, email);

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

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

-- Analyze index usage
SHOW INDEX FROM users;

Query Optimization

Use EXPLAIN to analyze query performance:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Connection Pooling

Implement connection pooling in your application:

  • Reuse database connections
  • Reduce connection overhead
  • Improve application performance
  • Handle connection failures gracefully

Monitoring and Maintenance

Performance Monitoring

Monitor database performance via the dashboard:

  • CPU Usage: Track CPU utilization
  • Memory Usage: Monitor RAM consumption
  • Disk I/O: Watch read/write operations
  • Connections: Active and total connections
  • Query Performance: Slow query log

Slow Query Log

Identify slow queries for optimization:

-- Enable slow query log
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; -- Queries taking > 2 seconds

-- View slow queries in dashboard or via CLI
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

Database Size

Check database and table sizes:

-- Database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- Table sizes
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY (data_length + index_length) DESC;

Backups and Recovery

Automatic Backups

All MariaDB databases include automatic daily backups:

  • Daily Backups: Taken during off-peak hours
  • Retention: 7 days (configurable up to 30 days)
  • Point-in-Time Recovery: Restore to any point within retention period
  • Backup Storage: Stored in separate availability zones

Manual Backups

Create on-demand backups:

  1. Navigate to your database
  2. Click Backups tab
  3. Click Create Backup
  4. Provide a descriptive name
  5. Backup will be created within minutes

Restoring from Backup

Restore your database from a backup:

  1. Go to Backups tab
  2. Select the backup to restore
  3. Click Restore
  4. Choose restoration method:
    • Restore to existing database: Overwrites current data
    • Restore to new database: Creates a new database instance

Warning: Restoring to an existing database will overwrite all current data. This action cannot be undone.

Exporting Data

Export your database using mysqldump:

# Export entire database
mysqldump -h db-mariadb-123456.danubedata.com \
  -u doadmin -p \
  --ssl-mode=REQUIRED \
  --single-transaction \
  --quick \
  defaultdb > backup.sql

# Export specific tables
mysqldump -h db-mariadb-123456.danubedata.com \
  -u doadmin -p \
  --ssl-mode=REQUIRED \
  defaultdb users orders > tables_backup.sql

# Export with compression
mysqldump -h db-mariadb-123456.danubedata.com \
  -u doadmin -p \
  --ssl-mode=REQUIRED \
  defaultdb | gzip > backup.sql.gz

Migration

Migrating from MySQL to MariaDB

MariaDB is compatible with MySQL, making migration straightforward:

  1. Export from MySQL:

    mysqldump -h old-mysql-server -u root -p mydatabase > mysql_dump.sql

  2. Import to MariaDB:

    mysql -h db-mariadb-123456.danubedata.com -u doadmin -p defaultdb < mysql_dump.sql

  3. Verify Data:

    -- Check table count
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'defaultdb';

-- Check row counts
SELECT table_name, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'defaultdb';

Best Practices for Migration

  • Test First: Migrate to a test database first
  • Check Compatibility: Verify all features work in MariaDB
  • Update Connection Strings: Update application configuration
  • Monitor Performance: Watch for performance differences
  • Backup Before Migration: Always backup before starting

Security Best Practices

User Security

-- Use strong passwords
CREATE USER 'myuser'@'%' IDENTIFIED BY 'Very$trong@P@ssw0rd!2024';

-- Limit user access by IP
CREATE USER 'admin'@'203.0.113.0/24' IDENTIFIED BY 'password';

-- Remove anonymous users (done by default)
DELETE FROM mysql.user WHERE User='';

-- Remove test database (done by default)
DROP DATABASE IF EXISTS test;

SSL/TLS Connections

All database instances support encrypted SSL/TLS connections:

Command Line - Simple SSL:

# Modern clients (MariaDB 10.2+, MySQL 5.7.11+)
mysql -h db-mariadb-123456.danubedata.com -u doadmin -p \
  --ssl-mode=REQUIRED --ssl-verify-server-cert=OFF

# Older clients
mysql -h db-mariadb-123456.danubedata.com -u doadmin -p \
  --ssl --ssl-verify-server-cert=0

Command Line - Full SSL with Client Certificates:

# Modern clients with full certificate authentication
mysql -h db-mariadb-123456.danubedata.com -u doadmin -p \
  --ssl-ca=/etc/database-certs/mariadb-instance-ca.pem \
  --ssl-cert=/etc/database-certs/mariadb-instance-client-cert.pem \
  --ssl-key=/etc/database-certs/mariadb-instance-client-key.pem

Application (PHP PDO):

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA => '/etc/database-certs/mariadb-123456-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // Skip hostname verification
];

Why skip hostname verification?
MariaDB auto-generates SSL certificates with generic names that don't match the actual hostname. Skipping hostname verification while using SSL still provides encryption for data in transit, which is safe for internal cluster traffic.

SSL certificates are automatically provisioned to VPS instances at:

  • /etc/database-certs/{database-name}-ca.pem
  • /etc/database-certs/{database-name}-client-cert.pem
  • /etc/database-certs/{database-name}-client-key.pem

Regular Security Updates

MariaDB instances are automatically updated with security patches:

  • Minor updates: Applied automatically during maintenance windows
  • Major updates: Require manual initiation
  • Maintenance windows: Configurable, typically off-peak hours
  • Notifications: Email notifications before scheduled maintenance

Troubleshooting

Connection Issues

Problem: Cannot connect to database

Solutions:

  • Verify connection details in dashboard
  • Check firewall rules allow your IP
  • Ensure SSL certificate is correctly configured
  • Test connection with mysql CLI tool
  • Check database status in dashboard

Performance Issues

Problem: Slow queries or high CPU usage

Solutions:

  • Review slow query log
  • Check for missing indexes
  • Analyze query execution plans with EXPLAIN
  • Consider upgrading to a larger profile
  • Optimize database schema and queries
  • Enable query cache for read-heavy workloads

Storage Full

Problem: Database running out of storage

Solutions:

  • Clean up old data
  • Archive historical records
  • Optimize tables with OPTIMIZE TABLE
  • Upgrade to a profile with more storage
  • Implement data retention policies

Too Many Connections

Problem: "Too many connections" error

Solutions:

  • Implement connection pooling
  • Reduce max connection lifetime
  • Close connections properly in application
  • Increase max_connections (contact support)
  • Upgrade to a larger profile with more max connections

Related Documentation