We treat database backups as infrastructure, not afterthought. Every small PHP/MySQL app we maintain runs a backup routine that is scripted, rotated, encrypted, and tested — not just enabled. Below is the exact approach we use for projects that handle transactional data, from a simple booking form to a small e-commerce site.

What we back up and how often

Our baseline is a full dump every night at 03:00. For transactional sites — anything that writes orders, payments, or user accounts — we also take an incremental binlog-based snapshot every hour during business hours. A full mysqldump of a 180 MB database takes about 45 seconds; an hourly differential takes under 10 seconds. We store seven daily full dumps, plus 24 hourly slices per day, so the maximum data loss window is 60 minutes for transactional apps and 24 hours for static content sites.

We never rely solely on the hosting provider's automated snapshot. Those are opaque, rarely encrypted, and often restore slowly. Our script lives in /usr/local/bin/backup-db.sh and runs as a dedicated backup user with SELECT, LOCK TABLES, and REPLICATION CLIENT privileges — never the application DB user.

The mysqldump script

We use a single script with explicit flags so the output is consistent and restorable. The key line is:

mysqldump --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 --hex-blob \
  -u backup_user -p"$DB_PASS" app_database | gzip > \
  /backups/app_database_$(date +%Y%m%d_%H%M%S).sql.gz

--single-transaction keeps the dump consistent without locking the whole database. We include --routines and --triggers because PHP apps often store business logic in stored procedures, and missing those means a restore is incomplete. The output is compressed with gzip at level 6; a 180 MB raw database compresses to roughly 42 MB, and the script writes a checksum file next to each archive so we can detect corruption before we need the file.

Rotation and cleanup

We keep local backups for exactly seven days, then delete anything older. The rotation is a single find command run after the dump succeeds:

find /backups/ -type f -name '*.sql.gz' -mtime +7 -delete
find /backups/ -type f -name '*.sha256' -mtime +7 -delete

We also delete failed partial dumps (files under 5 MB that include the timestamp format) before rotation, so a broken dump does not survive. We log every deletion to /var/log/db-backup-rotation.log with the file name and size, because silent deletion is worse than no rotation.

Off-site storage with rclone

Local rotation is not a backup; it is just a shorter failure window. We mirror the /backups/ directory to a remote server every six hours using rclone. Our copy snippet is:

rclone sync /backups/ remote_server:/backups/ \
  --transfers=4 --checksum --delete-during --log-file=/var/log/rclone-backup.log

The remote server is a separate VPS in a different region, not the same hosting provider. If the primary server suffers a disk failure, a provider-level outage, or a ransomware event, the off-site copy remains untouched. For clients who prefer cloud storage, we replace the remote path with an S3-compatible bucket (rclone copy /backups/ s3:my-bucket/app-backups/) and enable versioning on the bucket so even an accidental delete-during sync does not erase history.

We verify the remote copy weekly with rclone check /backups/ remote_server:/backups/ and record the mismatch count. Any non-zero count triggers an alert to our monitoring channel.

Encryption with GPG

Every backup is encrypted before it leaves the server. We generate a 4096-bit RSA keypair on a separate admin machine, export the public key to the server, and encrypt each .sql.gz file immediately after compression:

gpg --batch --yes --trust-model always --encrypt \
  --recipient backup@ourdomain.com --output \
  /backups/app_database_20260921_030000.sql.gz.gpg \
  /backups/app_database_20260921_030000.sql.gz

We delete the unencrypted .sql.gz after confirming the .gpg passes gpg --list-packets. The private key never touches the production server; if the server is compromised, the attacker gets only ciphertext. Restore requires the private key, which we store in a hardware-backed vault and test quarterly.

Testing restores — not just dumps

We run a full restore test every two weeks on a separate staging server. The procedure is fixed: download the latest .gpg, decrypt, decompress, load into a fresh MySQL instance, and run a query count against a reference checksum. A recent restore of our 42 MB compressed archive completed in 4 minutes and 17 seconds, with zero mismatched row counts.

We also test the hourly differential path by replaying a single hour's binlog against the previous full dump. This catches truncation errors, missing routines, or permission issues that a simple file-size check would miss. We keep the results in /backups/restore-tests/ with a timestamp, because a backup that has never been restored is a guess.

How often is enough?

For a static brochure site that updates monthly, daily full dumps are sufficient. For any site that writes user data — orders, messages, account changes — we recommend hourly differentials plus nightly full dumps. The cost of an hourly script is minimal: under 10 seconds of server time and 15 MB of additional disk space per day for a medium-sized database. The cost of a 24-hour data loss is not minimal; for a transactional site, it is unacceptable.

When a client asks whether hourly is overkill, we ask: what is the value of one hour of orders? If the answer is greater than the server cost, we set the schedule to hourly. That is the decision framework we apply in every LEMP stack hardening checklist we build, and it applies here too.

Practical recommendation

We recommend every small PHP/MySQL app run the nightly mysqldump with --single-transaction --routines --triggers, rotate with find ... -mtime +7 -delete, mirror off-site with rclone, encrypt with gpg, and test a restore every two weeks. For transactional sites, add hourly differentials. The entire setup takes about two hours to configure and saves days of recovery time. If you need help setting this up for a project, reach out through our contact page. Our work and case studies are available at /work/, including mobile money payment integration for Ugandan e-commerce.