Simplify Backing Up Multiple MySQL Databases on Docker

Backing up multiple databases on Docker can be challenging, especially when you need to handle each database individually. Ensuring your data is backed up regularly is crucial to prevent data loss and maintain business continuity.

This guide provides a comprehensive walkthrough on automating backups for multiple MySQL databases running inside Docker containers using the Mysql-bkup tool. With Mysql-bkup, you can streamline the process, making it easier and more efficient to manage backups for multiple databases simultaneously.

Why Backing Up Multiple Databases is Challenging

• Complexity: Managing individual backup scripts for each database can be time-consuming and error-prone.
• Resource Management: Running multiple backup processes simultaneously can strain system resources.
• Consistency: Ensuring all databases are backed up at the same time and with the same settings can be difficult.

How Mysql-bkup Simplifies the Process

Mysql-bkup is a Docker-based tool designed to backup, restore, and migrate MySQL databases. It supports multiple databases, and storage options (local, S3, SFTP, Azure Blob), and ensures data security through GPG encryption. With its configuration file, you can define backup schedules and settings for multiple databases in one place, eliminating the need for individual scripts.

Key Benefits of Using Mysql-bkup

• Centralized Configuration: Manage backups for multiple databases using a single configuration file.
• Flexible Scheduling: Set up global or database-specific backup schedules with cron expressions.
• Multiple Storage Options: Store backups locally or in the cloud (S3, Azure Blob, SFTP).
• Security: Encrypt backups using GPG for added protection.
• Notifications: Receive email or Telegram alerts for backup success or failure.

Getting Started

Prerequisites

Before proceeding, ensure the following are in place:

• Docker installed and running.
• MySQL or MariaDB databases running in Docker containers.

Automating Backups for Multiple Databases

Mysql-bkup allows you to configure and automate backups for multiple databases using a configuration file. This file defines the backup settings for each database, including host, port, credentials, and storage paths.

Configuration File Setup

The configuration file can be mounted into the container at /config/config.yaml, /config/config.yml, or specified via the BACKUP_CONFIG_FILE environment variable.

Key Features:

• Global Environment Variables: Use these for databases that share the same configuration.
• Database-Specific Overrides: Override global settings for individual databases by specifying them in the configuration file or using the database name as a suffix in the variable name (e.g., DB_HOST_DATABASE1).
• Global Cron Expression: Define a global cronExpression in the configuration file to schedule backups for all databases. If omitted, backups will run immediately.
• Configuration File Path: Specify the configuration file path using:
  • The BACKUP_CONFIG_FILE environment variable.
  • The --config or -c flag for the backup command.

Example Configuration File (config.yaml)

# Optional: Define a global cron expression for scheduled backups.
# Example: "@every 20m" (runs every 20 minutes). If omitted, backups run immediately.
cronExpression: "@midnight" # Optional

databases:
  - host: mysql1       # Optional: Overrides DB_HOST or uses DB_HOST_DATABASE1.
    port: 3306         # Optional: Default is 3306. Overrides DB_PORT or uses DB_PORT_DATABASE1.
    name: database1    # Required: Database name.
    user: database1    # Optional: Overrides DB_USERNAME or uses DB_USERNAME_DATABASE1.
    password: password # Optional: Overrides DB_PASSWORD or uses DB_PASSWORD_DATABASE1.
    path: /s3-path/database1  # Required: Backup path for SSH, FTP, or S3 (e.g., /home/toto/backup/).

  - host: mysql2       # Optional: Overrides DB_HOST or uses DB_HOST_LLAP.
    port: 3306         # Optional: Default is 3306. Overrides DB_PORT or uses DB_PORT_LLAP.
    name: lldap        # Required: Database name.
    user: lldap        # Optional: Overrides DB_USERNAME or uses DB_USERNAME_LLAP.
    password: password # Optional: Overrides DB_PASSWORD or uses DB_PASSWORD_LLAP.
    path: /s3-path/lldap  # Required: Backup path for SSH, FTP, or S3 (e.g., /home/toto/backup/).

  - host: mysql3       # Optional: Overrides DB_HOST or uses DB_HOST_KEYCLOAK.
    port: 3306         # Optional: Default is 3306. Overrides DB_PORT or uses DB_PORT_KEYCLOAK.
    name: keycloak     # Required: Database name.
    user: keycloak     # Optional: Overrides DB_USERNAME or uses DB_USERNAME_KEYCLOAK.
    password: password # Optional: Overrides DB_PASSWORD or uses DB_PASSWORD_KEYCLOAK.
    path: /s3-path/keycloak  # Required: Backup path for SSH, FTP, or S3 (e.g., /home/toto/backup/).

  - host: mysql4       # Optional: Overrides DB_HOST or uses DB_HOST_JOPLIN.
    port: 3306         # Optional: Default is 3306. Overrides DB_PORT or uses DB_PORT_JOPLIN.
    name: joplin       # Required: Database name.
    user: joplin       # Optional: Overrides DB_USERNAME or uses DB_USERNAME_JOPLIN.
    password: password # Optional: Overrides DB_PASSWORD or uses DB_PASSWORD_JOPLIN.
    path: /s3-path/joplin  # Required: Backup path for SSH, FTP, or S3 (e.g., /home/toto/backup/).

Deploying with Docker Compose

To deploy the backup solution using Docker Compose, create a compose.yaml file with the following content:

services:
  mysql-bkup:
    image: jkaninda/mysql-bkup
    container_name: mysql-bkup
    command: backup --config /backup/config.yaml
    volumes:
      - ./backup:/backup  # Mount the backup directory
      - ./config.yaml:/backup/config.yaml  # Mount the configuration file
    networks:
      - web

networks:
  web:

---

Conclusion

Backing up multiple MySQL databases on Docker doesn’t have to be complicated. With Mysql-bkup, you can automate and streamline the process, ensuring your data is secure and easily recoverable. Whether you’re managing a single database or multiple, this tool provides the flexibility and reliability you need.

For more detailed instructions and advanced configurations, explore the official documentation: Mysql-bkup Documentation. Start automating your backups today and enjoy peace of mind knowing your data is protected.