Guide to deploying databases

Databases are not stateless, it’s deploy is difficult than a deploy of a regular service. In this guide we focus on MySQL, but it’s really up to you what database you will use.

Goals:

  • Running up-to-date database engine
  • Automatically made backup
  • Migration of the data on deploy
  • Administration panel for overview

1. Basic configuration

infrastructure.db.yml.example

version: "2"

volumes:
    mysql-data:

services:
    #
    # MySQL server
    #
    #  In ./containers/templates/source/mysql put JINJA2 templates of SQL files, they will be compiled to ./containers/templates/compiled/mysql and run on MySQL start
    #  you can create users, databases with this method on MySQL startup, while keeping the passwords in the .env file
    #
    #  --innodb_file_per_table=1 is a secure setting to keep tables separated (in case of disk failure easier to recover data)
    #
    #  All MySQL databases data is stored in "./data/mysql". For backup configuration use "docker_volumes"
    #  (docker_hot_volumes not recommended) with path /var/lib/mysql
    #
    db:
        image: mariadb
        volumes:
            - "mysql-data:/var/lib/mysql"     # use a named volume
            #- "./data/mysql:/var/lib/mysql"  # use a bind-mount (possibly slower, but the files would be in one folder)
            - "./containers/templates/compiled/mysql:/docker-entrypoint-initdb.d/"
        command: mysqld --innodb_file_per_table=1
        environment:
            - MYSQL_USER=${MYSQL_USER}
            - MYSQL_PASSWORD=${MYSQL_PASSWORD}
            - MYSQL_DATABASE=${MYSQL_DATABASE}
            - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
        labels:
            com.centurylinklabs.watchtower.enable: true


    #
    # MySQL admin panel
    #
    db_mysql_admin:
        image: phpmyadmin/phpmyadmin
        environment:
            - PMA_HOST=db

            # gateway configuration
            - VIRTUAL_HOST=dba.${MAIN_DOMAIN}${DOMAIN_SUFFIX}
            - VIRTUAL_PORT=80
            - LETSENCRYPT_HOST=dba.${MAIN_DOMAIN}${DOMAIN_SUFFIX}
            - LETSENCRYPT_EMAIL=${LETSENCRYPT_EMAIL}
        expose:
            - "80"
        labels:
            com.centurylinklabs.watchtower.enable: true

2. Keep the database server up-to-date (optional)

You may want to enable the Watchtower to update the database image automatically, to do this please tag the database image with a label com.centurylinklabs.watchtower.enable: true

infrastructure.updates.yml.example

version: "2"
services:
    #
    # Automatically does a docker pull for tagged services with "com.centurylinklabs.watchtower.enable" tag
    #
    autoupdater:
        image: v2tec/watchtower
        command: "--label-enable --interval ${WATCHTOWER_INTERVAL} --cleanup"
        volumes:
            - /var/run/docker.sock:/var/run/docker.sock
        environment:
            - WATCHTOWER_NOTIFICATIONS=slack
            - WATCHTOWER_NOTIFICATION_SLACK_HOOK_URL=${WATCHTOWER_SLACK_HOOK}
            - WATCHTOWER_NOTIFICATION_SLACK_IDENTIFIER=${WATCHTOWER_IDENTIFIER}
        labels:
            com.centurylinklabs.watchtower.enable: true

3. Have a backup (optional)

It’s recommended to use File Repository as backup storage, but it’s totally optional as it requires an additional server in your infrastructure.

Read more here: file-repository

Example bahub configuration:

You need to define BACKUPS_DB_COLLECTION in the .env file.

4. Automate migrations (optional)

Adding a new application to the network requires a manual user creation, database creation - this also can be automated optionally.

  1. Create a _migrations database in the SQL
  2. Enable infrastructure.db_migrations.yml configuration file
  3. To execute SQL statements just right after deployment, put them into ./containers/migrations/prod and enable the db_migrations configuration file
  4. (Optionally) Use templating mechanism, put a SQL template in JINJA2 format in ./containers/templates/source, so it will appear compiled in /templates inside of the container

infrastructure.db_migrations.yml.example

version: "2"
services:

    #
    # Executes migrations right after database starts, then container stops as it is no longer needed
    #
    db_updater:
        image: mkbucc/shmig:latest
        depends_on:
            - db
        volumes:
            - ./containers/migrations/prod:/sql:ro
            - ./containers/templates/compiled:/templates:ro
        command: -t mysql up
        environment:
            - PASSWORD=${MYSQL_ROOT_PASSWORD}
            - HOST=db_mysql
            - LOGIN=root
            - PORT=3306
            - DATABASE=_migrations

Example of automatically generated SQL files with using variables from .env file

./containers/templates/source/access.sql.j2

CREATE DATABASE IF NOT EXISTS zsp;
CREATE USER IF NOT EXISTS 'some_page'@'%' IDENTIFIED BY '{{ DB_PASSWD_SOME_PAGE }}';
GRANT ALL ON `some_page`.* TO 'some_page'@'%' IDENTIFIED BY '{{ DB_PASSWD_SOME_PAGE }}';

./containers/migrations/prod/1553701697-some-page.sql

-- Migration: some-page
-- Created at: 2019-03-27 16:46
-- ====  UP  ====

source /templates/access.sql;

-- ==== DOWN ====

5. When database will go down, show maintenance page (optional)

Infracheck is doing health checks of the infrastructure. The “infrastructure.health.yml” needs to be enabled at first.

Example health check

Please notice the on_each_down and on_each_up sections.

{
    "type": "port-open",
    "input": {
        "po_port": "3306",
        "po_host": "db_mysql",
        "po_timeout": "1"
    },
    "hooks": {
        "on_each_down": [
            "touch /maintenance/on"
        ],
        "on_each_up": [
            "rm -f /maintenance/on"
        ]
    }
}