Database Backup Strategy Guide

Why Backup Is a Necessity, Not an Option

Data loss happens more often than you expect. A human error in a DELETE query without WHERE, disk corruption, ransomware attack, or even a failed update. Without a reliable and tested backup, any of these scenarios means a disaster that could end your business.

The statistics are clear: 60% of small businesses that lose their data close within 6 months. Backup is not an extra cost; it is insurance for your business continuity.

Backup Types

1. Full Backup

A complete copy of all data every time.

Advantages: Fast and straightforward restoration. A single file contains everything. Disadvantages: Consumes significant space and time. Impractical daily for large databases.

2. Differential Backup

Copies only changes since the last full backup.

Advantages: Faster than full backup. Restoration needs only two files (full + differential). Disadvantages: Size grows daily until the next full backup.

3. Incremental Backup

Copies only changes since the last backup of any type.

Advantages: Smallest size and fastest execution. Disadvantages: Restoration requires all incremental files in order. Losing one file breaks the chain.

Type Comparison

Criteria	Full	Differential	Incremental
Backup size	Large	Medium, growing	Small
Backup time	Long	Medium	Short
Restore time	Short	Medium	Long
Complexity	Simple	Moderate	Complex
Files needed for restore	1	2	Multiple

The 3-2-1 Strategy

The golden rule of backup:

• 3 copies of your data minimum
• 2 different storage media (local disk + cloud storage)
• 1 copy offsite (in a different geographic location)

This strategy protects against all scenarios: disk failure (second copy), site disaster (offsite copy), and data corruption (multiple copies at different times).

MySQL Backup

mysqldump (Logical Backup)

The simplest and most widely used tool:

# Backup single database
mysqldump -u root -p mydb > /backup/mydb_$(date +%Y%m%d_%H%M%S).sql

# Backup all databases
mysqldump -u root -p --all-databases > /backup/all_$(date +%Y%m%d_%H%M%S).sql

# Backup with compression
mysqldump -u root -p mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

# Data only without structure
mysqldump -u root -p --no-create-info mydb > /backup/data_only.sql

Percona XtraBackup (Physical Backup)

For large databases that cannot afford downtime:

# Full backup without table locking
xtrabackup --backup --target-dir=/backup/full/

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1/ \
 --incremental-basedir=/backup/full/

# Prepare backup for restoration
xtrabackup --prepare --target-dir=/backup/full/

Binary Log Replication

For Point-in-Time Recovery:

# Enable Binary Logging
# in my.cnf:
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800
binlog_format = ROW

# Restore to specific point in time
mysqlbinlog --stop-datetime="2026-03-25 14:30:00" \
 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

PostgreSQL Backup

pg_dump (Logical Backup)

# Backup database
pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql

# Custom format (supports selective restore)
pg_dump -U postgres -Fc mydb > /backup/mydb_$(date +%Y%m%d).dump

# Backup all databases
pg_dumpall -U postgres > /backup/all_$(date +%Y%m%d).sql

# Restore from custom format
pg_restore -U postgres -d mydb /backup/mydb_20260325.dump

pg_basebackup (Physical Backup)

# Full physical backup
pg_basebackup -U postgres -D /backup/base \
 -Ft -z -P --wal-method=stream

# Configure WAL Archiving for PITR
# in postgresql.conf:
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
wal_level = replica

Backup Scheduling

Recommended Strategy

Frequency	Type	Retention	Medium
Hourly	WAL/Binary Log	48 hours	Local
Daily (2:00 AM)	Incremental	7 days	Local + offsite
Weekly (Friday)	Full	4 weeks	Local + offsite
Monthly (1st)	Full	12 months	Offsite only

Automation with Cron

# /etc/cron.d/database-backup

# Daily backup at 2 AM
0 2 * * * root /opt/scripts/daily-backup.sh >> /var/log/backup.log 2>&1

# Weekly backup every Friday at 1 AM
0 1 * * 5 root /opt/scripts/weekly-backup.sh >> /var/log/backup.log 2>&1

# Clean old backups daily
30 3 * * * root find /backup/daily -mtime +7 -delete

Testing Restoration

A backup without restoration testing is worthless. Schedule regular tests:

1. Weekly: Verify backup files are complete and intact
2. Monthly: Restore a full backup on a test server and verify data
3. Quarterly: Full restoration test with timing (must be within defined RTO)

# Verify backup integrity
# MySQL
mysqlcheck -u root -p --all-databases

# PostgreSQL
pg_restore --list /backup/mydb.dump | head -20

Offsite Storage

Storage Options

Option	Cost	Speed	Security
S3 / Object Storage	Low	Medium	High with encryption
Second server at different location	Medium	High	High
NFS / SAN	High	Very high	High

Backup Encryption

Backups contain sensitive data. Encryption is mandatory:

# Encrypt with GPG
mysqldump -u root -p mydb | gzip | \
 gpg --encrypt --recipient backup@example.com > /backup/mydb.sql.gz.gpg

# Encrypt with OpenSSL
pg_dump -U postgres mydb | gzip | \
 openssl enc -aes-256-cbc -salt -out /backup/mydb.sql.gz.enc

Conclusion

Effective backup combines multiple types, the 3-2-1 strategy, and regular testing. Do not wait for disaster to discover your backups do not work. On CLOUD (cloud.net.sa) servers, we offer daily backup as an add-on with 7 or 30-day retention, but database-level backup remains your responsibility as the application administrator. Start today.

---

Get Started

Want to try it yourself? Choose the right hosting plan or build your custom server with the specs you need.