Nightly MySQL and file backups with a shell script and cron

Most of the self-hosted apps we look after are a database plus a folder of uploads and config. You don't need a backup suite for that. A short shell script, cron and a second location do the job, as long as the database dump is consistent, retention is sensible and a copy gets off the box. Then you test the restore, because the day someone calls about missing files is a bad day to find out your backups were empty.

You'll need the host running the app with MySQL or MariaDB, mysqldump and gzip (in the MySQL/MariaDB client package), and somewhere off-box. That can be another server you can rsync to over SSH (192.0.2.10 in the examples) or an object storage bucket with rclone configured.

Keep the password off the command line

A password on the command line shows up in ps and shell history. Put it in /root/.backup.my.cnf instead:

[client]
user=backup
password=long-random-string

chmod 600 /root/.backup.my.cnf

Give that MySQL user only what a dump needs, which is SELECT, SHOW VIEW, TRIGGER, LOCK TABLES and PROCESS. MySQL 8 wants PROCESS for tablespace information, or you can use --no-tablespaces.

The script

Save this as /usr/local/sbin/backup-myapp.sh:

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

APP="myapp"
DB_NAME="myapp"
APP_DIR="/var/www/myapp"
BACKUP_DIR="/var/backups/${APP}"
RETENTION_DAYS=14
REMOTE="backup@192.0.2.10:/srv/backups/${APP}/"
STAMP="$(date +%F_%H%M)"

umask 077
mkdir -p "${BACKUP_DIR}"

DB_FILE="${BACKUP_DIR}/${APP}_db_${STAMP}.sql.gz"
FILES_FILE="${BACKUP_DIR}/${APP}_files_${STAMP}.tar.gz"

# Remove partial files if anything fails
cleanup() { rm -f "${DB_FILE}.tmp" "${FILES_FILE}.tmp"; }
trap cleanup EXIT

echo "$(date -Is) starting backup of ${APP}"

# 1. Database: consistent snapshot without locking InnoDB tables
mysqldump --defaults-extra-file=/root/.backup.my.cnf \
    --single-transaction --quick --routines --triggers --no-tablespaces \
    "${DB_NAME}" | gzip > "${DB_FILE}.tmp"
gzip -t "${DB_FILE}.tmp"
mv "${DB_FILE}.tmp" "${DB_FILE}"

# 2. Uploads and config
tar -czf "${FILES_FILE}.tmp" -C "${APP_DIR}" uploads config
mv "${FILES_FILE}.tmp" "${FILES_FILE}"

# 3. Local retention
find "${BACKUP_DIR}" -type f -name "${APP}_*" -mtime +"${RETENTION_DAYS}" -delete

# 4. Off-box copy
rsync -a "${BACKUP_DIR}/" "${REMOTE}"

echo "$(date -Is) backup of ${APP} complete"

chmod 700 /usr/local/sbin/backup-myapp.sh
/usr/local/sbin/backup-myapp.sh
ls -lh /var/backups/myapp/

A few parts of that script are there because they've burned us before. set -euo pipefail stops the script on any error, including a failed mysqldump in the middle of a pipe. Without pipefail, a failed dump piped into a successful gzip counts as success, and you get a tiny, useless file every night.

--single-transaction takes a consistent snapshot of InnoDB tables without locking them, so people can keep using the app during the dump. It doesn't protect MyISAM tables, so if your app still uses those, check before relying on it. --quick streams rows instead of buffering whole tables in memory.

Writing to .tmp and renaming means a failed run never leaves behind a file that looks like a good backup, and gzip -t checks the archive is intact. The find -mtime +14 -delete line removes local copies older than 14 days, matched by name so it can't wander into other files.

rsync -a without --delete means the remote keeps files even after local retention removes them, so give the remote its own retention job. That longer remote history matters more than it sounds. If backups have been failing for longer than your local retention window, local cleanup can leave you with nothing usable, and the remote is all you've got.

Getting a copy off the box

For rsync, set up an SSH key for root to the backup host and restrict it on that end. For object storage, swap the last step for rclone:

rclone copy "${BACKUP_DIR}" "b2:example-backups/${APP}" --max-age 48h

On-box backups cover you for a bad update or an accidental delete. They do nothing for a dead disk, a deleted VM, a compromised host or ransomware, because those take the backups along with the data. I like a pull-based setup even better, where the backup server connects in and fetches the files, so a compromised app server has no credentials that can delete the backup history. Object storage with versioning or object lock gets you something similar.

Scheduling

Create /etc/cron.d/backup-myapp:

30 2 * * * root /usr/local/sbin/backup-myapp.sh >> /var/log/backup-myapp.log 2>&1

Files in /etc/cron.d need the user field (root here), unlike a personal crontab. Pick a quiet time and stagger it from other heavy jobs.

Cron won't tell anyone when a job fails. Have the script ping a dead man's switch service (healthchecks-style) on success, so you get alerted when the ping doesn't arrive.

LATEST=$(ls -1t /var/backups/myapp/myapp_db_*.sql.gz | head -n1)

mysql -u root -p -e "CREATE DATABASE restore_test;"
gunzip -c "$LATEST" | mysql -u root -p restore_test
mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='restore_test';"
mysql -u root -p -e "DROP DATABASE restore_test;"

mkdir -p /tmp/restore-test
tar -xzf "$(ls -1t /var/backups/myapp/myapp_files_*.tar.gz | head -n1)" -C /tmp/restore-test
ls -R /tmp/restore-test | head
rm -rf /tmp/restore-test