Pilvio
    |

    MySQL/MariaDB seadistamine ja varundamine

    Selles juhendis paigaldame MariaDB Pilvio VM-ile, seadistame tootmiskõlbliku konfiguratsiooni ja automaatse varundamise StorageVault'i.

    Mida ehitame

    • MariaDB 11 server Pilvio VM-il
    • Tootmiskõlblik konfiguratsioon ja turvaline juurdepääs
    • Automaatne varundamine Pilvio StorageVault'i (S3)
    • Tulemüürireeglid

    Eeldused

    • Pilvio konto ja API token (vaata ülevaadet)
    • Põhiteadmised MySQL/MariaDB haldamisest

    1. samm: VM ja tulemüüri loomine

    # VM loomine
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"

    Tulemüüri seadistus — luba MySQL ainult sinu rakendusserveritelt:

    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"]
      }
    ]
  }'

    2. samm: MariaDB paigaldamine

    ssh deploy@SINU_FLOATING_IP

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

# Turvaline algseadistus
sudo mariadb-secure-installation

    mariadb-secure-installation küsib:

    • Root parool → sea tugev parool
    • Remove anonymous users → Yes
    • Disallow root login remotely → Yes
    • Remove test database → Yes
    • Reload privileges → Yes

    3. samm: Tootmise seadistamine

    Loo fail /etc/mysql/mariadb.conf.d/99-pilvio.cnf:

    [mysqld]
# Võrk
bind-address = 0.0.0.0
port = 3306

# Mälu ja jõudlus (4 GB RAM VM jaoks)
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Ühendused
max_connections = 150
wait_timeout = 300
interactive_timeout = 300

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

# Märgistik
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

    sudo systemctl restart mariadb

    4. samm: Kasutaja ja andmebaasi loomine

    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

    5. samm: Andmete importimine (olemasolevalt serverilt)

    # Vanalt serverilt: ekspordi
mysqldump -h vana-server.ee -u vana_kasutaja -p \
  --single-transaction --routines --triggers --events \
  vana_andmebaas > /tmp/dump.sql

# Kopeeri ja impordi
scp /tmp/dump.sql deploy@SINU_FLOATING_IP:/tmp/
ssh deploy@SINU_FLOATING_IP "sudo mariadb myapp < /tmp/dump.sql"

    Suurte andmebaaside korral kasuta tihendust:

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

    6. samm: Automaatne varundamine StorageVault'i

    Loo fail /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: iga päev kell 3:00
(crontab -l 2>/dev/null; echo "0 3 * * * /home/deploy/backup-mariadb.sh >> /var/log/mariadb-backup.log 2>&1") | crontab -

    Pilvio VM-i automaatne varundamine

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

    7. samm: Taastamine

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

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

    Ühendamine rakendusega

    # Privaatse IP kaudu (sama Pilvio võrk)
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,
});

    Järgmised sammud: Ühenda MariaDB oma Node.js või FastAPI backendiga.