Auditing Automatic Backups: When It Fails Silently

You open the crontab, the backup line is still there, and the log is full of success lines. But when you lose the server and look at the backup file, it's 20 kilobytes. An empty SQL file with a few comment lines. This is exactly the situation that turns "auditing automatic backups" from a ceremonial task into a necessity. A backup that fails silently is more dangerous than having no backup at all, because you're at ease and not looking for problems.

Why automatic backups fail without errors

I've seen three scenarios in practice a lot, and all three share a common pattern: the script finishes with exit code zero, but the job you expected didn't get done.

• The password changed. Someone changed the MySQL user's password from the panel, but ~/.my.cnf still has the old password. mysqldump exits with the error Access denied for user, but since the output is redirected to a file, the file gets created and stays empty.
• The disk filled up. Yesterday's backup was written halfway and today there was no room at all. If rotation isn't set up correctly, old versions don't get deleted and the disk fills up silently.
• The path changed. A new database was added or the /var/www path was moved to /srv/app, and the script still goes after the old path. rsync errors out on a nonexistent path, but if there's a || true at the end of the command, the error gets swallowed.

The common thread here is: none of these show up in crontab. Cron only knows the command ran. It doesn't know what the result was.

Three checks that actually catch silent failure

Check file size, not file existence

The existence of a backup file proves nothing. Its size proves a lot. A simple script that compares the size of the latest backup with yesterday's size catches most errors:

#!/bin/bash
LAST=$(stat -c%s /backup/db-$(date -d yesterday +%F).sql.gz)
TODAY=$(stat -c%s /backup/db-$(date +%F).sql.gz)
if [ "$TODAY" -lt $((LAST / 2)) ]; then
  echo "backup shrank: $LAST -> $TODAY" | mail -s "BACKUP ALERT" ops@example.com
fi

If today's backup size is less than half of yesterday's, something is off. This simple threshold works better in practice than any complicated monitoring. Tune the number based on your actual data growth; if you have 5 percent daily growth, a 50 percent drop definitely means an error.

Take exit codes seriously

In crontab, if a command finishes with an error, cron sends an email. But only if MAILTO is set and the server has an MTA. On most modern servers, neither is the case. The more reliable way is to capture the exit code inside the script itself:

mysqldump --single-transaction --routines --triggers \
  --databases app_db | gzip > /backup/db-$(date +%F).sql.gz
if [ ${PIPESTATUS[0]} -ne 0 ]; then
  curl -s -X POST https://hooks.example.com/alert -d "dump failed"
fi

Note that we check ${PIPESTATUS[0]}, not $?. Because $? returns the exit code of gzip, and gzip succeeds even on empty input. This is where people make mistakes: many use $? and think for months that the backup is healthy, while every night they're creating a gzipped file out of nothing.

Test recovery, not the backup

A backup that has never been restored isn't a backup; it's a file. Run this once a month on a separate server:

gunzip -c /backup/db-2025-01-15.sql.gz | mysql -u root -p restore_test
mysql -u root -p -e "SELECT COUNT(*) FROM restore_test.orders;"

If the record count matches the main server, the backup is healthy. If you get an ERROR 1064 or Table doesn't exist, it means the file is incomplete. Don't run this test on the main server itself; you might corrupt the live database. If you don't have a separate server, a temporary cloud server is enough for this job.

Comparing backup methods and their real cost

My choice for most projects: mysqldump with --single-transaction for databases under 50 gigabytes, and xtrabackup above that. If your database is under 50 gigs and you go for xtrabackup, you're buying unnecessary complexity. The reverse is also true: on a 200-gig database, mysqldump locks for several hours every night and your users feel the outages.

Where you keep the backup matters more than the backup itself

A backup on the same server isn't a backup. If the server is lost, both go together. At least one copy must be somewhere else. rsync over SSH is the simplest way:

rsync -avz --delete -e "ssh -p 2222" /backup/ backup@remote:/mnt/backups/$(hostname)/

Note that I've specified the SSH port explicitly. If you've changed the default port and don't include this, rsync gets rejected with Connection refused, and if it's in cron, it just sends a pointless email. To know which ports should be open and which shouldn't, see the reference for common service ports.

One note about encryption: if you're putting the backup on a third-party server, encrypt it before transfer. gpg --encrypt or openssl enc -aes-256-cbc both work. Don't keep the key on the same server that takes the backup; if the server is lost, the key is gone too and the backup becomes unrecoverable.

Monitoring: how to know last night's backup ran

The simplest way is a heartbeat file. The backup script creates a file with today's date at the end of a successful run. A separate script checks every morning whether this file belongs to today or not:

find /backup/.heartbeat -mtime +1 -type f | grep -q . && \
  echo "backup stale" | mail -s "ALERT" ops@example.com

This method is more reliable than log-based monitoring, because logs can fill up and rotate, but the heartbeat file is only created when the backup has actually finished. If you want to go deeper and understand why the server has slowed down or why the backup is taking so long, the guide to diagnosing high server load is a good starting point.

If you've just set up the server and haven't laid out the backup structure yet, before anything else take choosing a Linux distribution seriously, because backup tools have practical differences across distributions. And if you're working on a dedicated server, set aside disk space for backups from the start, not on the root partition.

Frequently asked questions

How do I know my automatic backup is actually running?

Look at the file's modification time, not its existence. The command stat /backup/db-$(date +%F).sql.gz should show today's date. If the file exists but the date is yesterday or older, the backup didn't run or was left half-finished.

The more reliable way is the heartbeat file: the backup script creates an empty file with today's date at the end of a successful run, and a separate script checks its existence every morning.

Why is my MySQL backup file empty?

It's almost always due to an authentication error or lack of access. If mysqldump can't connect to the database, it writes the error to stderr, but since the output is redirected to a file, an empty file gets created and the exit code may even be zero.

Solution: check the ${PIPESTATUS[0]} exit code and make sure ~/.my.cnf has the current password. Run the same command manually once and look at the output.

How often should I test backup recovery?

Once a month at minimum. If your data is critical, every two weeks. Don't do the recovery test on the main server; set up a separate environment and open the file there.

The success criterion is that the record count of the main table matches the restored version. If you just opened the file and got no errors, you haven't tested it.

Isn't a backup on the same server enough?

No. If the disk or server is lost, the backup goes with it. At least one copy must be at a separate destination, preferably in another datacenter.

If you're encrypting the backup, keep the key somewhere else. The key on the same server means an encrypted backup you can never open.

Run the backup script manually once today and look at the output file size. If it's smaller than expected, don't wait until tomorrow.