MariaDB mySQL backup and restore in Docker

Running MariaDB or MySQL in Docker makes backups a little awkward. The database lives inside the container. The dump belongs on the host. A common case is the MySQL database behind a WordPress installation in Docker.

One way around this is to mount a host folder into the container in your docker-compose.yml and then work inside the container. The one-liners below are shorter. They work because >, < and | belong to the shell on your host, not to the container: the dump travels out through the container’s standard output and into a file next to you, and an import runs the same way in reverse.

One thing to settle first, because it comes up in every section below. The client programs inside the container depend on the image. MySQL images ship mysqldump and mysql. MariaDB images from 11.0 onwards ship mariadb-dump and mariadb instead.

Breaking change (May 2024):
The releases of 16 May 2024 — 10.5.25, 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4 and 11.4.2 — made mariadb-dump put one extra line at the very top of every dump: /*!999999\- enable the sandbox mode */ (MariaDB.org, 10.11.8 release notes, MDEV-33727). It puts the client into a sandbox mode, so that a tampered dump file can no longer trigger shell commands on the way in.

That line has a cost. Older MariaDB clients choke on it, and so does every MySQL client, current ones included. The import stops at ERROR at line 1: Unknown command '\-'. So this is not only a problem when you feed an ancient server — piping a MariaDB dump into mysql hits it too. If the dump has to go there anyway, drop the first line: mariadb-dump … | tail -n +2 > db.sql when you take the backup, or tail -n +2 db.sql | mysql … when you restore it. MariaDB states that the language connectors are unaffected.

Backup using mysqldump

docker exec db_container_name mysqldump [--user username] [--password=password] database_name > /your/backup/path/db.sql

Note:
mysqldump still works on MySQL images and on MariaDB images up to 10.11, and it works the same way for either database — as long as the program is actually in the container. From MariaDB 11.0 it no longer is. The next section covers that.

Backup with mariadb-dump

From MariaDB 11.0 the mysqldump symlink is deprecated and gone from the official Docker image (MariaDB documentation). The dump tool is not the only casualty. Version 11.0 dropped the whole family of mysql-named executables from the image, which is why the image’s own healthcheck had to be rebuilt without mysqladmin at the time (MariaDB.org). On MariaDB the three are called mariadb-dump, mariadb and mariadb-admin. Otherwise nothing changes:

docker exec db_container_name mariadb-dump [--user username] [--password=yourpassword] database_name > /your/backup/path/db.sql

Two things this one-liner leaves open. First, left to itself mariadb-dump locks the tables of each database while it reads them — --lock-tables is the default. For InnoDB, --single-transaction is the better choice: the dump then runs inside one transaction and captures a single point in time without blocking writes. It does nothing for MyISAM, which has no transactions to begin with (MariaDB documentation). Second, a password written like this ends up in the process list and in your shell history. The MariaDB documentation calls that insecure outright and points to an option file instead — or to --password with no value, which makes the client ask for it.

Restore the database

To restore a backup, use the following command:

docker exec -i db_container_name mysql [--user username] [--password=password] database_name < /your/backup/path/db.sql

On MySQL images and on MariaDB images up to 10.11, that command is correct as it stands. On a MariaDB image from 11.0 the client is called mariadb, so the import reads docker exec -i db_container_name mariadb [--user username] [--password=password] database_name < /your/backup/path/db.sql.

The -i is load-bearing. It keeps standard input open (Docker reference). Leave it out and the client in the container gets no input at all: it reads nothing, exits, and nothing about that looks like an error.

There is one more trap. A dump of a single database carries no CREATE DATABASE statement. The target database has to exist beforehand, or the server answers with error 1049, Unknown database. Pass --databases database_name when you dump and the CREATE DATABASE and USE statements travel with it.

Backup and restore in one command

To move a database from one container into another without parking a file in between, join the dump and the import with a pipe. For MySQL:

docker exec db_container_name mysqldump [--user username] [--password=password] database_name | docker exec -i second_db_container_name mysql [--user username] [--password=password] -C database_name

The following command is now used for MariaDB databases:

docker exec db_container_name mariadb-dump [--user username] [--password=password] database_name | docker exec -i second_db_container_name mysql [--user username] [--password=password] database_name

The -C in the MySQL example is short for --compress and compresses the client connection. MySQL 8.0.18 turned it into a legacy option alongside --compression-algorithms, but it still works (MySQL manual). With both containers on the same host it buys you very little.

Two caveats about the pipe. The second line hands a MariaDB dump to a program called mysql, and the sandbox line from the top of this article decides whether that works: a MariaDB client from the May 2024 releases onwards understands it, an older one does not, a real MySQL client never will. A MariaDB image from 11.0 has no mysql left anyway — the target there is mariadb. If the dump is headed for MySQL, strip the first line first. The other caveat is the missing file. There is nothing to retry from: if the transfer breaks off, the dump is gone and the target database is half filled.

Cheers!