Automated MySQL/MariaDB Backup on VPS: mysqldump + cron

June 2026 · 7 min read

Your database is the heart of most websites. Losing your database means losing everything. Setting up automated backups to run every night should be the first thing you do when you launch a VPS. This article will walk you through a complete MySQL and MariaDB backup system—from basic mysqldump commands all the way to offsite backups that protect you even if your entire VPS fails.

Basic Backup with mysqldump

# Backup all databases
mysqldump -u root -p --all-databases > all-databases.sql

# Backup a single database
mysqldump -u root -p mydb > mydb.sql

# Backup with compression on the fly
mysqldump -u root -p mydb | gzip > mydb-$(date +%Y%m%d).sql.gz

# Backup with single-transaction (no table locking — best for InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gz

Create a Dedicated Backup User

Never use root for backups. If your credentials leak, an attacker gets full access to everything. Create a backup user with only the minimum permissions needed to read and lock tables:

# Create backup user
mysql -u root -p
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongP@ss123';
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Create a .my.cnf File to Skip Password Prompts

# Create /root/.my.cnf (or /home/backupuser/.my.cnf)
nano /root/.my.cnf

[mysqldump]
user=backup_user
password=StrongP@ss123
single-transaction
quick
max_allowed_packet=512M

# Restrict file permissions so only root can read it
chmod 600 /root/.my.cnf

# Test backup without typing a password
mysqldump --all-databases | gzip > test.sql.gz

Create a Complete Backup Script

# Create /usr/local/bin/mysql-backup.sh
nano /usr/local/bin/mysql-backup.sh

#!/bin/bash
# MySQL Daily Backup Script

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql-backup.log"

mkdir -p "$BACKUP_DIR"

log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

log "Starting MySQL backup..."

# Backup all databases, one file per database
mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)" | while read db; do
  OUTFILE="$BACKUP_DIR/${db}_${DATE}.sql.gz"
  mysqldump --single-transaction "$db" | gzip > "$OUTFILE"
  if [ $? -eq 0 ]; then
    SIZE=$(du -sh "$OUTFILE" | cut -f1)
    log "OK: $db -> $OUTFILE ($SIZE)"
  else
    log "ERROR: Failed to backup $db"
  fi
done

# Delete backups older than RETENTION_DAYS days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
log "Cleaned up backups older than $RETENTION_DAYS days"
log "Backup completed."

# Make script executable
chmod +x /usr/local/bin/mysql-backup.sh

# Test run it
/usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/

Set Up cron to Run Backups Automatically Every Night

crontab -e

# Add this line: run backup every day at 3 AM
0 3 * * * /usr/local/bin/mysql-backup.sh

Offsite Backup to S3 (AWS/Cloudflare R2/Wasabi)

Backup on the same VPS is not safe enough. If your VPS fails, disk corrupts, or ransomware hits, your backup disappears too. Always upload backups to another location:

# Install AWS CLI or rclone
apt install awscli -y
# or
curl https://rclone.org/install.sh | sudo bash

# Configure rclone (supports S3, Cloudflare R2, Wasabi, SFTP, Google Drive, etc.)
rclone config

# Add to your backup script after backup completes
rclone copy "$BACKUP_DIR" remote:my-db-backups/$(hostname) --max-age 7d
log "Uploaded to remote storage"

Offsite Backup via SFTP/SCP

# Create SSH key pair (passwordless)
ssh-keygen -t ed25519 -f /root/.ssh/backup_key -N ""

# Copy public key to backup server
ssh-copy-id -i /root/.ssh/backup_key.pub [email protected]

# Add to your backup script
rsync -azq -e "ssh -i /root/.ssh/backup_key" \
  "$BACKUP_DIR/" [email protected]:/backups/$(hostname)/

Test Restore: Verify Your Backups Actually Work

# Test restore to temporary database
mysql -u root -p -e "CREATE DATABASE test_restore;"
zcat /var/backups/mysql/mydb_20260607_030001.sql.gz | mysql -u root -p test_restore

