Implementing Automated Database Backups with Incremental Snapshots Using rsync and Cron on Linux

Databases are the backbone of almost every IT system, and backup strategies are crucial to prevent data loss. While full backups are standard, they can be time-consuming and storage-intensive for large databases.

This guide explains how to implement automated incremental backups for MySQL/MariaDB/PostgreSQL using rsync and cron. Incremental backups save time, bandwidth, and storage, making it ideal for production environments.

Real-Life Scenario

Scenario:

• A company hosts multiple MySQL databases on a Linux server.

• Daily full backups are slow and consume a lot of disk space.

• They need a solution that backs up only changed data while maintaining daily full backups weekly.

Solution: Incremental backups with rsync and automated cron jobs.

Step 1: Prerequisites

• Linux server (Ubuntu, CentOS, Debian, Rocky)

• Root or sudo access

• rsync installed:

sudo apt install rsync -y        # Debian/Ubuntu
sudo dnf install rsync -y        # RHEL/CentOS/AlmaLinux

• MySQL/MariaDB/PostgreSQL installed

• Sufficient disk space for backups

Step 2: Prepare Backup Directories

Create directories for full and incremental backups:

sudo mkdir -p /backup/db/full
sudo mkdir -p /backup/db/incremental
sudo chown -R $(whoami):$(whoami) /backup/db

Step 3: Take Initial Full Backup

For MySQL/MariaDB:

mysqldump -u root -p --all-databases > /backup/db/full/db_full_$(date +%F).sql

For PostgreSQL:

sudo -u postgres pg_dumpall > /backup/db/full/db_full_$(date +%F).sql

Explanation:

• --all-databases / pg_dumpall → Dumps entire database server

• $(date +%F) → Adds timestamp to backup file

Step 4: Create Incremental Backup Script

Create /usr/local/bin/db_incremental_backup.sh:

#!/bin/bash

# Source and destination directories
SRC="/backup/db/full/"
DEST="/backup/db/incremental/"
DATE=$(date +%F_%H-%M)

# Create destination directory
mkdir -p $DEST/$DATE

# Rsync incremental backup
rsync -av --link-dest=$SRC $SRC $DEST/$DATE

# Optional: Keep only last 7 incremental backups
cd /backup/db/incremental
ls -1tr | head -n -7 | xargs -d '\n' rm -rf --

Make it executable:

chmod +x /usr/local/bin/db_incremental_backup.sh

Explanation:

• --link-dest=$SRC → Links unchanged files to previous backup (saves space)

• ls -1tr | head -n -7 | xargs rm -rf → Deletes older incremental backups

Step 5: Schedule Automated Backups with Cron

Edit crontab:

crontab -e

Add:

# Full backup weekly on Sunday at 2:00 AM
0 2 * * 0 mysqldump -u root -pYourPassword --all-databases > /backup/db/full/db_full_$(date +\%F).sql

# Incremental backup daily at 2:00 AM (except Sunday)
0 2 * * 1-6 /usr/local/bin/db_incremental_backup.sh

Explanation:

• Sunday → Full backup

• Monday–Saturday → Incremental backup

Step 6: Real-Life Example

• Full backup size: 100GB

• Daily incremental: 5–10GB (only changed data)

• Storage saved: ~70% compared to daily full backups

• Recovery is fast: restore last full backup + latest incremental

Step 7: Restore from Incremental Backup

1. Restore the full backup:

mysql -u root -p < /backup/db/full/db_full_2026-01-28.sql

2. Apply incremental backups in chronological order:

mysql -u root -p < /backup/db/incremental/2026-01-29/db_full_2026-01-28.sql

Repeat for each day to reach the latest state.

Step 8: Best Practices

• Encrypt backups for sensitive databases:

gpg -c /backup/db/full/db_full_$(date +%F).sql

• Offload backups to remote storage (S3, NFS, or another server):

rsync -avz /backup/db/ user@backup-server:/backups/db/

• Test restores weekly to ensure integrity

• Monitor backup disk usage and cron logs

Benefits

• Save disk space with incremental backups

• Automate backup scheduling

• Easy integration with offsite backups

• Production-safe and low-impact on server performance

💡 Pro Tip: Combine this with ZFS snapshots or LVM snapshots for near-instant backups with minimal downtime, especially on large-scale production servers.