Automate Database Backups on Linux

By Kokil Thapa | Last reviewed: September 2026

You run a production app on Ubuntu and sleep better when dumps leave the server on a schedule. To automate database backups on Linux, you combine native dump tools, a scheduler, compression, encryption, and off-site copy. This guide covers MySQL 9.7, PostgreSQL 18, and MariaDB 12.3 on real servers — the same stack I use on Linux system administration client projects. No paid backup appliance required.

What is the best way to automate database backups on Linux?

A reliable backup pipeline has five layers. Each layer solves one failure mode. Skip any layer and you will eventually lose data you thought was safe.

The dump tool depends on your engine. Logical backups use mysqldump for MySQL and pg_dump for PostgreSQL. Physical backups use Percona XtraBackup or pg_basebackup when databases exceed a few gigabytes. Most small and mid-size Laravel or WordPress servers I maintain sit comfortably in the logical-backup camp.

Scheduler choice matters less than consistency. Cron is universal on Ubuntu 22/24. Systemd timers give better logging and dependency control. Application-level tools like Spatie Laravel Backup wrap dump and upload in one package — useful when the app team owns ops.

Core components you need before writing scripts

1. A dedicated Linux user with read-only database access — never run dumps as root against production.
2. A backup directory outside the web root, e.g. /var/backups/db/, owned by the backup user.
3. A .my.cnf or .pgpass file with mode 600 so passwords never appear in cron lines or ps output.
4. Enough disk space for at least two full dumps plus compression overhead.
5. An off-site destination: another VPS, NAS, or object storage synced nightly.

For broader server context, read database backup strategies for small servers and Ubuntu server backup strategies. Both align with the patterns below.

How do you set up automated MySQL backups with mysqldump and cron?

MySQL 9.7 still ships mysqldump as the standard logical backup tool. On a typical Ubuntu server running PHP 8.4 and Laravel 12, a nightly full dump plus binary log retention covers most recovery scenarios.

Create a credentials file

# /home/backup/.my.cnf
[client]
user=backup_user
password=STRONG_PASSWORD_HERE
host=127.0.0.1

Grant minimal privileges in MySQL:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

The official MySQL mysqldump reference documents every flag. For InnoDB apps, always use --single-transaction to avoid table locks.

Write the backup script

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/db/mysql"
RETAIN_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp_production"
FILENAME="${DB_NAME}_${TIMESTAMP}.sql.gz"

mkdir -p "$BACKUP_DIR"

mysqldump \
  --defaults-extra-file=/home/backup/.my.cnf \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  "$DB_NAME" | gzip -9 > "${BACKUP_DIR}/${FILENAME}"

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${RETAIN_DAYS} -delete

echo "[$(date -Is)] MySQL backup OK: ${FILENAME}" >> /var/log/db-backup.log

Save as /usr/local/bin/backup-mysql.sh, chmod 750, owned by the backup user. Test manually before scheduling.

Schedule with cron

# crontab -e (as backup user)
15 2 * * * /usr/local/bin/backup-mysql.sh

That runs at 02:15 daily — off-peak for Nepal-hosted apps where traffic dips after midnight NPT. Adjust for your timezone and load pattern.

Prefer systemd? See systemd service management on Linux for timer unit patterns. Timers retry cleanly after reboot — cron does not catch missed runs by default.

How do you automate PostgreSQL backups on a Linux server?

PostgreSQL 18 uses pg_dump for logical backups and pg_basebackup for physical replication-style copies. Most app databases under 50 GB fit the dump approach well.

Configure passwordless auth

# /home/backup/.pgpass
localhost:5432:myapp_production:backup_user:STRONG_PASSWORD_HERE

Set permissions: chmod 600 /home/backup/.pgpass. PostgreSQL reads this automatically when PGPASSFILE is set or the file lives in the home directory.

PostgreSQL backup script

#!/bin/bash
set -euo pipefail

export PGPASSFILE="/home/backup/.pgpass"
BACKUP_DIR="/var/backups/db/postgresql"
RETAIN_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp_production"
FILENAME="${DB_NAME}_${TIMESTAMP}.dump"

mkdir -p "$BACKUP_DIR"

pg_dump \
  -h localhost \
  -U backup_user \
  -Fc \
  -f "${BACKUP_DIR}/${FILENAME}" \
  "$DB_NAME"

find "$BACKUP_DIR" -name "*.dump" -mtime +${RETAIN_DAYS} -delete

echo "[$(date -Is)] PostgreSQL backup OK: ${FILENAME}" >> /var/log/db-backup.log

The -Fc flag produces a custom-format archive. It compresses internally and restores with parallel jobs via pg_restore. The official PostgreSQL pg_dump documentation covers format trade-offs.

For all databases on one host, loop through pg_dumpall --globals-only for roles and grants, then dump each database separately. Global objects matter when you rebuild a fresh cluster.

MariaDB 12.3 note

MariaDB accepts the same mysqldump workflow as MySQL 9.7. Use mariadb-dump if your distribution aliases it, but flags remain compatible. On mixed stacks — WordPress 7.1 on MariaDB plus a Laravel 13 API on PostgreSQL 18 — run separate scripts per engine.

mysqldump vs pg_dump vs physical backup tools — which should you use?

Tool choice depends on database size, downtime tolerance, and recovery time objective. The table below compares what I deploy on production Linux servers.

Pair full dumps with binary logs for point-in-time recovery on MySQL. See MySQL binary logs for replication and backup for binlog retention setup. PostgreSQL achieves the same with continuous WAL archiving — heavier to configure on a single VPS.

On a booking platform like Adventure Third Pole Trek, nightly mysqldump plus off-site sync has been sufficient for years. The database grew slowly. Restore tests mattered more than exotic tooling.

How do you encrypt backups and copy them off-server?

