Backup SQL

1. Use Side Tasks

Here's an example of how containers can be used in SideTasks to perform external backups to S3 storage. LayerOps provides an example image that dump databases and sends it to an S3 account. registry.nimeops.net/layerops-public/marketplace/backup-sql

Always with Scaleway provider, so we'll use the same credentials in shared Environment Variables:

MY_BACKUP_S3_ACCESS_KEY (don't forget to check the "hide value" box)
MY_BACKUP_S3_SECRET_KEY (don't forget to check the "hide value" box)

1.1 Variables used with backup-sql service

DB_HOST : the address of sql server (hostname or ip, + optionnaly the port if different of default 3306)
DB_USER: username to connect to the sql server
DB_PASSWORD: password to connect to the sql server
DB_NAMES: list of databases to backup (separated by a ";")

BACKUP_S3_PROVIDER : must be a S3 provider supported by rclone's S3 storage provider plugin (see https://rclone.org/s3/ )- example: Scaleway
BACKUP_S3_ENDPOINT: the S3 remote endpoint url - example s3.fr-par.scw.cloud
BACKUP_S3_BUCKET: the S3 bucket name - example: my-backup-bucket
BACKUP_S3_PATH (optionnal): the S3 subdirectory inside the bucket - example: nextcloud
BACKUP_S3_REGION: the S3 remote region - example: fr-par
BACKUP_S3_ACCESS_KEY (sensitive)
BACKUP_S3_SECRET_KEY (sensitive)

BACKUP_ENCRYPT_KEY (optionnal): if set, the backup will be encrypted using the given encrypt key
BACKUP_REMOTE_DIR (optionnal): if set, Remote dir name (default to sql)
BACKUP_RETENTION (optionnal): if set, Backup retention in days (default to 7)
BACKUP_COMPRESSION (optionnal): if set, Compression type: zstd (default) and targz
BACKUP_RESTORE_DATE (optionnal): if set, specify the date ie 2022_12_26_142528 (for 2022_12_26_142528_dbname.tar.gz)
BACKUP_SERVICE_NAME (optionnal): if set, specify the service Name (default mysql, postgresql 17 available)
BACKUP_LOCAL_RESTORE_DIR (optionnal): if set to yes, only restore dump file in /docker-entrypoint-initdb.d

1.2 Add to database service

Example for MariaDB

YOU MUST create a persistent volume /docker-entrypoint-initdb.d, which will be backuped

volumes:
  - name: mariadb_initdb
    path: /docker-entrypoint-initdb.d

add environment Variables in service:

environmentVariables:
(... existing variables ...)
  - key: BACKUP_SERVICE_NAME
    value: "WORDPRESS_DB"     
  - key: BACKUP_S3_BUCKET
    value: "poc-layerops"
  - key: BACKUP_S3_PATH
    value: "DEMO_BACKUP_VOLUME"
  - key: BACKUP_S3_PROVIDER
    value: "Scaleway"
  - key: BACKUP_S3_ENDPOINT
    value: "s3.fr-par.scw.cloud"
  - key: BACKUP_S3_REGION
    value: "fr-par"
  - key: BACKUP_S3_ACCESS_KEY
    value: "%MY_BACKUP_S3_ACCESS_KEY%"
  - key: BACKUP_S3_SECRET_KEY
    value: "%MY_BACKUP_S3_SECRET_KEY%"
  - key: BACKUP_COMPRESSION
    value: "targz"
  - key: BACKUP_RETENTION
    value: "1"
  - key: BACKUP_LOCAL_RESTORE_DIR
    value: "yes"
  - key: MYSQL_RANDOM_ROOT_PASSWORD
    value: "yes"
  - key: MYSQL_USER
    value: "wp"
  - key: MYSQL_PASSWORD
    value: "%MY_WORDPRESS_DB_PASSWORD%"
  - key: MYSQL_DATABASE
    value: "wp"
  - key: DB_NAMES # used by backup-sql container
    value: "%MYSQL_DATABASE%" 
  - key: DB_USER # used by backup-sql container
    value: "%MYSQL_USER%" 
  - key: DB_PASSWORD # used by backup-sql container
    value: "%MYSQL_PASSWORD%"
  - key: DB_HOST # used by backup-sql container -> linked service
    value: "127.0.0.1"

Add sideTasks to the service:

Classic environment

sideTasks:
  - dockerConfiguration: # Restore
      image: registry.nimeops.net/layerops-public/marketplace/backup-sql
      imageVersion: 2.0.0
      command:
        - /usr/local/bin/restore
    type: preStart
    isLongLived: false

2. Use Cron function for backups / restore jobs

2.1 Create backup mysqldump job (cron and manal run)

Classic environment

services:
  - id: backupmywp
    name: Backup-myWP
    dockerConfiguration:
      image: registry.nimeops.net/layerops-public/marketplace/backup-sql
      imageVersion: 2.0.0
    countMin: 1
    countMax: 1
    cronExpression: "@daily"
    cronAllowOverlap: false
    cpuLimit: 512 # MHz 
    memoryLimitMiB: 512
    links:
      - toServiceId: mydb
        toServicePort: 3306
        localExposedPort: 3306
        variableHost: MY_DB_HOST
        variablePort: MY_DB_PORT
        variableAddress: MY_DB_ADDRESS
    sharedEnvironmentVariables:
      - MY_BACKUP_S3_ACCESS_KEY
      - MY_BACKUP_S3_SECRET_KEY
      - MY_WORDPRESS_DB_PASSWORD
    environmentVariables:
      - key: BACKUP_LOCAL_RESTORE_DIR
        value: "no"
      - key: BACKUP_SERVICE_NAME
        value: "WORDPRESS_DB"     
      - key: BACKUP_S3_BUCKET
        value: "poc-layerops"
      - key: BACKUP_S3_PATH
        value: "DEMO_BACKUP_VOLUME"
      - key: BACKUP_S3_PROVIDER
        value: "Scaleway"
      - key: BACKUP_S3_ENDPOINT
        value: "s3.fr-par.scw.cloud"
      - key: BACKUP_S3_REGION
        value: "fr-par"
      - key: BACKUP_S3_ACCESS_KEY
        value: "%MY_BACKUP_S3_ACCESS_KEY%"
      - key: BACKUP_S3_SECRET_KEY
        value: "%MY_BACKUP_S3_SECRET_KEY%"
      - key: BACKUP_COMPRESSION
        value: "targz"
      - key: BACKUP_RETENTION
        value: "7"
      - key: DB_NAMES # used by backup-sql container
        value: "%MYSQL_DATABASE%" 
      - key: DB_USER # used by backup-sql container
        value: "%MYSQL_USER%" 
      - key: DB_PASSWORD # used by backup-sql container
        value: "%MYSQL_PASSWORD%" 
      - key: DB_HOST # used by backup-sql container -> linked service
        value: "%MY_DB_HOST%"
      - key: DB_PORT # used by backup-sql container -> linked service
        value: "%MY_DB_PORT%"
      - key: MYSQL_USER
        value: "wp"
      - key: MYSQL_PASSWORD
        value: "%MY_WORDPRESS_DB_PASSWORD%"
      - key: MYSQL_DATABASE
        value: "wp"

2.2 Create restore mysqldump job (manual run)

This job, which is imported stopped (isPaused: true), will have to be triggered manually. It will check that the database is empty, and if so, will import it by retrieving the backup from S3.

Classic environment

services:
  - id: restoremywp
    name: Restore-myWP
    dockerConfiguration:
      image: registry.nimeops.net/layerops-public/marketplace/backup-sql
      command:
        - /usr/local/bin/restore
      imageVersion: 2.0.0
    countMin: 1
    countMax: 1
    cronExpression: "* * * * *"
    cronAllowOverlap: false
    isPaused: true
    cpuLimit: 512 # MHz
    memoryLimitMiB: 512
    links:
      - toServiceId: mydb
        toServicePort: 3306
        localExposedPort: 3306
        variableHost: MY_DB_HOST
        variablePort: MY_DB_PORT
        variableAddress: MY_DB_ADDRESS
    sharedEnvironmentVariables:
      - MY_BACKUP_S3_ACCESS_KEY
      - MY_BACKUP_S3_SECRET_KEY
      - MY_WORDPRESS_DB_PASSWORD
    environmentVariables:
      - key: BACKUP_LOCAL_RESTORE_DIR
        value: "no"
      - key: BACKUP_SERVICE_NAME
        value: "WORDPRESS_DB"     
      - key: BACKUP_S3_BUCKET
        value: "poc-layerops"
      - key: BACKUP_S3_PATH
        value: "DEMO_BACKUP_VOLUME"
      - key: BACKUP_S3_PROVIDER
        value: "Scaleway"
      - key: BACKUP_S3_ENDPOINT
        value: "s3.fr-par.scw.cloud"
      - key: BACKUP_S3_REGION
        value: "fr-par"
      - key: BACKUP_S3_ACCESS_KEY
        value: "%MY_BACKUP_S3_ACCESS_KEY%"
      - key: BACKUP_S3_SECRET_KEY
        value: "%MY_BACKUP_S3_SECRET_KEY%"
      - key: BACKUP_COMPRESSION
        value: "targz"
      - key: BACKUP_RETENTION
        value: "7"
      - key: DB_NAMES # used by backup-sql container
        value: "%MYSQL_DATABASE%" 
      - key: DB_USER # used by backup-sql container
        value: "%MYSQL_USER%" 
      - key: DB_PASSWORD # used by backup-sql container
        value: "%MYSQL_PASSWORD%" 
      - key: DB_HOST # used by backup-sql container -> linked service
        value: "%MY_DB_HOST%"
      - key: DB_PORT # used by backup-sql container -> linked service
        value: "%MY_DB_PORT%"
      - key: MYSQL_USER
        value: "wp"
      - key: MYSQL_PASSWORD
        value: "%MY_WORDPRESS_DB_PASSWORD%"
      - key: MYSQL_DATABASE
        value: "wp"

ServicesBackup Volumes

MonitoringIntroduction