Backup a MySQL or MariaDB Docker container

When it comes to databases running in Docker containers then a consistent backup of the data with classic backup methods becomes a bit tricky. So you’ve to use a quite different approach to backup a MySQL or MariaDB Docker container.

The nature of containers

I like Docker and I like to run my application stack inside containers. Simple applications probably settle with a single container. Though, it can get a bit more complicated really quick. Eventually you start working with persistent data and you might looking forward to a database. In small setups an SQLite database might be enough. However, you’ll come to a point where your need a “proper” database instance like MySQL, MariaDB or PostgreSQL.

Of course you want a proper setup – won’t you? So you might create an application container, a database container and link them together, so that the application can talk to the database. Most likely you also have a Docker reverse proxy or port mapping in place, so that the request finally reaches the application.

Now, this is all fine and it works nicely. However, if you’re looking to create a backup of your database you’ve a bit of a problem. Your database container will probably have no NAT’ed port mapping and the IP can change all the time. If you’re using the official Docker images you also might have no direct access to the database credentials. So you need a different solution.

The idea to backup a database

As shown before, you can’t easily use the traditional methods to backup a database inside a container. So you need a new approach to update your database – a more container-like approach. You’re already using containers to run your application stack, so why not put the backup procedure inside a container as well?

The first approach is putting the backup inside the database container itself. Of course this would work. However, IMHO this isn’t very nice and clean. You can no longer use the official Docker images and you’ve to create your own image to run a database. You should also run only one process per container and move all scheduled maintenance tasks and batch jobs to dedicated containers.

So you’re looking forward to a little different setup. You might have an application container, a database container and last but not least a backup container. The backup container is only in charge of backupping the data running inside the database container.

Backup a MySQL or MariaDB

With that thought in mind I’ve create a new Docker image to backup a MySQL or MariaDB. The image has some “magic” in it, which automatically detects your linked database container (MySQL, MariaDB) and starts to backup the data with mydumper – a fast MySQL backup utility.

Let’s say you’re using your own MariaDB container and you want to backup it. Simply run:

This will create a backup of the my_mariadb container, stores it in /var/mariadb_backup on your host and quits. If you want to create another backup you only have to start your container again:

Restore the MySQL or MariaDB

Of course you can also restore the database:

The my_backup container will automatically detects the latest backup in /var/mariadb_backups and restores the database with it. Of course you can also define it manually by setting the  RESTORE_DIR environment variable.

Demo applications made easy

Of course you can use the confirm/mysql-backup Docker image to create and restore backups for all of your productive MySQL or MariaDB containers, because it should work out of the box with the official database Docker images. Though, there’s one more advantage.

Let’s say you’ve a demo application intended to use for several customers at different times. Just setup the application in advance and then backup the database so that you’ve a clean snapshot. For example:

The create a second container and test the restore:

Now you’re ready to let the customer tinker with your well-prepared application. After mentioned customer destroyed your application, you can easily restore it to its snapshot state by running:

See that? Simply run the command above to reset your demo application back to its initial status 🙂

8 Comments

    • Dominique Barton Reply

      Hi frekele.

      Thanks for your comment and link. I’d pleased to have a look at it.

      Cheers
      Domi

  • Maksym Prokopov Reply

    I’ve found numerous troubles using this solution.

    1. Docker with syntax version 2 does not expose ENV variables for linked container, so script does not see database address and credentials.

    2. A lot of errors when restore with slightly different database version. In this example backup has been done with version 5.7.9 and restore with 5.7.12.

    Here is part of output log:

    ** (myloader:17): CRITICAL **: Error restoring vdomik.wp_links from file vdomik.wp_links-schema.sql.gz: Invalid default value for ‘link_updated’
    ** Message: Dropping table (if exists) vdomik.wp_options
    ** Message: Creating table vdomik.wp_options
    ** Message: Dropping table (if exists) vdomik.wp_postmeta
    ** Message: Creating table vdomik.wp_postmeta
    ** Message: Dropping table (if exists) vdomik.wp_posts
    ** Message: Creating table vdomik.wp_posts

    ** (myloader:17): CRITICAL **: Error restoring vdomik.wp_posts from file vdomik.wp_posts-schema.sql.gz: Invalid default value for ‘post_date’
    ** Message: Dropping table (if exists) vdomik.wp_term_relationships

    So I’d not recommend to use this solution.

    Another option is to use “docker exec” with “mysqldump” and “docker cp” for export mysql dump to host.

    • Dominique Barton Reply

      Hi Maksym

      This seems to be a mydumper issue and not a Docker issue. Have you probably backupped the database from another MySQL or mariaDB version as you’re trying to restore?

      Cheers
      Domi

    • Dominique Barton Reply

      Btw you’re right. Newer Docker versions have issues because of the missing ENV vars! Sorry about that… 

    • Vyacheslav Reply

      Thank you, Maksim, docker exec works.

  • walter carroll Reply

    Thanks for detailing your use-case. I’d be curious to understand in greater detail why you’re not interested in also containerizing postgres. Is it because you don’t have sufficient database init scripts that sets up database contents for development?

    • Dominique Barton Reply

      Hi Walter.

      Thanks for your comment. We currently have no PostgreSQL Docker images because we’ve no PostreSQL databaes in use. There was never an intention to “only” dockerize MySQL/MariaDB and ignore PostgreSQL. We just don’t use PostgreSQL yet. However, we would definitely dockerize PostgreSQL when we’ve a use case for it. There’s also an official image available on Docker Hub.

      Cheers
      Domi

Leave a Reply

Your email address will not be published. Required fields are marked *