# Verify data is complete
mysql -u root -p test_restore -e "SHOW TABLES; SELECT COUNT(*) FROM users;"

# Clean up test database
mysql -u root -p -e "DROP DATABASE test_restore;"

Binary Logs for Point-in-Time Recovery

mysqldump is a "snapshot" at the moment of backup. If you need to recover to the exact minute your database crashed, you need binary logging enabled too:

# Add to /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800  ; Keep 7 days
max_binlog_size = 100M

# Restart MySQL
systemctl restart mysql

A solid backup system needs four things: automated daily backups, a retention policy that fits your needs, offsite copies, and regular restore tests. With these four steps, you can rest assured that no matter what happens to your VPS, your data will survive.

Verify Your Backup is Working Correctly

# Test that the backup script runs
/usr/local/bin/mysql-backup.sh

# Check the backup files created
ls -lh /var/backups/mysql/
# You should see .sql.gz files from the last run

# Verify gzip files are not corrupted
gzip -t /var/backups/mysql/mydb_*.sql.gz && echo "Backup file OK"

# View the backup log
tail -20 /var/log/mysql-backup.log

Common Issues & Troubleshooting

Error: "Access denied for user 'backup_user'" during mysqldump

The backup user may lack sufficient privileges, or .my.cnf is misconfigured:

# Test backup user login
mysql -u backup_user -p -e "SHOW DATABASES;"

# Check user permissions
mysql -u root -p -e "SHOW GRANTS FOR 'backup_user'@'localhost';"
# Should have: SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT

# Check .my.cnf configuration
cat /root/.my.cnf

Error: Backup file is 0 bytes or unusually small

This usually happens if disk is full or mysqldump failed silently:

# Check disk space
df -h /var/backups/

# Run mysqldump directly to see errors (not through script)
mysqldump --single-transaction mydb 2>&1 | head -5
# If there's an error, you'll see it here

# Make sure your script redirects stderr correctly
# Should have: 2>&1 in the cron command

Error: "ERROR 2013 (HY000): Lost connection to MySQL server during query"

Backup took too long and the connection timed out, especially with large databases:

# Increase timeout in mysqldump
mysqldump --single-transaction --net-read-timeout=300 --net-write-timeout=300 mydb | gzip > backup.sql.gz

# Or add to /root/.my.cnf
[mysqldump]
net_read_timeout=300
net_write_timeout=300

Error: cron backup doesn't run even though manual runs work

cron doesn't have the same PATH environment as your shell—always use full paths:

# Fix crontab to use full paths
0 3 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

# Check if cron service is running
systemctl status cron

# View cron logs
grep "mysql-backup" /var/log/syslog | tail -10

Error: Need to restore only one specific table, not the whole database

You can restore a single table by extracting just that table's section from the dump:

# Extract only the create+insert of a specific table
zcat backup.sql.gz | sed -n '/CREATE TABLE `users`/,/CREATE TABLE `/{ /CREATE TABLE `users`/,/UNLOCK TABLES/p }' > users_only.sql

mysql -u root -p mydb < users_only.sql

Frequently Asked Questions

Is --single-transaction necessary? Can I use it with every database?

--single-transaction works best with InnoDB, which is MySQL/MariaDB's main storage engine. It allows backup to run without locking tables, so your website stays responsive during backup. If you're using MyISAM (very old), use --lock-tables instead. For WordPress and modern applications, --single-transaction works perfectly.

Is offsite backup really necessary if I have snapshots from my VPS provider?

Provider snapshots are excellent disk-level backups, but they have limits: some can only restore the entire disk (not individual databases), they're more expensive, and if the provider has an outage, your snapshots might go down too. Having mysqldump backups stored offsite gives you another layer of protection and more flexibility when restoring specific databases or tables.

How do I backup a large database (50GB+) faster?

For large databases, use mydumper (parallel dump) instead of mysqldump, which runs single-threaded: apt install mydumper then mydumper -u root -B mydb -o /backup/ mydumper dumps multiple tables in parallel, making it 5-10x faster than mysqldump. You can also restore with myloader.