Local dumps on the same disk as production protect against application bugs, not disk failure or server compromise. Always copy encrypted archives elsewhere.

Encrypt with GPG before upload

gpg --symmetric --cipher-algo AES256 \
  --output "${BACKUP_DIR}/${FILENAME}.gpg" \
  "${BACKUP_DIR}/${FILENAME}"

rm "${BACKUP_DIR}/${FILENAME}"

Store the passphrase in a secrets file readable only by the backup user — not in the script itself. Generate strong passphrases with a password generator and record them in your team's vault.

For encryption architecture context, read database encryption at rest and in transit.

Sync off-site with rsync

rsync -avz --delete \
  -e "ssh -i /home/backup/.ssh/id_ed25519" \
  /var/backups/db/ \
  backup@remote.example.com:/backups/production-db/

Add this line to the end of your backup script. For object storage, use automated off-site backups to S3 or rclone. The rsync vs rclone comparison helps you pick based on destination type.

On shared EC2 infrastructure where I run Deployer 7 pipelines, sister legal-tech sites sync encrypted dumps to a separate availability zone nightly. Same pattern described in automate server backups with rsync and cron.

How do you verify automated database backups actually work?

An untested backup is a hope, not a backup. I schedule a monthly restore drill on a staging VPS — the same practice covered in database restore testing you should actually do.

MySQL restore test

gunzip -c /var/backups/db/mysql/myapp_20260901_021500.sql.gz \
  | mysql -u root -p staging_myapp

Run a row-count check against a known table. Compare checksums if the table is large. Log the result.

PostgreSQL restore test

createdb -U postgres staging_myapp
pg_restore -U postgres -d staging_myapp \
  /var/backups/db/postgresql/myapp_20260901_021500.dump

Verify extensions, sequences, and foreign keys survived. Custom-format dumps sometimes need --no-owner on restore to different users.

Monitoring and alerts

• Check exit codes — append || echo "FAIL" | mail -s "DB backup failed" admin@example.com or use a monitoring hook.
• Watch backup file size — a sudden 90% drop often means an empty dump from auth failure.
• Alert if no new file appears within 26 hours of the scheduled run.
• Log to syslog via logger so centralized monitoring picks it up.

For Laravel apps, combine OS-level dumps with application backups via Spatie. The app backup captures storage/ files that SQL dumps miss. Client portals like Mijar Law Associates store uploaded documents outside the database — both layers matter.

Full-stack coverage falls under support and maintenance services and domain registration and hosting when clients want hands-off ops.

What common mistakes break Linux database backup automation?

These failures show up repeatedly on production servers I troubleshoot.

• Credentials in crontab. Any user running ps aux sees the password. Use .my.cnf or .pgpass instead.
• Backing up to the same filesystem. Disk failure kills production and backups together. Sync off-server immediately after dump.
• No retention policy. Dumps fill the disk within weeks. Use find -mtime or logrotate-style pruning.
• Skipping grants and globals. Restoring data without users and permissions breaks the app silently.
• Never testing restore. Corrupt dumps go unnoticed for months. Schedule quarterly drills minimum.
• Running dumps during peak hours. Large tables lock or slow queries under load. Schedule off-peak.
• Forgetting binary logs. Full dumps alone only recover to dump time. Enable binlog retention for point-in-time recovery.

The complete server backup walkthrough in automated server backups complete setup extends these patterns to file-level backups. Pair both for full disaster recovery.

Redis 8.10 and Memcached 1.6.x caches are ephemeral — back up persistence files separately if you enable RDB/AOF, but treat cache as rebuildable unless it holds session data. Session-in-Redis setups need their own dump schedule.

Key Takeaways

• Automate database backups on Linux with mysqldump or pg_dump, cron or systemd, gzip compression, and GPG encryption.
• Store credentials in .my.cnf or .pgpass with mode 600 — never in crontab lines.
• Sync encrypted archives off-server nightly via rsync or rclone to a different machine or region.
• Rotate local files with find -mtime and keep at least 30 days of daily dumps.
• Test restores monthly on staging — verify row counts, grants, and uploaded files outside the database.
• Upgrade from logical to physical backups only when restore time exceeds your agreed recovery window.

People Also Ask

How often should you automate database backups on Linux?

Daily full logical dumps suit most production apps. High-transaction eCommerce sites may add hourly binlog shipping or WAL archiving for point-in-time recovery. Weekly backup verification on staging catches silent failures early.

Can you automate database backups without downtime?

Yes. Use mysqldump --single-transaction for InnoDB tables and pg_dump against a live PostgreSQL instance. Both run online. MyISAM tables still need brief locks — migrate to InnoDB if possible.

What is the best free tool to automate database backups on Linux?

Native dump tools plus cron cost nothing and work on any VPS. Spatie Laravel Backup adds convenience for PHP apps. Restic and BorgBackup add deduplicated encrypted archives when local storage is tight.

Should database backups be encrypted on Linux servers?

Always encrypt before off-site transfer. A database dump contains passwords, personal data, and payment records. GPG symmetric encryption with AES-256 is simple and script-friendly. See backup and disaster recovery strategy on the cloud for multi-tier retention design.

Build a backup pipeline you can trust

Automate database backups on Linux with boring, proven tools — dump, compress, encrypt, schedule, sync, and test. That six-step loop has recovered client data after bad deploys, accidental truncates, and full disk failures on servers I maintain. Start with tonight's cron job and a manual restore test this week.

Need help wiring backups into your Laravel app, WordPress shop, or legal-tech portal? Review the Quick And Easy Nepalese Grocery stack or browse more production portfolio work. For hands-on server setup, see Linux system administration in Nepal or enterprise application development. Questions about your specific stack? Contact us — a working backup plan beats a perfect one you never deployed.