restic-backup-suite: Database dumps

Database dumps in restic-backup-suite: Docker MariaDB/MySQL, Docker PostgreSQL and native MySQL/MariaDB, entry format, password variable, file names and failure behaviour.

Deutsche Version

Back to overview

Database dumps

Before the file backup, backup.sh creates SQL dumps in a temporary directory /tmp/restic-db-dump-XXXXXX (mode 700). This directory is backed up together with BACKUP_PATHS and removed when the script exits. The dumps run in this order: native, Docker MariaDB/MySQL, Docker PostgreSQL.

You can test the dumps without a backup using restic-backup --dump-only.

Docker MariaDB and MySQL

DOCKER_MARIADB_CONTAINERS=(
    "my-mysql:root:app_db"
    "new-mariadb:root:wiki_db:MARIADB_ROOT_PASSWORD"
    "custom-db:root:mydb:DB_ROOT_PASS"
)

Format: container_name:db_user:db_name[:password_env_var]

Field	Meaning
`container_name`	Name of a running container (exact match in `docker ps`)
`db_user`	Database user
`db_name`	Database to dump (one database per entry)
`password_env_var`	Name of the environment variable inside the container that holds the password. Default: `MYSQL_ROOT_PASSWORD`. Only names made of letters, digits and underscore are accepted, otherwise the entry is skipped with an error

Per entry the script:

runs mariadb-dump inside the container via docker exec if it exists, otherwise mysqldump.
reads the password inside the container from the named environment variable. It is not stored in config.sh.
uses the options --single-transaction --quick --routines --triggers --events.
writes the result to <container>_<db_name>.sql.

If the container is not running, the script skips the entry with a warning. If the dump fails, the incomplete file is deleted and an error is logged.

Docker PostgreSQL

DOCKER_POSTGRES_CONTAINERS=(
    "my-postgres:postgres:app_db"
)

Format: container_name:db_user:db_name

The script runs pg_dump -U <db_user> --no-password <db_name> inside the container. The password comes from the container's POSTGRES_PASSWORD variable, which must be set there. Result file: <container>_<db_name>.sql.

Native (without Docker)

MYSQL_BACKUP_ENABLED=true
MYSQL_EXCLUDE_DBS="information_schema performance_schema mysql sys"

When true, the script lists all databases on the host (SHOW DATABASES) and dumps each one to <db>.sql. It uses mariadb-dump and mariadb if present, otherwise mysqldump and mysql. If a program is missing, the script skips the native dump with a warning.

There is no credentials setting. Access has to work for root without a password prompt (for example through the Unix socket).
The four system databases information_schema, performance_schema, mysql and sys are always skipped, regardless of MYSQL_EXCLUDE_DBS.
Options: --single-transaction --quick --lock-tables=false --routines --triggers.

Failure behaviour

A failed dump is logged but does not abort the backup. If restic backup then succeeds, the run ends with exit code 0 and writes the marker file even though a dump is missing. Check the log if the dumps matter, or test new entries beforehand with --dump-only, where a failed dump leads to exit code 1.

Restoring

You can import the dumps with restic-restore. Note the limitations on the Restore page.