Pilvio
    |

    MySQL/MariaDB Setup and Backup

    In this tutorial, we will install MariaDB on a Pilvio VM, configure it for production use, and set up automatic backups to StorageVault.

    What we will build

    • MariaDB 11 server on a Pilvio VM
    • Production-ready configuration and secure access
    • Automatic backups to Pilvio StorageVault (S3)
    • Firewall rules

    Prerequisites

    • Pilvio account and API token (see overview)
    • Basic knowledge of MySQL/MariaDB administration

    Step 1: Creating VM and firewall

    # Create VM
curl "https://api.pilvio.com/v1/user-resource/vm" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "name=mariadb-server" \
  -d "os_name=ubuntu" \
  -d "os_version=24.04" \
  -d "vcpu=2" \
  -d "ram=4096" \
  -d "disks=50" \
  -d "username=deploy" \
  -d "password=TurvalineParool123!" \
  -d "public_key=ssh-ed25519 AAAA... sinu@arvuti"

    Firewall configuration — allow MySQL only from your application servers:

    curl "https://api.pilvio.com/v1/network/firewall" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -H "Content-Type: application/json" \
  -X POST \
  --data '{
    "name": "mariadb-fw",
    "rules": [
      {
        "protocol": "tcp",
        "direction": "inbound",
        "port_start": 22,
        "port_end": 22,
        "endpoint_spec_type": "any"
      },
      {
        "protocol": "tcp",
        "direction": "inbound",
        "port_start": 3306,
        "port_end": 3306,
        "endpoint_spec_type": "ip_prefixes",
        "endpoint_spec": ["10.0.0.0/8"]
      }
    ]
  }'

    Step 2: Installing MariaDB

    ssh deploy@SINU_FLOATING_IP

# Install MariaDB 11
sudo apt-get update
sudo apt-get install -y mariadb-server mariadb-client

# Secure initial setup
sudo mariadb-secure-installation

    mariadb-secure-installation will ask:

    • Root password -> set a strong password
    • Remove anonymous users -> Yes
    • Disallow root login remotely -> Yes
    • Remove test database -> Yes
    • Reload privileges -> Yes

    Step 3: Production configuration

    Create the file /etc/mysql/mariadb.conf.d/99-pilvio.cnf:

    [mysqld]
# Network
bind-address = 0.0.0.0
port = 3306

# Memory and performance (for a 4 GB RAM VM)
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Connections
max_connections = 150
wait_timeout = 300
interactive_timeout = 300

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

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

    sudo systemctl restart mariadb

    Step 4: Creating user and database

    sudo mariadb <<'SQL'
-- Rakenduse kasutaja
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app_user'@'10.%' IDENTIFIED BY 'tugev-parool-siia';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_user'@'10.%';

-- Varundamise kasutaja (ainult lugemisõigus)
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup-parool-siia';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;
SQL

    Step 5: Importing data (from an existing server)

    # From the old server: export
mysqldump -h vana-server.ee -u vana_kasutaja -p \
  --single-transaction --routines --triggers --events \
  vana_andmebaas > /tmp/dump.sql

# Copy and import
scp /tmp/dump.sql deploy@SINU_FLOATING_IP:/tmp/
ssh deploy@SINU_FLOATING_IP "sudo mariadb myapp < /tmp/dump.sql"

    For large databases, use compression:

    mysqldump -h vana-server.ee -u vana_kasutaja -p \
  --single-transaction vana_andmebaas | gzip | \
  ssh deploy@SINU_FLOATING_IP "gunzip | sudo mariadb myapp"

    Step 6: Automatic backups to StorageVault

    Create the file /home/deploy/backup-mariadb.sh:

    #!/bin/bash
set -euo pipefail

BUCKET="minu-mariadb-backups"
S3_ENDPOINT="https://s3.pilvio.com:8080"
DATE=$(date +%Y%m%d-%H%M%S)
BACKUP_DIR="/tmp/mariadb-backups"
BACKUP_FILE="${BACKUP_DIR}/myapp-${DATE}.sql.gz"

mkdir -p "$BACKUP_DIR"

# Dump koos tihendamisega
mariadb-dump -u backup_user -p'backup-parool-siia' \
  --single-transaction --routines --triggers --events \
  myapp | gzip > "$BACKUP_FILE"

# Üleslaadimine StorageVault'i
aws s3 cp "$BACKUP_FILE" "s3://${BUCKET}/mariadb/${DATE}.sql.gz" \
  --endpoint-url "$S3_ENDPOINT"

# Kohalik puhastamine (hoia viimased 3)
ls -t ${BACKUP_DIR}/myapp-*.sql.gz 2>/dev/null | tail -n +4 | xargs rm -f 2>/dev/null || true

echo "[$(date)] Varundamine lõpetatud: $BACKUP_FILE"

    chmod +x /home/deploy/backup-mariadb.sh

# Cron: every day at 3:00
(crontab -l 2>/dev/null; echo "0 3 * * * /home/deploy/backup-mariadb.sh >> /var/log/mariadb-backup.log 2>&1") | crontab -

    Pilvio automatic VM backup

    curl "https://api.pilvio.com/v1/user-resource/vm/backup" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "uuid=SINU_VM_UUID"

    Step 7: Restoring

    # Download backup
aws s3 cp s3://minu-mariadb-backups/mariadb/20250211-030000.sql.gz /tmp/ \
  --endpoint-url https://s3.pilvio.com:8080

# Restore
gunzip -c /tmp/20250211-030000.sql.gz | sudo mariadb myapp

    Connecting to the application

    # Via private IP (same Pilvio network)
mysql://app_user:tugev-parool@10.x.x.x:3306/myapp

# Node.js näide
# npm install mysql2

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

const pool = mysql.createPool({
  host: '10.x.x.x',  // Pilvio privaatne IP
  port: 3306,
  user: 'app_user',
  password: 'tugev-parool-siia',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
});

    Next steps: Connect MariaDB to your Node.js or FastAPI backend.