Backup and Restore

Database Backup and Restore

A database backup that has never been restored is not a backup — it is an untested assumption. Database backups must be automated, tested regularly, and stored off the server. The two main approaches are logical backups (SQL dump files, slower to restore, human-readable) and physical backups (binary copies of the data directory, faster to restore, require same database version). For most use cases, logical backups with mysqldump or pg_dump are sufficient.

MySQL/MariaDB backup

# Backup a single database:
mysqldump -u root -p myapp_db > /backup/myapp_db-$(date +%Y%m%d-%H%M).sql

# Backup all databases:
mysqldump -u root -p --all-databases > /backup/all-dbs-$(date +%Y%m%d).sql

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

# Important options:
# --single-transaction  → consistent snapshot without locking (InnoDB tables)
# --routines           → include stored procedures and functions
# --triggers           → include triggers (default: yes)
mysqldump -u root -p --single-transaction --routines myapp_db > /backup/myapp_db.sql

Restore from a mysqldump file

# Create the database first if it doesn't exist:
mysql -u root -p -e "CREATE DATABASE myapp_db;"

# Restore:
mysql -u root -p myapp_db < /backup/myapp_db-20250609.sql

# Restore compressed:
gunzip -c /backup/myapp_db-20250609.sql.gz | mysql -u root -p myapp_db

PostgreSQL backup

# Backup a single database (as postgres user):
sudo -u postgres pg_dump myapp_db > /backup/myapp_db-$(date +%Y%m%d).sql

# Backup in custom format (smaller, supports parallel restore):
sudo -u postgres pg_dump -Fc myapp_db > /backup/myapp_db-$(date +%Y%m%d).dump

# Backup all databases:
sudo -u postgres pg_dumpall > /backup/all-dbs-$(date +%Y%m%d).sql

# Restore from SQL file:
sudo -u postgres psql myapp_db < /backup/myapp_db.sql

# Restore from custom format:
sudo -u postgres pg_restore -d myapp_db /backup/myapp_db.dump

# Parallel restore (faster, uses 4 workers):
sudo -u postgres pg_restore -j 4 -d myapp_db /backup/myapp_db.dump

Backup automation

# Create a MySQL backup script:
sudo nano /usr/local/bin/mysql-backup.sh

/usr/local/bin/mysql-backup.sh

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d-%H%M)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Backup all databases, compressed
mysqldump -u root --single-transaction --all-databases   | gzip > "$BACKUP_DIR/all-dbs-$DATE.sql.gz"

# Delete backups older than retention period
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $BACKUP_DIR/all-dbs-$DATE.sql.gz"

sudo chmod +x /usr/local/bin/mysql-backup.sh

# Schedule daily at 2am:
echo "0 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1" | sudo tee /etc/cron.d/mysql-backup

Testing restores

⚠️ WARNING: Never test restores on a production database. Restore to a separate test instance. A backup you have never restored is an untested assumption — test monthly at minimum.

# Test MySQL restore to a separate database:
mysql -u root -p -e "CREATE DATABASE myapp_restore_test;"
mysql -u root -p myapp_restore_test < /backup/myapp_db-latest.sql

# Verify row counts match:
mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='myapp_db';" > /tmp/prod_counts.txt
mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='myapp_restore_test';" > /tmp/restore_counts.txt
diff /tmp/prod_counts.txt /tmp/restore_counts.txt

# Cleanup:
mysql -u root -p -e "DROP DATABASE myapp_restore_test;"

Conclusion

For MySQL, the essential backup options are --single-transaction (consistent InnoDB backup without locking tables) and --routines (include stored procedures). For PostgreSQL, the custom format (-Fc) is preferred for databases larger than a few GB because it supports parallel restore. Always copy backups off-server to remote storage (S3, a different machine) immediately — a backup that lives only on the database server will be lost if the server disk fails or is compromised.