Database Sync

Rsync handles files brilliantly, but most web applications also depend on a database. Without a coordinated file + database backup strategy, your restores will be incomplete — files from one point in time, database from another.

info

For details on why you should never rsync live database files directly, see Live Replication Edge Cases.

The Problem

What You Backup	What You Get Back
Files only	Site looks broken — no content, no users, no settings
Database only	Database without application code, themes, uploads
Files + Database together	Complete, functional restore

Coordinated Backup Workflow

MySQL / MariaDB

#!/bin/bash
# coordinated-backup.sh — Files + Database together
set -euo pipefail

TIMESTAMP=$(date +%F_%H%M)
BACKUP_DIR="/backup/$TIMESTAMP"
mkdir -p "$BACKUP_DIR"

# 1. Export database (consistent snapshot)
mysqldump --defaults-file=/root/.my.cnf \
 --single-transaction \
 --routines --triggers --events \
 --all-databases | gzip > "$BACKUP_DIR/databases.sql.gz"

# 2. Backup files (incremental)
rsync -av --link-dest=/backup/latest \
 /var/www/html/ "$BACKUP_DIR/files/"

# 3. Update latest pointer
ln -sfn "$BACKUP_DIR" /backup/latest

echo " Files + DB backed up: $BACKUP_DIR"

PostgreSQL

# Export all PostgreSQL databases
pg_dumpall -U postgres | gzip > "$BACKUP_DIR/postgres.sql.gz"

# Or export a specific database
pg_dump -U postgres -d myapp | gzip > "$BACKUP_DIR/myapp.sql.gz"

# Then rsync alongside files
rsync -av --link-dest=/backup/latest \
 /var/www/html/ "$BACKUP_DIR/files/"

Credential Management

warning

Never put database passwords in scripts or crontabs. Use config files:

MySQL

/root/.my.cnf

[mysqldump]
user = backup_user
password = secure_password

chmod 600 /root/.my.cnf
# Now mysqldump works without -u/-p flags:
mysqldump --defaults-file=/root/.my.cnf --all-databases

PostgreSQL

/root/.pgpass

localhost:5432:*:backup_user:secure_password

chmod 600 /root/.pgpass
# Now pg_dump works without password prompt:
pg_dump -U backup_user myapp

Migration: Moving to a New Server

Full Site Migration

# On old server: create backup
mysqldump --defaults-file=/root/.my.cnf \
 --single-transaction --all-databases | \
 gzip > /tmp/databases.sql.gz

# Transfer everything to new server
rsync -avzP /var/www/html/ user@newserver:/var/www/html/
rsync -avzP /tmp/databases.sql.gz user@newserver:/tmp/

# On new server: import
gunzip < /tmp/databases.sql.gz | mysql -u root -p
sudo chown -R www-data:www-data /var/www/html

Staging Refresh from Production

# Pull latest production data to staging
rsync -avz user@production:/var/www/html/ /var/www/staging/

# Pull and import production database
ssh user@production "mysqldump --single-transaction app_db" | \
 mysql staging_db

# Update URLs if needed (WordPress example)
wp search-replace 'production.com' 'staging.example.com' \
 --path=/var/www/staging/ --skip-columns=guid

High-Frequency Database Backups

For high-traffic sites where data changes rapidly:

# Hourly database export (cron)
0 * * * * mysqldump --defaults-file=/root/.my.cnf \
 --single-transaction myapp_db | \
 gzip > /backup/db/hourly_$(date +\%F_\%H).sql.gz

# Daily file backup (cron)
0 2 * * * rsync -av --link-dest=/backup/latest \
 /var/www/html/ /backup/$(date +\%F)/ && \
 ln -sfn /backup/$(date +\%F) /backup/latest

# Weekly cleanup (cron)
0 3 * * 0 find /backup/db/ -name "hourly_*" -mtime +7 -delete

Verifying Database Backups

# Test that the dump is valid (check header)
zcat /backup/latest/databases.sql.gz | head -20

# Check file size (should be non-zero and reasonable)
ls -lh /backup/latest/databases.sql.gz

# Full restore test to a temporary database
gunzip < /backup/latest/databases.sql.gz | mysql test_restore_db
mysql test_restore_db -e "SHOW TABLES;" | wc -l
mysql -e "DROP DATABASE test_restore_db;"

Common Pitfalls

Pitfall	Consequence	Prevention
Backing up files without database	Incomplete restore	Always export DB alongside files
Password in crontab/script	Security risk, visible in ps	Use .my.cnf or .pgpass
No --single-transaction	Table locks block live site	Always use for InnoDB/live servers
Database and files from different times	Inconsistent restore	Export DB first, then rsync files
Not testing restore	Corrupt dump goes unnoticed	Periodically restore to test DB

Quick Reference

# Export MySQL + rsync files
mysqldump --defaults-file=/root/.my.cnf --single-transaction \
 --all-databases | gzip > /backup/db.sql.gz
rsync -av /var/www/html/ /backup/files/

# Export PostgreSQL + rsync files
pg_dumpall | gzip > /backup/postgres.sql.gz
rsync -av /var/www/html/ /backup/files/

# Restore MySQL
gunzip < /backup/db.sql.gz | mysql -u root -p

# Restore PostgreSQL
gunzip < /backup/postgres.sql.gz | psql -U postgres

# Verify dump
zcat /backup/db.sql.gz | head -20

What's Next

• Database Export and Rsync — Detailed export techniques
• Backup Strategies — Design your backup architecture
• Disaster Recovery — Restore from coordinated backups