Live Replication Edge Cases
This is one of the most dangerous rsync scenarios: trying to rsync live database files directly from /var/lib/mysql/ or /var/lib/postgresql/ instead of using proper export tools. This page explains why it fails, when it's safe, and what to use instead.
Critical Warning
Never rsync live database data directories. Database files are continuously being written to, locked, and updated by the database engine. Rsyncing them while the database is running produces a corrupt, unrestorable backup in most cases.
Why Rsync Fails on Live Databases
How Database Storage Works
Databases don't just write data to files — they maintain a complex system of buffers, journals, and locks:
flowchart TD
APP["Application Writes"] --> BUF["Memory Buffer Pool<br/>(data in RAM, not yet on disk)"]
BUF --> WAL["Write-Ahead Log<br/>(transaction journal)"]
WAL --> DATA["Data Files on Disk<br/>(.ibd, .frm, base/)"]
BUF -.-> DATA
RSYNC["Rsync reads HERE "] --> DATA
style RSYNC fill:#ff4444,color:#fff
style BUF fill:#ff9900,color:#000
When rsync copies database files, it sees:
- Partially written data — a file might be mid-update
- Stale data — the buffer pool hasn't been flushed to disk yet
- Inconsistent state — different files represent different points in time
The Result
| Scenario | What Happens | Outcome |
|---|---|---|
| Rsync during active writes | Files copied at different timestamps | Corrupt backup — relationships between tables are broken |
| Rsync during transaction | Transaction log and data files don't match | Unrecoverable — database can't replay partial transactions |
| Rsync with InnoDB tables | Buffer pool not flushed | Missing data — recent writes exist only in RAM |
Database File Types (What You're Rsyncing)
MySQL / MariaDB
| File | Purpose | Safe to Rsync Live? |
|---|---|---|
ibdata1 | InnoDB shared tablespace | No — actively locked |
*.ibd | InnoDB per-table tablespace | No — constantly updated |
*.frm | Table structure definitions | Maybe — rarely changes but depends on others |
ib_logfile* | InnoDB redo logs | No — critical for crash recovery |
mysql-bin.* | Binary log (replication) | Yes — append-only, safe to copy |
*.MYD / *.MYI | MyISAM data/index | Risky — less dangerous than InnoDB but still unsafe |
PostgreSQL
| File/Directory | Purpose | Safe to Rsync Live? |
|---|---|---|
base/ | Database data files | No |
pg_wal/ | Write-ahead logs | No — essential for consistency |
pg_xact/ | Transaction status | No |
postmaster.pid | Lock file | No — causes conflicts |
When Rsync on Database Files IS Safe
There is exactly one scenario where rsyncing raw database files is safe:
Cold Backup (Database Stopped)
# Step 1: Stop the database
sudo systemctl stop mysql # or: sudo systemctl stop postgresql
# Step 2: Rsync the data directory
rsync -av /var/lib/mysql/ /backup/mysql-cold/
# Step 3: Start the database immediately
sudo systemctl start mysql # or: sudo systemctl start postgresql
Downtime required: The database is unavailable during the rsync. For a 10 GB database, this could be 2-5 minutes (local) or 10-30 minutes (remote).
warning
Cold backups require planned downtime. For most production environments, this is unacceptable. Use the alternatives below instead.
Safe Alternatives for Live Databases
Tier 1: Export Tools (Simple, Reliable)
Best for: Small to medium databases, most use cases.
# MySQL / MariaDB — locking-free export
mysqldump -u root --single-transaction \
--routines --triggers --events \
--all-databases | gzip > db_$(date +%F).sql.gz
# PostgreSQL
pg_dump -U postgres -Fc my_database > db_$(date +%F).dump
# Then rsync the dump file
rsync -avzP /var/backups/db/ user@backup:/offsite/db/
| Pros | Cons |
|---|---|
| Simple and reliable | Slower for very large databases (100 GB+) |
No downtime (with --single-transaction) | Full export each time (not incremental) |
| Portable — works across DB versions | CPU-intensive during export |
Tier 2: Hot Backup Tools (Production-Grade)
Best for: Large databases (50 GB+), minimal impact on production.
Percona XtraBackup (MySQL / MariaDB)
# Full hot backup (no locking, no downtime)
xtrabackup --backup --target-dir=/backup/xtrabackup/full/
# Prepare the backup for restoration
xtrabackup --prepare --target-dir=/backup/xtrabackup/full/
# Rsync the prepared backup to remote
rsync -avzP /backup/xtrabackup/full/ user@backup:/offsite/xtrabackup/
Incremental Backup with XtraBackup
# Full backup (baseline)
xtrabackup --backup --target-dir=/backup/base/
# Incremental backup (only changes since last full)
xtrabackup --backup --target-dir=/backup/inc1/ \
--incremental-basedir=/backup/base/
pg_basebackup (PostgreSQL)
# Stream a full backup
pg_basebackup -U postgres -D /backup/pg-basebackup/ \
-Ft -z -P --wal-method=stream
# Rsync to remote
rsync -avzP /backup/pg-basebackup/ user@backup:/offsite/pg/
| Tool | Database | Hot Backup? | Incremental? |
|---|---|---|---|
mysqldump | MySQL / MariaDB | Yes (with --single-transaction) | Full only |
xtrabackup | MySQL / MariaDB | Yes | Yes |
mariabackup | MariaDB | Yes | Yes |
pg_dump | PostgreSQL | Yes | Full only |
pg_basebackup | PostgreSQL | Yes | With WAL archiving |
Tier 3: Native Replication (High Availability)
Best for: Real-time sync, high availability, zero downtime failover.
This is not rsync — it's the database's built-in replication:
MySQL Replication
-- On the primary server
CHANGE MASTER TO
MASTER_HOST='replica.server.com',
MASTER_USER='replication_user',
MASTER_PASSWORD='secure_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
START SLAVE;
PostgreSQL Streaming Replication
/etc/postgresql/16/main/postgresql.conf
# Primary server
wal_level = replica
max_wal_senders = 3
| Feature | Rsync | mysqldump | XtraBackup | Replication |
|---|---|---|---|---|
| Live database safe | ||||
| Zero downtime | ||||
| Real-time sync | ||||
| Incremental | (files) | |||
| Point-in-time recovery | ||||
| Simple setup |
Combined Approach: Rsync + Database Export
The correct pattern uses rsync for files and dedicated tools for databases:
#!/bin/bash
# complete-backup.sh — Files via rsync, DB via export
set -e
TIMESTAMP=$(date +%F_%H%M)
BACKUP="/backup/$TIMESTAMP"
REMOTE="user@backup-server:/offsite/$TIMESTAMP"
mkdir -p "$BACKUP/db" "$BACKUP/files"
# Database: Export (NOT rsync on live files)
mysqldump -u root --single-transaction --all-databases \
| gzip > "$BACKUP/db/all_databases.sql.gz"
# Files: Rsync (safe for application files)
rsync -az --exclude='cache/' /var/www/html/ "$BACKUP/files/www/"
rsync -a /etc/nginx/ "$BACKUP/files/nginx/"
# Transfer everything to offsite
rsync -avzP "$BACKUP/" "$REMOTE/"
echo " Backup complete: $TIMESTAMP"
Common Pitfalls
| Pitfall | Risk Level | Prevention |
|---|---|---|
Rsyncing /var/lib/mysql/ while MySQL is running | Critical — corrupt backup | Always use mysqldump or xtrabackup |
| Assuming cold backup = instant | Moderate — longer downtime than expected | Check data size with du -sh /var/lib/mysql/ first |
Using mysqldump without --single-transaction | Moderate — locks tables | Always include --single-transaction for InnoDB |
| Not testing restore after backup | Critical — unknown backup quality | Periodically import dumps on a test server |
| Using rsync for real-time DB sync | Critical — data loss | Use native replication for real-time sync |
Decision Guide
flowchart TD
START["Need to Back Up Database?"] --> LIVE{"Is the database running?"}
LIVE -->|Yes| SIZE{"Database size?"}
LIVE -->|No - offline| COLD[" Cold Backup with Rsync<br/>rsync /var/lib/mysql/ /backup/"]
SIZE -->|"< 50 GB"| DUMP[" mysqldump + rsync<br/>Simple, reliable"]
SIZE -->|"> 50 GB"| HOT[" XtraBackup / pg_basebackup<br/>Hot backup, incremental"]
START --> REALTIME{"Need real-time sync?"}
REALTIME -->|Yes| REPL[" Native Replication<br/>MySQL/PostgreSQL built-in"]
What's Next
- Database Export and Rsync — The standard export → transfer → import workflow
- Backup Strategies — Build a comprehensive backup architecture
- Cron Automation — Schedule automated database backups