In this article, we will discuss the Docker image databack/mysql-backup, its configuration using Docker Compose, and some of the key features and parameters it offers for efficient MySQL database backups.

Docker Compose Configuration

Below is a sample Docker Compose configuration for the databack/mysql-backup Docker image:

Environment Variables

In this configuration, several environment variables are defined. Let’s review them in detail:

• DB_SERVER: Specifies the hostname to connect to the database. Required.
• DB_PORT: Port to use to connect to the database, defaulting to 3306 if not specified.
• DB_USER: The username for the database.
• DB_PASS_FILE: Path to the file containing the database password.
• NO_DATABASE_NAME: When set to false, specifies the backup is not for a specific database.
• DB_DUMP_CRON: Sets the dump schedule using standard crontab syntax. In this example, it’s set to back up the database at 2 AM every day.
• COMPRESSION: Specifies the compression to use; in this case, it’s bzip2.
• DB_DUMP_TARGET: The directory where the backup will be stored; here, it is /backup.
• DB_DUMP_SAFECHARS: Replaces certain characters in the dump filename to ensure compatibility across various systems.
• MYSQLDUMP_OPTS: Additional mysqldump options. In this example, the options --single-transaction, --quick, and --hex-blob are used.
• TZ: Time zone setting; here, it’s set to UTC.

The Options of mysqldump (MYSQLDUMP_OPTS)

The MYSQLDUMP_OPTS parameter allows additional options to be passed to mysqldump:

• --single-transaction: Ensures the dump is a consistent state by isolating the dump in a single transaction, ideal for InnoDB tables.
• --quick: Retrieves rows from the database table in smaller chunks to preserve memory.
• --hex-blob: Dumps binary strings in hexadecimal format to ensure data integrity for binary columns.

Using Secrets

The configuration uses Docker secrets to securely pass the database password. The secret db_password is defined under secrets, and the corresponding file is ./db_password.txt. The application can then access the password from /run/secrets/db_password.

Extending YAML Syntax to Avoid Text Duplication

The YAML configuration uses anchors and aliases to extend the syntax and avoid repetitive text. Here’s how it’s done:

• The x-defaults anchor stores the common configuration under &defaults, which is then referenced using <<: *defaults.
• The shared environment variables are stored under &default-environment and referenced using <<: *default-environment.

This technique maintains clarity and reduces redundancy in the configuration.

Creating Backup Accounts in MySQL

Before using the Docker image, a backup account must be created in MySQL with the necessary permissions. Execute the following SQL commands to create a user backup:

This grants the backup user the necessary permissions to perform read-only operations required for backup purposes.

To get started with the databack/mysql-backup Docker image, follow these straightforward steps.

Running the mysql-backup Service

To run the mysql-backup service in the background, use the following command:

This command starts the mysql-backup service as defined in your compose.yml file. The -d flag ensures that the service runs as a daemon in the background, allowing it to perform scheduled backups without user intervention.

Testing the Backup Configuration

To verify that your backup configuration is set up correctly, you can run the test service. This service is configured to execute the backup process once and then exit. Use the following command:

By running this command, the test service will execute and print logs to the console. If the backup configuration is correct, you should see messages indicating that the backup process has successfully completed.

The databack/mysql-backup Docker image is a comprehensive solution for scheduled backups of MySQL databases. By leveraging Docker Compose and the robust configuration options offered, including the use of secrets and customizable mysqldump parameters, users can efficiently manage and secure their database backups. The use of YAML anchors and aliases simplifies the configuration, promoting maintainability and clarity.