Automating MySQL backups with mysqldump and cron

Write a mysqldump backup script, store compressed dumps with rotation, and schedule it with cron so your MySQL or MariaDB data is recoverable.

  • Intermediate
  • Linux VPS
  • 6 min read

By RunRacks Team · Published

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:

Terminal
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:

Terminal
sudo mysql

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

SQL
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:

Terminal
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:

Terminal
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:

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

Write the backup script

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

Terminal
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:

Terminal
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:

Terminal
sudo crontab -e

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

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

Save and confirm:

Terminal
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:

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

Second, test the archive is readable and complete:

Terminal
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:

Terminal
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

If you need more CPU or RAM to run dumps alongside a busy site, look at our Linux VPS plans.

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Control panels

CyberPanel: back up and restore a website

Create on-demand and scheduled website backups in CyberPanel, download the archives off the server, and restore a site from a backup when something breaks.

  • Intermediate
  • Linux VPS
  • 6 min read