mysqldump is the standard tool for logical backups: mysqldump --all-databases --single-transaction --routines --triggers > backup.sql. The --single-transaction flag ensures consistent backups without locking tables (for InnoDB). For larger databases, use mariabackup or xtrabackup for physical backups that are much faster to restore. Schedule backups via cron: run mysqldump daily, with weekly full physical backups for faster recovery options.
MySQL/MariaDB Backup Methods
mysqldump is the standard tool for logical backups: mysqldump --all-databases --single-transaction --routines --triggers > backup.sql. The --single-transaction flag ensures consistent backups without locking tables (for InnoDB). For larger databases, use mariabackup or xtrabackup for physical backups that are much faster to restore. Schedule backups via cron: run mysqldump daily, with weekly full physical backups for faster recovery options.
PostgreSQL Backup Approaches
Use pg_dump for individual databases or pg_dumpall for everything including roles and permissions. For large databases, use the custom format: pg_dump -Fc dbname > backup.dump which compresses automatically and allows selective restoration. For point-in-time recovery, enable WAL archiving: configure archive_mode = on and archive_command to copy WAL files to secure storage. This allows you to restore to any moment in time, not just when the last backup ran.
Automated Backup Pipeline
Build a complete backup pipeline: 1) Dump databases to local temp directory. 2) Compress with gzip or zstd. 3) Encrypt with GPG or upload to encrypted Restic repository. 4) Copy off-site to S3-compatible storage or another VPS. 5) Verify backup integrity with a test restore monthly. 6) Prune old backups according to retention policy (keep 7 daily, 4 weekly, 12 monthly). With FastNode’s sub-10ms latency across the UK, database dumps complete quickly even for large databases.
Looking for reliable hosting? FastNode offers Lightning-fast UK VPS hosting with NVMe storage and low-latency servers across Europe. Explore our plans and find the perfect solution for your needs.