Back up MySQL and PostgreSQL databases in Docker containers

To back up a database running in Docker, run the client tool inside the container with docker exec and redirect the output to a file on the host. That way the dump is produced by the same server version that holds the data, and you never have to install a matching client on the VPS. This guide covers MySQL/MariaDB with mysqldump, PostgreSQL with pg_dump, compression, a cron script, and an offsite copy with restic.

Before you start

You need:

• A Linux VPS with Docker installed and at least one running database container.
• Root or sudo access, plus a non-root user such as deploy for the backup directory.
• The database credentials from your docker-compose.yml or .env file.
• Free disk space at least as large as the database, plus room for the compressed copy.
• A backup directory outside the container, for example /srv/backups.

List your containers and note the exact names:

docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'

The examples below use mysql_db for MySQL/MariaDB and pg_db for PostgreSQL. Replace them with your own container names.

Dump MySQL or MariaDB with mysqldump

The official MySQL and MariaDB images read the root password from the MYSQL_ROOT_PASSWORD environment variable inside the container. You can reuse it directly, which keeps the password out of your shell history.

mkdir -p /srv/backups
docker exec mysql_db sh -c 'mysqldump -u root -p"$MYSQL_ROOT_PASSWORD" \
  --single-transaction --routines --triggers --events \
  --databases myapp' > /srv/backups/myapp-$(date +%F).sql

The outer single quotes matter: $MYSQL_ROOT_PASSWORD is expanded by the shell inside the container, not by your login shell on the host.

What the flags do:

• --single-transaction takes a consistent snapshot of InnoDB tables without locking them for the whole dump. Use it for any live application.
• --routines --triggers --events includes stored procedures, triggers and scheduled events. Without them a restore silently loses logic.
• --databases myapp writes CREATE DATABASE and USE statements, so the dump recreates the schema on restore.

If your container does not expose the password as an environment variable, pass it through MYSQL_PWD instead. It is still visible to docker inspect on the host, so treat the VPS as a trusted machine.

docker exec -e MYSQL_PWD='your-password' mysql_db \
  mysqldump -u root --single-transaction --databases myapp > /srv/backups/myapp.sql

To dump every database including user grants, add --all-databases and --flush-privileges.

Dump PostgreSQL with pg_dump

For PostgreSQL, use the custom format. It is compressed already, restores selectively, and lets you list the contents before restoring.

docker exec pg_db pg_dump -U postgres -Fc -d myapp > /srv/backups/myapp-$(date +%F).dump

If you prefer plain SQL that you can read and edit, pipe it through gzip:

docker exec pg_db pg_dump -U postgres -d myapp | gzip -9 > /srv/backups/myapp-$(date +%F).sql.gz

Authentication depends on your pg_hba.conf. Inside the container, a local socket connection often uses trust or peer, so no password is needed. If you do need one, set PGPASSWORD for that single command:

docker exec -e PGPASSWORD='your-password' pg_db \
  pg_dump -U postgres -h 127.0.0.1 -Fc -d myapp > /srv/backups/myapp.dump

Compress and sanity-check the dump

Compress plain SQL dumps and check that the file is not empty or truncated.

gzip -9 /srv/backups/myapp-2026-01-01.sql
ls -lh /srv/backups/

A MySQL dump ends with a completion marker. Check it before you trust the file:

zcat /srv/backups/myapp-2026-01-01.sql.gz | tail -n 3

You should see a line like -- Dump completed on 2026-01-01. If the marker is missing, the dump was cut short — usually a full disk or a killed process.

For a PostgreSQL custom-format dump, list the archive contents without restoring:

docker exec -i pg_db pg_restore -l < /srv/backups/myapp-2026-01-01.dump | head -n 20

A readable table of contents means the archive is intact.

Automate it with a script and cron

Put the whole thing in one script so cron can run it unattended. set -euo pipefail makes the script fail loudly instead of writing a half-empty file.

sudo tee /usr/local/bin/db-backup.sh > /dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR=/srv/backups
STAMP=$(date +%F-%H%M)
mkdir -p "$BACKUP_DIR"

docker exec mysql_db sh -c 'mysqldump -u root -p"$MYSQL_ROOT_PASSWORD" \
  --single-transaction --routines --triggers --events \
  --databases myapp' | gzip -9 > "$BACKUP_DIR/myapp-$STAMP.sql.gz"

find "$BACKUP_DIR" -name 'myapp-*.sql.gz' -mtime +7 -delete
EOF
sudo chmod 750 /usr/local/bin/db-backup.sh

Run it once by hand and confirm the exit code:

sudo /usr/local/bin/db-backup.sh && echo OK

Then schedule it. A quiet hour for your application is the right time.

sudo crontab -e

15 3 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

Check /var/log/db-backup.log after the first night. An empty log is what you want.

Ship the dump off the server with restic

Restic gives you encrypted, deduplicated snapshots on a remote host. Install it from your distribution's package manager, then point it at a repository on another machine.

export RESTIC_REPOSITORY=sftp:deploy@203.0.113.10:/srv/restic-repo
export RESTIC_PASSWORD_FILE=/root/.restic-pass
restic init
restic backup /srv/backups --tag db-dumps
restic snapshots

Add the same three lines to the end of /usr/local/bin/db-backup.sh so every cron run also pushes a snapshot.

Check it works

A backup you have never restored is a guess. Restore the dump into a throwaway container and look at the data.

docker run -d --name mysql_test \
  -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=myapp mysql:8
sleep 25
zcat /srv/backups/myapp-2026-01-01.sql.gz | \
  docker exec -i mysql_test sh -c 'exec mysql -u root -p"$MYSQL_ROOT_PASSWORD"'
docker exec mysql_test mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e 'SHOW TABLES;' myapp
docker rm -f mysql_test

For PostgreSQL, restore the custom archive into a fresh container:

docker run -d --name pg_test -e POSTGRES_PASSWORD=test -e POSTGRES_DB=myapp postgres:16
sleep 20
docker exec -i pg_test pg_restore -U postgres -d myapp --clean --if-exists < /srv/backups/myapp-2026-01-01.dump
docker exec pg_test psql -U postgres -d myapp -c '\dt'
docker rm -f pg_test

Finally, confirm the offsite copy is readable:

restic snapshots --tag db-dumps
restic restore latest --target /tmp/restore-test
ls -lh /tmp/restore-test/srv/backups/

Troubleshooting

• mysqldump: Got error: 1045: Access denied for user 'root' — the password inside the container does not match. Check docker inspect mysql_db for the environment variables, or confirm the credentials in your compose file.
• the input device is not a TTY — you used docker exec -t inside cron or a pipe. Remove -t; it is only for interactive sessions.
• The dump file is 0 bytes — the redirect created the file on the host but the command inside the container failed. Run the command without the redirect to see the error, and keep set -o pipefail in your script.
• pg_dump: error: connection to server on socket failed — wrong user or database name. List what exists with docker exec pg_db psql -U postgres -l.
• restic: wrong password or no key found — the password file changed or the repository path is wrong. Verify RESTIC_REPOSITORY and RESTIC_PASSWORD_FILE point where you think.
• Restore stops with Unknown command '\' — you piped a custom-format .dump into psql. Use pg_restore for custom archives and psql only for plain SQL.

Next steps

• How to back up and restore MySQL databases with mysqldump for the host-level version of the same workflow.
• Back up Docker volumes with restic and restore them to cover uploads, config files and other persistent data next to the database.
• Open a shell inside a running Docker container when you need to inspect a container by hand.
• Back up and restore MongoDB with mongodump and mongorestore if you also run a document database.