MySQL Backup on VPS: Automation

Losing a database is a business catastrophe. Automatic daily backups are insurance you set up once and forget about.

Simple backup script

#!/bin/bash
# /usr/local/bin/mysql-backup.sh

DB_USER="root"
DB_PASS="YourPassword"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M)
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR"

# Get list of all databases
DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" |   grep -Ev "(Database|information_schema|performance_schema|sys)")

for DB in $DATABASES; do
    echo "Backup: $DB"
    mysqldump -u"$DB_USER" -p"$DB_PASS"       --single-transaction       --routines       --triggers       "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
done

# Delete old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$KEEP_DAYS" -delete

echo "Backup complete: $(ls -lh $BACKUP_DIR | tail -5)"

chmod +x /usr/local/bin/mysql-backup.sh

# Add to cron (daily at 2:00)
echo "0 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1"   >> /etc/crontab

Restoring from backup

# Unpack and restore
gunzip < /var/backups/mysql/mydb_2026-01-25_02-00.sql.gz |   mysql -u root -p mydb

Storing backups in the cloud (S3/Backblaze)

# Install AWS CLI
sudo apt install -y awscli

# Configure
aws configure

# Upload to S3
aws s3 sync /var/backups/mysql/ s3://your-bucket/mysql-backups/   --storage-class STANDARD_IA

Diagnosing common backup errors

mysqldump fails with "Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)'" — this happens when using --single-transaction with a user lacking the PROCESS privilege needed to read the process list while taking a consistent snapshot.

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

Grant the needed rights: GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'backup_user'@'localhost'; — a dedicated backup-only user is safer than putting root credentials in a script.

The backup file is 0 bytes or cuts off halfway through — a typical sign that disk space ran out while the dump was running, and the script does not check the exit code of mysqldump.

df -h /var/backups
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   20G     0 100% /

mysqldump: Got errno 28 on write

errno 28 is ENOSPC, "No space left on device". Add a free-space check before the script starts and an exit-code check after mysqldump, so a broken archive is never stored as if it were a successful backup.

Backup strategies compared

When mysqldump is not the right tool

• The database is larger than 50-100 GB — mysqldump produces a text SQL dump that can take hours to restore; for that size use a physical backup with XtraBackup instead.
• You need a backup with no write pause and no I/O load during business hours — dumping a large database creates noticeable load, so schedule it for a nighttime window.
• You need point-in-time recovery (rolling back to a specific second) — that requires binary logs and a separate strategy, not just a periodic dump.

Verifying the result

1. Check the archive is not empty or corrupted: gzip -t /var/backups/mysql/mydb_*.sql.gz — the command must finish without errors.
2. Restore the dump into a test database and compare the table count: mysql -e "SHOW TABLES" testdb | wc -l must match the source database.
3. Confirm cron is actually running the job: grep mysql-backup /var/log/syslog must show an entry for every day.
4. Confirm the copy really lands in the cloud: aws s3 ls s3://your-bucket/mysql-backups/ | tail -5 must show recent files.

For setting up MariaDB itself before backing it up, see the article on installing and configuring MariaDB, and for restoring large dumps see the guide to importing large dumps.

Frequently asked questions

Do I need to stop the MySQL server before running mysqldump?

No, stopping the server isn't necessary — that's exactly what the single-transaction flag is for, letting it take a consistent snapshot of InnoDB tables without locking writes. The application keeps running during the dump, reading and writing data as usual, and the backup still comes out consistent thanks to the transactional mechanism rather than a service stop.

Can a backup be restored into a database with a different name than the original?

Yes, an mysqldump file is just plain SQL text with commands that create and populate tables, so it can be loaded into a database under any name as long as that database is created first and referenced during restore. The database name inside the dump isn't tied to the original one, unless the dump covers an entire named database.

Is it enough to keep the backup locally on the same server the database runs on?

No, keeping a backup only on the same server is risky — if the server is hacked, fails, or the disk gets damaged, the local copy disappears along with the original. A proper strategy means shipping a copy off the server, to cloud storage or another machine, otherwise the whole point of backing up is lost.

Do site files need a separate backup, or is backing up the database alone enough?

A database-only backup covers table structure and content, but not the site's files — uploaded media, application configuration, or custom code that usually isn't stored in the database at all. A complete recovery strategy includes a separate backup of the filesystem alongside the database dump.