mysqldump | gzip Made a 20-Byte Backup That Said It Worked

Why is my mysqldump backup only 20 bytes?

Because mysqldump failed and gzip didn't. A gzip file wrapping nothing is about 20 bytes: a header, an empty compressed block, and a trailer. If a .sql.gz is that size, the dump wrote nothing and the compressor faithfully compressed it.

The worse part is that the script reported success. Every database backup one of my maintenance scripts had ever taken was one of these, each logged as "status": "completed".

Why the script said it worked

The backup line was the one everybody writes:

mysqldump "$DB" | gzip > "$OUT"

The bash manual is explicit about what that line returns: "The return status of a pipeline is the exit status of the last command, unless the pipefail option is enabled." The last command is gzip. gzip got an empty stream, compressed it, and exited 0. mysqldump's failure (bad credentials, a missing database, a dropped connection) went to stderr and nowhere else.

This is the sibling of grep exiting zero when it finds the word FAILED: in both cases the command that decided "success" was not the one doing the work.

The fix: turn on pipefail

#!/usr/bin/env bash
set -euo pipefail

mysqldump --single-transaction "$DB" | gzip > "$OUT"

With pipefail, per the same manual, "the return value of a pipeline is the value of the last (rightmost) command to exit with a non-zero status." Now a failed mysqldump fails the line, and set -e stops the script before it logs anything as completed. It's off unless the script turns it on, which is why this keeps happening.

Two notes:

Put set -o pipefail in the script itself, not in your interactive shell. Cron and CI don't inherit your shell settings.
It's a bash (and zsh/ksh) option. If the script runs under a minimal /bin/sh, don't assume it's there. Use a bash shebang.

When you need to know which command failed

PIPESTATUS holds every command's exit code from the last pipeline, in order:

mysqldump --single-transaction "$DB" | gzip > "$OUT"
codes=("${PIPESTATUS[@]}")
if [ "${codes[0]}" -ne 0 ]; then
  echo "mysqldump failed with ${codes[0]}" >&2
  exit 1
fi

Copy it straight away: the next command you run overwrites it.

Don't trust the exit code alone: check the file

Exit codes tell you nothing crashed. They don't tell you the backup is usable. Two cheap checks catch the rest:

gzip -t "$OUT"                                  # the archive is intact
zcat "$OUT" | tail -n 1 | grep -q '^-- Dump completed' \
  || { echo "dump is incomplete" >&2; exit 1; }

mysqldump writes a -- Dump completed on ... comment as its last line (unless you pass --skip-comments), so a file missing it was cut off partway.

FAQ

Does set -e alone fix this? No. Without pipefail, the pipeline's status is still gzip's 0, so set -e has nothing to react to.

How do I find backups that already failed this way? Look for archives at or near 20 bytes, and run gzip -t plus the last-line check above against the ones you'd actually restore from.

`mysqldump | gzip` Made a 20-Byte Backup That Said It Worked