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:

docker run --name my_backup --link my_mariadb -v /var/mariadb_backups:/backup confirm/mysql-backup

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:

docker start my_backup

Restore the MySQL or MariaDB

Of course you can also restore the database:

docker run --name my_backup --link my_mariadb -e MODE=RESTORE -v /var/mariadb_backups:/backup confirm/mysql-backup

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:

docker run --name demo_backup --link demo_db -v /var/demo_backup:/backup -d confirm/mysql-backup

The create a second container and test the restore:

docker run --name demo_restore --link demo_db -v /var/demo_backup:/backup -e MODE=RESTORE -d confirm/mysql-backup

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:

docker start demo_restore

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

19 Comments

    • Dominique Barton

      Hi frekele.

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

      Cheers
      Domi

  • Maksym Prokopov

    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

      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

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

    • Vyacheslav

      Thank you, Maksim, docker exec works.

  • walter carroll

    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

      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

  • Reuben

    Warning: The –link flag is a deprecated legacy feature of Docker. It may eventually be removed. Unless you absolutely need to continue using it, we recommend that you use user-defined networks to facilitate communication between two containers instead of using –link. One feature that user-defined networks do not support that you can do with –link is sharing environmental variables between containers. However, you can use other mechanisms such as volumes to share environment variables between containers in a more controlled way.

    Alternative is here: (Connect using network port mapping)
    https://docs.docker.com/engine/userguide/networking/default_network/dockerlinks/

  • MangeChats

    Your image principle rocks, but with my mysql containers based on image sameersbn/mysql:latest, it fails because the environment variable I get are spelt a bit differently (see joined example)
    MYSQL_ENV_DB_NAME=”IKV”
    MYSQL_ENV_DB_PASS=””
    MYSQL_ENV_DB_USER=”IKVadm”
    MYSQL_PORT_3306_TCP_ADDR=”10.10.0.9″

  • Dave

    No need for a special Image you can run mysqldump from the command line, create a one line *.sh bash script and schedule it in cron.

    Map the sqlbackup folder in your docker and make sure it’s a folder on the host computer for data persistence obv.

    docker exec MariaDB sh -c “mysqldump -u root -p’password’ –all-databases > /var/lib/mysql/sqlbackup/backupfilename.sql”

  • fall guys

    feel regret I did not know this before I read your article, thank for sharing!

  • doula

    I was looking for this!

  • life insurance

    Yey! I’m a little misconception and learning a lot! It’s just perfect for me!

  • https://surveyornewcastle.com.au/

    So happy I found this blog. So much inaccurate information out there. Thank you guys for spending the time to put out quality content.

  • subway surfers

    I will apply this method on your own. Thanks so much for sharing!