Automatic backup of docker Mysql or MariaDB container

Why do I need MySQL / MariaDB backups?

Regular backups of your MySQL databases are essential for several reasons, whether you are hosting as a hobby project at home or for a business with an SLA.

1. Data Loss Prevention: Backups help to prevent data loss due to system failures, hardware malfunctions, human errors, and other unforeseen circumstances. If you lose your data, you can restore it from a backup.
2. Continuity: If your MySQL database is an integral part of your business or home lab operations, a database failure can lead to downtime, loss of revenue, and damage to your reputation, your significant other, cat or dog, or hamster being mad at you because the supercoolapp you made for them isn’t working anymore. Regular backups can help ensure that your business can continue operating in the event of a database failure.
3. Disaster Recovery: In the event of a disaster such as a fire, flood, or other natural disasters, having a backup of your MySQL databases can help you quickly recover your data and resume your operations, this of course requires you to move these backups to a remote location away from your computer or server.
4. Compliance: Many industries have regulatory requirements that mandate data backups. If you are subject to such regulations, regular backups are essential to remaining compliant.
5. Testing and Development: Backups can also be useful for testing and development purposes. You can use backups to create test environments or to test updates and changes to your database without risking data loss.

Overall, regular backups of your MySQL databases are crucial for protecting your data, ensuring business continuity, and complying with regulations. It is recommended to establish a backup strategy that includes regular backups and storing backups in multiple locations along with just as important: restoring said backups to ensure the restore works.

Install docker

Or install docker rootless

Edit docker-compose.yml

Edit your docker-compose.yml and add the section from line 18 – Line 39.

version: '3'
services:
  mycoolapp_db:
    image: mariadb:10
    container_name: mycoolapp_db
    volumes:
      - ./db:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: mycoolapp_r_p
      MYSQL_DATABASE: mycoolapp
      MYSQL_USER: mycoolapp
      MYSQL_PASSWORD: mycoolapp
    restart: unless-stopped
    networks:
      - net
      
  mycoolapp_db_backup:
    image: fradelg/mysql-cron-backup
    container_name: mycoolapp_db_backup
    depends_on:
      - mycoolapp_db
    volumes:
      - ./db_backup:/backup
    environment:
      - MYSQL_HOST=mycoolapp_db
      - MYSQL_DATABASE=mycoolapp
      - MYSQL_USER=mycoolapp
      - MYSQL_PASS=mycoolapp
      - MAX_BACKUPS=2
      - INIT_BACKUP=0
      # Every day at 03:00
      - CRON_TIME=42 3 * * *
      - GZIP_LEVEL=9
      - TZ=America/New_York
      - MYSQLDUMP_OPTS=--skip-lock-tables --single-transaction --quick
    restart: unless-stopped
    networks:
      - net

networks:
  net:

ENV variables you can use:

• MYSQL_HOST: The host/ip of your mysql database.
• MYSQL_HOST_FILE: The file in the container where to find the host of your mysql database (cf. docker secrets). You should use either MYSQL_HOST_FILE or MYSQL_HOST (see examples below).
• MYSQL_PORT: The port number of your mysql database.
• MYSQL_USER: The username of your mysql database.
• MYSQL_USER_FILE: The file in the container where to find the user of your mysql database (cf. docker secrets). You should use either MYSQL_USER_FILE or MYSQL_USER (see examples below).
• MYSQL_PASS: The password of your mysql database.
• MYSQL_PASS_FILE: The file in the container where to find the password of your mysql database (cf. docker secrets). You should use either MYSQL_PASS_FILE or MYSQL_PASS (see examples below).
• MYSQL_DATABASE: The database name to dump. Default: --all-databases.
• MYSQL_DATABASE_FILE: The file in the container where to find the database name(s) in your mysql database (cf. docker secrets). In that file, there can be several database names: one per line. You should use either MYSQL_DATABASE or MYSQL_DATABASE_FILE (see examples below).
• MYSQLDUMP_OPTS: Command line arguments to pass to mysqldump (see mysqldump documentation).
• MYSQL_SSL_OPTS: Command line arguments to use SSL.
• CRON_TIME: The interval of cron job to run mysqldump. 0 3 * * sun by default, which is every Sunday at 03:00. It uses UTC timezone.
• MAX_BACKUPS: The number of backups to keep. When reaching the limit, the old backup will be discarded. No limit by default.
• INIT_BACKUP: If set, create a backup when the container starts.
• INIT_RESTORE_LATEST: If set, restores the latest backup.
• EXIT_BACKUP: If set, create a backup when the container stops.
• TIMEOUT: Wait a given number of seconds for the database to be ready and make the first backup, 10s by default. After that time, the initial attempt for backup gives up and only the Cron job will try to make a backup.
• GZIP_LEVEL: Specify the level of gzip compression from 1 (quickest, least compressed) to 9 (slowest, most compressed), default is 6.
• USE_PLAIN_SQL: If set, back up and restore plain SQL files without gzip.
• TZ: Specify TIMEZONE in Container. E.g. “Europe/Berlin”. The default is UTC.

The ones that matter most here (imo) are the following:

INIT_BACKUP: I don’t bother with this because I want backups done on a schedule.
INIT_RESTORE_LATEST: this restores the last backup made on launch, this is dangerous, be careful setting this.
EXIT_BACKUP: I also don’t have this as again I need timed backups, but this can be very useful to make backups on the shutdown of a container.
MAX_BACKUPS: very important, this is basically how many days of backups you will have. I keep it low because I have these backups moved to remote storage where they are kept for up to 6 months.
CRON_TIME: this follows standard cron notation which you can change using a resource like https://crontab.guru .

you can get the latest info from the projects homepage : https://github.com/fradelg/docker-mysql-cron-backup