How do I back up a small production database safely?

Short answer: automate regular backups, store them off the server, keep several generations, and test a restore. A backup you have never restored is only a hope.

What a minimum setup looks like

1. Automated schedule. A cron job or managed backup feature, at least daily. More often if losing a day of data would hurt.
2. Consistent dumps. For MySQL with InnoDB, a single-transaction dump avoids locking the whole database:

mysqldump --single-transaction --routines --triggers mydb | gzip > mydb-$(date +%F).sql.gz

For PostgreSQL use pg_dump, and for MongoDB use mongodump. 3. Off-server copy. Upload to object storage or another machine, so a server loss does not take the backups with it. 4. Retention. For example keep daily backups for a few weeks and monthly ones for longer. Delete old ones automatically. 5. Encryption and access control. Backups contain all your data. Encrypt them and restrict who can read them. 6. Monitoring. Alert if a backup did not run or is suspiciously small. 7. Restore test. Regularly restore into a scratch database and check that the app works with it.

Going further

• Point-in-time recovery (binary logs in MySQL, WAL archiving in PostgreSQL) lets you recover to a moment before a bad change, not just to the last dump.
• Uploaded files need backing up too, not only the database.
• Write down the restore procedure, including who does it and how long it should take.

Pitfalls

• Backups stored on the same disk as the database.
• Dumps that silently fail (a full disk, an expired password).
• Never testing a restore.
• Forgetting configuration and secrets needed to rebuild the server.

If your host offers managed backups, use them as one layer, but still keep an independent copy.