Application-Consistent Database Backups

Understanding Backup Consistency

A backup taken while a database is actively writing may contain partial transactions, leading to an inconsistent state. Application-consistent backups ensure all transactions are complete and data is in a known-good state at backup time.

Types of Consistency

• Crash-consistent — Like pulling the power plug; database recovers via WAL/journal but may lose uncommitted data
• Application-consistent — All transactions committed, caches flushed, data in a coherent state
• File-consistent — Files not being written during the copy operation

MySQL Consistent Backups

# InnoDB - consistent snapshot without locking
mysqldump --single-transaction --routines --triggers --events \
    --quick --flush-logs --all-databases | gzip > /backup/mysql-$(date +%Y%m%d).sql.gz

# Percona XtraBackup for hot backups
sudo apt install -y percona-xtrabackup-80

xtrabackup --backup --target-dir=/backup/xtra-full \
    --user=backup_user --password=backup_pass
xtrabackup --prepare --target-dir=/backup/xtra-full

# Incremental
xtrabackup --backup --target-dir=/backup/xtra-incr-1 \
    --incremental-basedir=/backup/xtra-full

PostgreSQL Consistent Backups

# pg_dump uses snapshot isolation - always consistent
pg_dump -Fc --verbose --file=/backup/appdb.dump appdb

# Physical backup with WAL streaming
pg_basebackup -D /backup/pg-base -Ft -z -P --wal-method=stream

Filesystem Freeze for Volume Snapshots

#!/bin/bash
set -euo pipefail

# Flush and lock the database
mysql -e "FLUSH TABLES WITH READ LOCK; FLUSH LOGS;"

# Freeze filesystem
sync
fsfreeze -f /var/lib/mysql

# Take LVM snapshot
lvcreate -L 10G -s -n db-snap /dev/vg0/db-data

# Unfreeze immediately
fsfreeze -u /var/lib/mysql
mysql -e "UNLOCK TABLES;"

# Copy data from snapshot at leisure
mount /dev/vg0/db-snap /mnt/db-snap
tar czf /backup/db-consistent-$(date +%Y%m%d).tar.gz -C /mnt/db-snap .
umount /mnt/db-snap
lvremove -f /dev/vg0/db-snap

Multi-Database Coordinated Backup

#!/bin/bash
BACKUP_ID=$(date +%Y%m%d-%H%M%S)
BACKUP_DIR="/backup/$BACKUP_ID"
mkdir -p "$BACKUP_DIR"

# Parallel consistent dumps
mysqldump --single-transaction --all-databases \
    | gzip > "$BACKUP_DIR/mysql.sql.gz" &

redis-cli BGSAVE
cp /var/lib/redis/dump.rdb "$BACKUP_DIR/redis.rdb" &

mongodump --oplog --out="$BACKUP_DIR/mongo" --gzip &

wait

cat > "$BACKUP_DIR/manifest.json" << EOF
{"backup_id":"$BACKUP_ID","timestamp":"$(date -Iseconds)","status":"complete"}
EOF

Best Practices

• Always use --single-transaction for MySQL InnoDB dumps
• Freeze filesystems for the shortest possible duration
• Test restored backups to verify consistency
• Use XtraBackup or pgBackRest for production databases
• Log backup metadata (binlog position, WAL location) with every backup
• For multi-database stacks, coordinate timing or briefly pause writes