Automating MySQL backups with mysqldump and cron

Automating MySQL backups with mysqldump and cron means one script runs on a schedule, writes a compressed dump of every database, and deletes old files so the disk never fills. You set it up once, then check the logs and test a restore — that is the whole job.

This guide builds a backup script, stores the dumps outside the web root, rotates them, and schedules the run with cron. It works on Ubuntu 22.04/24.04, Debian 12, and AlmaLinux/Rocky 8–9 with MySQL or MariaDB.

Before you start

You need:

• A Linux VPS with root or sudo access, and MySQL or MariaDB running.
• A dedicated backup user with read access to the databases you want to dump. Do not use root in the script.
• Enough free disk space for at least two full dump sets. Check with df -h and see how to check database and table sizes if you are unsure how big the dumps will be.
• A backup directory outside the web root, for example /var/backups/mysql.

Create the directory and lock it down:

sudo mkdir -p /var/backups/mysql
sudo chmod 700 /var/backups/mysql
sudo chown root:root /var/backups/mysql

Create a dedicated backup user

Log in to MySQL as an admin:

sudo mysql

Then create the user. Grant only what a dump needs:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'use-a-long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
EXIT;

LOCK TABLES and RELOAD let mysqldump take a consistent snapshot. If your account cannot have them, use --single-transaction instead (shown below) — it works for InnoDB tables without locking.

Test the login before you script anything:

mysql -u backup -p -e "SHOW DATABASES;"

If that fails with error 1045, see Fix MySQL or MariaDB error 1045: Access denied for user.

Store the credentials safely

Do not put the password on the command line — it shows up in ps output. Use an option file:

sudo tee /root/.my-backup.cnf > /dev/null <<'EOF'
[client]
user=backup
password=use-a-long-random-password
host=localhost
EOF

sudo chmod 600 /root/.my-backup.cnf

Confirm it works:

mysql --defaults-extra-file=/root/.my-backup.cnf -e "SELECT 1;"

Write the backup script

Create /usr/local/bin/mysql-backup.sh:

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

BACKUP_DIR="/var/backups/mysql"
DEFAULTS_FILE="/root/.my-backup.cnf"
RETENTION_DAYS=14
STAMP="$(date +%Y-%m-%d_%H%M)"
LOG="/var/log/mysql-backup.log"

log() { echo "$(date '+%Y-%m-%d %H:%M:%S') $*" >> "$LOG"; }

log "Backup started"

# Dump every database except the system schemas
DATABASES=$(mysql --defaults-extra-file="$DEFAULTS_FILE" -N -B -e \
  "SELECT schema_name FROM information_schema.schemata
   WHERE schema_name NOT IN ('information_schema','performance_schema','mysql','sys');")

if [ -z "$DATABASES" ]; then
  log "No user databases found, nothing to do"
  exit 0
fi

for DB in $DATABASES; do
  FILE="$BACKUP_DIR/${DB}_${STAMP}.sql.gz"
  if mysqldump --defaults-extra-file="$DEFAULTS_FILE" \
       --single-transaction --quick --routines --triggers --events \
       --default-character-set=utf8mb4 "$DB" | gzip -9 > "$FILE"; then
    log "OK  $DB -> $FILE ($(du -h "$FILE" | cut -f1))"
  else
    log "FAIL $DB"
    rm -f "$FILE"
    exit 1
  fi
done

# Remove dumps older than the retention window
find "$BACKUP_DIR" -type f -name '*.sql.gz' -mtime +"$RETENTION_DAYS" -delete
log "Backup finished, removed files older than ${RETENTION_DAYS} days"
EOF

sudo chmod 750 /usr/local/bin/mysql-backup.sh

What the flags do:

• --single-transaction gives a consistent snapshot of InnoDB tables without locking them, so the site keeps working.
• --quick streams rows instead of buffering the whole table in memory — important on a small VPS.
• --routines --triggers --events includes stored procedures, triggers and scheduled events. Without them, a restore silently loses logic.
• gzip -9 shrinks the dump; text SQL compresses well.

Run it once by hand:

sudo /usr/local/bin/mysql-backup.sh
sudo tail -n 20 /var/log/mysql-backup.log
ls -lh /var/backups/mysql

You should see one .sql.gz file per database, named with the timestamp.

Schedule it with cron

Open root's crontab:

sudo crontab -e

Add a nightly run at 02:30, with output discarded because the script logs itself:

30 2 * * * /usr/local/bin/mysql-backup.sh >/dev/null 2>&1

Save and confirm:

sudo crontab -l

Cron uses the system timezone. Check it with timedatectl and pick an hour when your traffic is lowest. If you want a second daily run, add another line — for example 30 14 * * * for an afternoon copy.

Check it works

A backup you have never restored is a guess. Verify in three steps.

First, confirm the schedule fired:

sudo grep "$(date +%Y-%m-%d)" /var/log/mysql-backup.log

Second, test the archive is readable and complete:

gzip -t /var/backups/mysql/yourdb_*.sql.gz && echo "archive OK"
zcat /var/backups/mysql/yourdb_*.sql.gz | tail -n 5

The last lines should contain Dump completed. If they do not, the dump was cut short.

Third, restore into a scratch database and compare row counts:

mysql --defaults-extra-file=/root/.my-backup.cnf -e "CREATE DATABASE restore_test;"
zcat /var/backups/mysql/yourdb_*.sql.gz | mysql --defaults-extra-file=/root/.my-backup.cnf restore_test
mysql --defaults-extra-file=/root/.my-backup.cnf -e "SELECT COUNT(*) FROM restore_test.your_table;"
mysql --defaults-extra-file=/root/.my-backup.cnf -e "DROP DATABASE restore_test;"

If the counts match, your backup is real. Full restore options are covered in How to back up and restore MySQL databases with mysqldump.

Troubleshooting

• Log shows mysqldump: Got error: 1045: Access denied — the credentials in /root/.my-backup.cnf are wrong or the grants are missing. Re-run the GRANT statement and test with mysql --defaults-extra-file=/root/.my-backup.cnf -e "SELECT 1;".
• Backup runs but files are 20 bytes — the dump failed and gzip compressed an empty stream. Check the log line for that database; usually it is a missing LOCK TABLES privilege or a table the backup user cannot read.
• Disk fills up — retention is too long or dumps are growing. Lower RETENTION_DAYS, check df -h, and move older copies off the server rather than keeping them locally.
• Cron never runs the script — cron has a minimal PATH and no environment. Use absolute paths inside the script (as above) and check /var/log/syslog or journalctl -u cron for entries.
• Dump takes hours and blocks writes — you are missing --single-transaction, or you have large MyISAM tables that still lock. Convert them to InnoDB or accept a maintenance window.

Next steps

• Schedule tasks with cron on Linux for the syntax and timezone details.
• Automating server maintenance with cron jobs and shell scripting to fold this script into a wider routine.
• Back up your Linux VPS with rsync, restic and cron to copy dumps to another machine.
• Tune MySQL and MariaDB memory settings for a small VPS if dumps are straining your server.