Database Backup and Recovery on Canadian VPS Servers

Database backups are a different problem from file backups. Files change relatively slowly. A database changes with every form submission, every order, every user registration, and every content update. The appropriate backup frequency and recovery process for a database differ meaningfully from those for files, and understanding the distinction prevents both under-protection and misplaced confidence in a backup that cannot actually restore what you need.

mysqldump: the baseline approach

mysqldump is the standard command-line tool for exporting a MySQL or MariaDB database to a SQL file. A basic daily backup command:

mysqldump -u root --single-transaction --quick dbname | gzip > /backups/dbname-$(date +%Y%m%d).sql.gz

The --single-transaction flag performs the export inside a transaction, allowing it to run without locking tables on InnoDB databases. This is critical for production databases where table locks would prevent concurrent writes during the backup window. --quick streams rows directly rather than buffering the entire result set in memory, which prevents memory exhaustion on large databases. Compress output immediately after export: gzip reduces SQL files by 80 to 90 percent. Automate this as a cron job running at 3am nightly. Add find /backups -name '*.sql.gz' -mtime +7 -delete to the script to purge files older than seven days.

Getting backups off the primary server

A backup stored on the same server as the data it protects is not a complete backup. A server hardware failure, ransomware attack, or accidental deletion destroys both data and local backup simultaneously. Copy database backups to an off-server location. AWS S3 with the ca-central-1 (Montreal) region keeps backups under Canadian jurisdiction. Backblaze B2 is cost-effective at approximately $6 per TB per month. The rclone tool supports both and can sync your backup directory to off-server storage automatically as part of the backup script.

Point-in-time recovery with binary logs

Nightly mysqldump backups cover the scenario where a server fails and you need to restore to the previous night. They do not cover a need to recover data from earlier in the same day. Enable binary logging in /etc/mysql/mysql.conf.d/mysqld.cnf: log_bin = /var/log/mysql/mysql-bin.log. With binary logging enabled, every database change is recorded in the binary log. A point-in-time recovery applies the nightly backup then replays binary log events from backup time to the desired recovery point. Set expire_logs_days = 7 to automatically purge logs older than seven days.

Testing database restores

Run a test restore quarterly. The procedure: take a backup, create a test database, import the backup, connect a staging copy of your application, and verify the data is correct and complete. Document the restore time so you have a realistic recovery time expectation. An untested backup is an assumption, not a safety net.